第71讲：MySQL锁机制详解：表级锁、元数据锁和意向锁的全面解析与实践指南

MySQL中的表级锁

文章目录

• MySQL中的表级锁
• • 1.MySQL中表级锁的概念
  • 2.表锁的概念以及基本使用
  • 3.元数据锁的概念以及基本使用
  • 4.意向锁的概念以及基本使用

1.MySQL中表级锁的概念

表级锁显而易见就是对于数据库表的一种锁，通过表锁可以锁定表，避免并发事务下，导致表中数据不一致性。

表级锁的力度大，针对表上锁后，表中的数据可能都无法操作，只能读，影响的范围大，并且发送锁的冲突概率是最高的，因为后面还会有行级锁，如果通过事务对某一条数据进行了行级锁，那么再创建一个表级锁，就会导致事务无法提交的情况。

对于MySQL中的表级锁分为以下三种类型：

• 表锁
  • 表锁是针对某张表设置一个lock锁。
• 元数据锁
  • 元数据锁就是针对表结构的一种锁。
• 意向锁
  • 对表设置锁时，会检查表中每一条数据是否有行级锁，可以通过意向锁避免设置表锁时去检查每一条数据是否有行级锁。

2.表锁的概念以及基本使用

2.1.表锁的分类以及概念

表级锁中的表锁又分为两类：

• 表共享读锁（read lock）
• 表独占写锁（write lock）

什么是表共享读锁？

如下图所示，在多个并发客户端的情况下，第一个客户端开启了表共享读锁，那么DQL查询操作在多个客户端上都可以查询数据，不受影响，但是当有DDL/DML类的修改表数据的操作时，第一个客户端直接会提示表中存在锁，第二个客户端的写操作会处于阻塞状态，当第一个客户端释放锁之后，第二个客户端的写操作才能正常执行。

共享读锁不会影响其他客户端的读，但是会阻塞其他客户端的写。

什么是表独占写锁？

如下图所示，在多个并发客户端的情况下，第一个客户端开启了表独占写锁，此时第一个客户端上执行DQL查询或者DML修改数据都是不受任何影响，但是当第二个客户端要执行查询或者修改数据的操作时，就会一直处于阻塞状态，只有当第一个客户端释放锁之后，第二个客户端才能操作。

独占写锁会阻塞其他客户端的读以及写。

2.2.表锁的使用语法

1）创建锁

lock tables 表名 read/write

2）解锁

unlock tables
或者断开客户端链接

2.3.表共享读锁的基本使用

1.客户端1创建一个读锁
mysql> lock tables xscjb read;

2.客户端1查询数据
mysql> select * from xscjb limit 1;
+----+--------+------+------+------+
| xh | xm     | ywcj | sxcj | yycj |
+----+--------+------+------+------+
|  1 | 小明   |   45 |   75 |   93 |
+----+--------+------+------+------+

3.客户端1写入数据异常
mysql> update xscjb set ywcj = '99' where xh = 1;
ERROR 1100 (HY000): Table 'xscjb_logs' was not locked with LOCK TABLES

4.客户端2查询数据
mysql> select * from xscjb limit 1;
+----+--------+------+------+------+
| xh | xm     | ywcj | sxcj | yycj |
+----+--------+------+------+------+
|  1 | 小明   |   45 |   75 |   93 |
+----+--------+------+------+------+

5.客户端2写入数据
mysql> update xscjb set ywcj = '99' where xh = 1;
一直处于阻塞中

6.客户端1解锁
mysql> unlock tables;

7.客户端2写入数据成功

2.4.表独占写锁的基本使用

1.客户端1创建一个读锁
mysql> lock tables xscjb read;

2.客户端1查询数据
mysql> select * from xscjb limit 1;
+----+--------+------+------+------+
| xh | xm     | ywcj | sxcj | yycj |
+----+--------+------+------+------+
|  1 | 小明   |   45 |   75 |   93 |
+----+--------+------+------+------+

3.客户端1写入数据正常
mysql> update xscjb set ywcj = '99' where xh = 1;
Query OK, 0 rows affected (0.13 sec)
Rows matched: 1  Changed: 0  Warnings: 0

4.客户端2查询数据
mysql> select * from xscjb limit 1;
处于阻塞

5.客户端2写入数据
mysql> update xscjb set ywcj = '99' where xh = 1;
处于阻塞中

6.客户端1解锁
mysql> unlock tables;

7.客户端2写入数据成功

3.元数据锁的概念以及基本使用

3.1.元数据锁的概念

元数据锁（metadata lock）是MySQL系统中自动控制的一种锁，无需手动创建，默认就存在的一种锁，在访问一张数据表时都会自动增加一个元数据锁。

元数据锁又称为MDL锁，元数据锁的作用是维护表中元数据与实际数据的一致性，元数据可以理解为是表结构，当表中存在活动的事务时，不可以对元数据进行写入操作。为了避免表中还存在活动事务时，元数据被修改的情况，从而出现了元数据锁。

通俗一点来说，元数据锁就是为了避免DML（数据库操作语音）与DDL（数据库定义语音）语句有冲突，从而保证数据读写的正确性。

在MySQL5.5版本中引入了MDL元数据锁的概念，当对一张表进行增删改查操作时，会默认加一个MDL元数据共享锁，当对表结构进行变更操作时，会添加一个MDL排他锁。

3.2.常见的SQL操作所对应的元数据锁

当有互斥的操作，就说明不兼容，此时就会处于阻塞状态。

在元数据锁中对DQL和DML语句又进行了细分，DQL类型的语句都是SHARED_READ类型的元数据锁，DML类型的语句都是SHARED_WRITE类型的锁，这两种类型的锁互相兼容。

