mysql数据锁之意向锁
约 1511 字大约 5 分钟
2025-05-20
重点信息
意向锁是一种不与行级锁冲突表级锁，这一点非常重要!!! 如果与行级锁冲突, 那么意向锁就没有意义了!!! 不如直接用普通的表级锁!!!
需要强调一下，意向锁是一种不与行级锁冲突表级锁，这一点非常重要!!!
意向锁是有数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享 / 排他锁之前，InooDB 会先获取该数据行所在在数据表的对应意向锁。
意向共享锁
mysql> begin;
Query OK, 0 rows affected (0.000 sec)
mysql>
mysql>
mysql> select * from people where id = 3 FOR SHARE;
+----+------+-----+--------+--------+---------+
| id | code | age | name | height | address |
+----+------+-----+--------+--------+---------+
| 3 | 103 | 23 | 苏三 | 174 | 成都 |
+----+------+-----+--------+--------+---------+
1 row in set (0.001 sec)
mysql> select * from performance_schema.data_locks;
+--------+--------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+--------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| INNODB | 140426607632288:92:1764:140426811047840 | 421901584342944 | 49 | 30 | blog_test | people | NULL | NULL | NULL | 140426811047840 | TABLE | IS | GRANTED | NULL |
| INNODB | 140426607632288:92:500:4:4:140426824922144 | 421901584342944 | 49 | 30 | blog_test | people | NULL | NULL | PRIMARY | 140426824922144 | RECORD | S,REC_NOT_GAP | GRANTED | 3 |
+--------+--------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
2 rows in set (0.003 sec)
mysql>上面 FOR SHARE 预期价数据共享岁, 可以看到, 数据行的记录锁是 S,REC_NOT_GAP 锁, 而表的意向锁是 IS 锁.
注意上面的操作不要提交或者回滚, 后面我们回来新事务验证锁的冲突.
下面再新开一个mysql连接, 执行下面的sql:
mysql> begin;
Query OK, 0 rows affected (0.000 sec)
mysql> select * from people where id = 3 FOR SHARE;
+----+------+-----+--------+--------+---------+
| id | code | age | name | height | address |
+----+------+-----+--------+--------+---------+
| 3 | 103 | 23 | 苏三 | 174 | 成都 |
+----+------+-----+--------+--------+---------+
1 row in set (0.000 sec)
mysql> select * from performance_schema.data_locks;
+--------+---------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+---------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| INNODB | 140426607632288:92:1764:140426811047840 | 421901584342944 | 49 | 30 | blog_test | people | NULL | NULL | NULL | 140426811047840 | TABLE | IS | GRANTED | NULL |
| INNODB | 140426607631496:124:1764:140426811046816 | 421901584342152 | 47 | 14 | blog_test | people | NULL | NULL | NULL | 140426811046816 | TABLE | IS | GRANTED | NULL |
| INNODB | 140426607631496:124:500:4:4:140426824912416 | 421901584342152 | 47 | 14 | blog_test | people | NULL | NULL | PRIMARY | 140426824912416 | RECORD | S,REC_NOT_GAP | GRANTED | 3 |
| INNODB | 140426607632288:92:500:4:4:140426824922144 | 421901584342944 | 49 | 30 | blog_test | people | NULL | NULL | PRIMARY | 140426824922144 | RECORD | S,REC_NOT_GAP | GRANTED | 3 |
+--------+---------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
4 rows in set (0.002 sec)
mysql>会看到, LOCK_DATA=3上有两个 共享锁 , 同时数据表上有两个 意向共享锁 , 说明 共享锁 不会与 表级意向共享锁 冲突. 同时表级共享意向锁可以添加多个.
意向排它锁
接上述意向共享锁, 新开一个mysql连接, 执行下面的sql:
mysql> begin;
Query OK, 0 rows affected (0.000 sec)
mysql> select * from people where id = 3 FOR UPDATE;发现SQL陷入阻塞状态, 新开查询窗口, 查询锁信息:
mysql> select * from performance_schema.data_locks;
+--------+---------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+---------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| INNODB | 140426607632288:92:1764:140426811047840 | 421901584342944 | 49 | 30 | blog_test | people | NULL | NULL | NULL | 140426811047840 | TABLE | IS | GRANTED | NULL |
| INNODB | 140426607631496:124:1764:140426811046816 | 421901584342152 | 47 | 14 | blog_test | people | NULL | NULL | NULL | 140426811046816 | TABLE | IS | GRANTED | NULL |
| INNODB | 140426607633080:6:1764:140426811048864 | 116763 | 51 | 13 | blog_test | people | NULL | NULL | NULL | 140426811048864 | TABLE | IX | GRANTED | NULL |
| INNODB | 140426607631496:124:500:4:4:140426824912416 | 421901584342152 | 47 | 14 | blog_test | people | NULL | NULL | PRIMARY | 140426824912416 | RECORD | S,REC_NOT_GAP | GRANTED | 3 |
| INNODB | 140426607632288:92:500:4:4:140426824922144 | 421901584342944 | 49 | 30 | blog_test | people | NULL | NULL | PRIMARY | 140426824922144 | RECORD | S,REC_NOT_GAP | GRANTED | 3 |
| INNODB | 140426607633080:6:500:4:4:140426824926752 | 116763 | 51 | 13 | blog_test | people | NULL | NULL | PRIMARY | 140426824926752 | RECORD | X,REC_NOT_GAP | WAITING | 3 |
+--------+---------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
6 rows in set (0.002 sec)
mysql>发现表级意向排它锁(IX)获取成功, 但是 行级意向锁WATING状态 , 说明 表级意向共享锁与表级意向排它锁是可以同时存在的 , 但是 行级共享锁与排它锁时互斥的.
再次新开一个连接, 执行如下sql:
mysql> begin;
Query OK, 0 rows affected (0.000 sec)
mysql> select * from people where id = 2 FOR UPDATE;
+----+------+-----+-----------+--------+---------+
| id | code | age | name | height | address |
+----+------+-----+-----------+--------+---------+
| 2 | 102 | 18 | 周杰伦 | 173 | 台湾 |
+----+------+-----+-----------+--------+---------+
1 row in set (0.004 sec)
mysql>发现我们再查询 id = 2 的数据是, 并没有陷入锁等待, 直接加锁成功, 此时查看锁信息数据如下:
mysql> select * from performance_schema.data_locks;
+--------+---------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+---------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| INNODB | 140426607632288:92:1764:140426811047840 | 421901584342944 | 49 | 30 | blog_test | people | NULL | NULL | NULL | 140426811047840 | TABLE | IS | GRANTED | NULL |
| INNODB | 140426607631496:124:1764:140426811046816 | 421901584342152 | 47 | 14 | blog_test | people | NULL | NULL | NULL | 140426811046816 | TABLE | IS | GRANTED | NULL |
| INNODB | 140426607633080:8:1764:140426811048864 | 116764 | 51 | 17 | blog_test | people | NULL | NULL | NULL | 140426811048864 | TABLE | IX | GRANTED | NULL |
| INNODB | 140426607633080:8:500:4:3:140426824926752 | 116764 | 51 | 17 | blog_test | people | NULL | NULL | PRIMARY | 140426824926752 | RECORD | X,REC_NOT_GAP | GRANTED | 2 |
| INNODB | 140426607631496:124:500:4:4:140426824912416 | 421901584342152 | 47 | 14 | blog_test | people | NULL | NULL | PRIMARY | 140426824912416 | RECORD | S,REC_NOT_GAP | GRANTED | 3 |
| INNODB | 140426607632288:92:500:4:4:140426824922144 | 421901584342944 | 49 | 30 | blog_test | people | NULL | NULL | PRIMARY | 140426824922144 | RECORD | S,REC_NOT_GAP | GRANTED | 3 |
+--------+---------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
6 rows in set (0.003 sec)
mysql>说明如下问题:
- 表级意向共享锁与行级排它锁不冲突, 可以同时存在
- 表级意向排它锁与行级共享锁不冲突, 可以同时存在
- 行级锁, 不是施加在同一条数据上, 共享锁与排它锁可以同时存在
作用
上面演示了各种场景下, 施加的意向锁以及所之间的兼容性, 那么, 意向锁的作用是什么呢? 有什么意义?
- 处理锁定请求: 当一个事务要锁定表中的某一行数据时，如果没有意向锁，那么其他事务试图
锁定整个表的操作将会被阻塞。这时系统需要检查表中的每一行是否已被加锁，这会大大降低效率。有了意向锁，只需检查表上的意向锁即可. 本质上属于空间换时间 - 提高并发性(自行看看插入意向锁,如何提高并发): 意向锁的设计使得多个事务可以对表中的不同行同时加锁，而不影响表级锁的处理。例如，如果一个事务已经对某一行加了排他锁，另一个事务想要对另一行加排他锁，由于意向锁不会与行级的共享或排他锁互斥，因此第二个事务可以继续执行而不会被阻塞.
- 保证数据完整性: 通过使用意向锁，数据库能确保在多粒度锁的环境下数据的一致性和完整性不被破坏。当一个事务持有表的意向排他锁时，其他事务知道有其他操作正在进行，从而避免发生冲突操作.