3.3.元数据锁演示

#事务一
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from xscjb limit 1;
+----+--------+------+------+------+
| xh | xm     | ywcj | sxcj | yycj |
+----+--------+------+------+------+
|  1 | 小明   |   99 |   75 |   93 |
+----+--------+------+------+------+
1 row in set (0.00 sec)

mysql> update xscjb set ywcj = '99' where xh = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.03 sec)


#事务二
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from xscjb limit 1;
+----+--------+------+------+------+
| xh | xm     | ywcj | sxcj | yycj |
+----+--------+------+------+------+
|  1 | 小明   |   99 |   75 |   93 |
+----+--------+------+------+------+
1 row in set (0.00 sec)

mysql> update xscjb set ywcj = '99' where xh = 2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.03 sec)

只要两个事务修改的不是同一条数据，都是互不影响的。

1.开启一个事务查询xscjb的数据，不提交事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from xscjb limit 1;
+----+--------+------+------+------+
| xh | xm     | ywcj | sxcj | yycj |
+----+--------+------+------+------+
|  1 | 小明   |   99 |   75 |   93 |
+----+--------+------+------+------+

2.此时去修改xscjb表的表结构
mysql> alter table xscjb add column pjcj int;
会一直处于阻塞状态，修改元数据会产生排它锁，排它锁与共享锁互斥，此时会处于阻塞状态

3.只有当事务全部提交后，修改元数据的语句才能成功执行

我们还可以通过以下SQL语句看到当前数据库实例中的元数据锁，如上图所示，select产生的是共享读锁，alert产生了排它锁。

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks ;

4.意向锁的概念以及基本使用

4.1.意向锁的概念

意向锁是为了避免DML数据库操作语句在执行过程中，出现行锁与表锁的冲突，通过意向锁可以在创建表锁时不需要去检查每行数据是否有行锁，从而减少表锁检查所带来的性能消耗。

没有使用意向锁之前，客户端一给表中加了行锁，客户端二要给表加表锁，过程如下：

当有了意向锁之后，客户端一给表中加了行锁，客户端二要给表加表锁，过程如下：

简单来说，意向锁相当于一个声明，根据这个声明，添加表锁时就有了参考依据，因此不需要逐行判断是否可以添加表锁，从而避免了行锁与表锁产生的冲突。

4.2.意向锁的种类

根据意向锁的不同种类，可以判断出是否允许对表添加表锁，从而避免行锁与表锁的冲突，如果不去管这个冲突，那么一条数据正在修改，突然对表上了锁，那么就会产生数据异常的现象。

意向锁的分类：

• 意向共享锁（IS）
  • 平常的select语句不会自定添加意向共享锁，需要在select后面加上lock in share mode参数，才能为表建立意向共享锁。
• 意向排它锁（IX）
  • insert、update、delete、select…for update等语句，都会字段添加一个意向排它锁。

意向共享锁可以与表锁中表共享读锁（read）兼容，但是与表锁中的表独占写锁（write）互斥，当表存意向共享锁时，我们可以为表设置一个读锁，但是不能设置为写锁，一旦表中有意向共享锁时，当为表设置写锁时，就会处于阻塞状态，只有当事务提交后，写锁才能正常执行。

意向排它锁既与表共享读锁排斥又与表独占写锁排斥，当表中有意向排它锁时，都不能为表设置表锁，否则就会处于阻塞状态。

当事务提交后，意向共享锁、意向排它锁都会自定是否，被阻塞的操作此时才能被执行。

4.3.意向共享锁与表读锁兼容的演示

意向共享锁与表锁中的读锁兼容，但是与写锁互斥。

首先开启一个事务，然后执行select语句产生意向共享锁，然后再另一个会话中为表设置表锁，观察效果。

1.开启一个事务并执行select产生意向共享锁
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from xscjb where xh = 1 lock in share mode;
+----+--------+------+------+------+------+
| xh | xm     | ywcj | sxcj | yycj | pjcj |
+----+--------+------+------+------+------+
|  1 | 小明   |   99 |   75 |   93 | NULL |
+----+--------+------+------+------+------+
1 row in set (0.00 sec)


2.查询产生的意向共享锁，lock_mode为IS就表示为意向共享锁
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data |
+---------------+-------------+------------+-----------+---------------+-----------+
| db_1          | xscjb       | NULL       | TABLE     | IS            | NULL      |
| db_1          | xscjb       | PRIMARY    | RECORD    | S,REC_NOT_GAP | 1         |
+---------------+-------------+------------+-----------+---------------+-----------+

3.为表设置读锁没有任何问题
mysql> lock tables xscjb read;
mysql> unlock tables;

4.由于意向共享锁与写锁互斥，因此写锁一直处于阻塞中，只有当事务提交后释放意向锁，写锁才能正常执行
mysql> lock tables xscjb write;

4.3.意向排他锁与表锁不兼容的演示

意向排它锁与表读锁和写锁都互斥，都会处于阻塞中。

1.开启一个事务并执行update产生意向排它锁
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update yexxb set ye = '10000' where id = 1;


2.查询产生的意向共享锁，lock_mode为IX就表示为意向排它锁
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data |
+---------------+-------------+------------+-----------+---------------+-----------+
| db_1          | yexxb       | NULL       | TABLE     | IX            | NULL      |
| db_1          | yexxb       | PRIMARY    | RECORD    | X,REC_NOT_GAP | 1         |
+---------------+-------------+------------+-----------+---------------+-----------+


4.由于意向排它锁与读锁、写锁互斥，因此都会一直处于阻塞中，只有当事务提交后释放意向锁，读锁、写锁才能正常执行
mysql> lock tables yexxb read;
mysql> lock tables yexxb write;