MySQL语句加锁分析
准备表结构
DROP TABLE IF EXISTS `t_student`; CREATE TABLE `t_student` ( `id` int NOT NULL, `no` char(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `age` int NOT NULL, `score` int NOT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `idx_no`(`no` ASC) USING BTREE, INDEX `idx_name`(`name` ASC) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_student -- ---------------------------- INSERT INTO `t_student` VALUES (15, 'S0015', 'Bob', 15, 100); INSERT INTO `t_student` VALUES (18, 'S0018', 'Alice', 18, 100); INSERT INTO `t_student` VALUES (20, 'S0020', 'Jim', 20, 100); INSERT INTO `t_student` VALUES (30, 'S0030', 'Eric', 30, 91); INSERT INTO `t_student` VALUES (37, 'S0037', 'Tom', 37, 22); INSERT INTO `t_student` VALUES (49, 'S0049', 'Tom', 49, 83); INSERT INTO `t_student` VALUES (50, 'S0050', 'Rose', 50, 89);
一、 Insert语句分析
1. 开启事物，执行语句
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into t_student values(56,'S0008','Dany',23,89); Query OK, 1 row affected (0.00 sec)
2. 查看锁信息
mysql> select * from data_locks\G; *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 2564937557728:1065:2564927920152 ENGINE_TRANSACTION_ID: 16710 THREAD_ID: 52 EVENT_ID: 29 OBJECT_SCHEMA: test OBJECT_NAME: t_student PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 2564927920152 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL 1 row in set (0.00 sec)
说明：插入语句只是 在表上加入了意向排他锁(IX)，意向锁不会阻止其他事务进行插入操作，注意id=56这条记录此时没有被提交，是对其他事务不可见的（RR级别下）此时当前事务隐式持有id=56的记录锁，但如果其他事务执行update t_student set score=100 where id>20;也会被阻塞，因为该事务要获取id>20的所有Next Key Lock，获取id=56的Next Key Lock是获取不到的，需要等待。
一、 Update语句分析
按id更新
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update t_student set score = 100 where id = 25; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0
查看锁信息
mysql> select * from data_locks\G; *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 2564937557728:1065:2564927920152 ENGINE_TRANSACTION_ID: 16711 THREAD_ID: 52 EVENT_ID: 33 OBJECT_SCHEMA: test OBJECT_NAME: t_student PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 2564927920152 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 2564937557728:3:4:5:2564926668824 ENGINE_TRANSACTION_ID: 16711 THREAD_ID: 52 EVENT_ID: 33 OBJECT_SCHEMA: test OBJECT_NAME: t_student PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 2564926668824 LOCK_TYPE: RECORD LOCK_MODE: X,GAP LOCK_STATUS: GRANTED LOCK_DATA: 30 2 rows in set (0.00 sec)
共加了两个锁，分别是：
-
表锁：IX 类型的意向锁；
-
行锁：X 类型的间隙锁；
这里我们重点关注行锁，图中 LOCK_TYPE 中的 RECORD 表示行级锁，而不是记录锁的意思，通过 LOCK_MODE 可以确认是 next-key 锁，还是间隙锁，还是记录锁：-
如果 LOCK_MODE 为 X，说明是 next-key 锁；
-
如果 LOCK_MODE 为 X, REC_NOT_GAP，说明是记录锁；
-
如果 LOCK_MODE 为 X, GAP，说明是间隙锁；因此，此时事务 A 在主键索引（INDEX_NAME : PRIMARY）上加的是间隙锁，锁范围是(20, 30)。
-
说明 在表上加入了IX锁，在记录上加入了X锁和GAP锁，注意GAP锁和GAP锁并不互斥。GAP锁与插入意向锁互斥。
此时，我们启动另外一个事务，向插入一条 id=26的记录，再看看锁的信息：
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into t_student values(26,'S0008','Dany',23,89);
mysql> select * from data_locks\G; *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 2564937555400:1065:2564927917848 ENGINE_TRANSACTION_ID: 16748 THREAD_ID: 56 EVENT_ID: 7 OBJECT_SCHEMA: test OBJECT_NAME: t_student PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 2564927917848 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 2564937555400:3:4:5:2564926606360 ENGINE_TRANSACTION_ID: 16748 THREAD_ID: 56 EVENT_ID: 7 OBJECT_SCHEMA: test OBJECT_NAME: t_student PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 2564926606360 LOCK_TYPE: RECORD LOCK_MODE: X,GAP,INSERT_INTENTION LOCK_STATUS: WAITING LOCK_DATA: 30 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 2564937554624:1065:2564927917080 ENGINE_TRANSACTION_ID: 16747 THREAD_ID: 55 EVENT_ID: 4 OBJECT_SCHEMA: test OBJECT_NAME: t_student PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 2564927917080 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 4. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 2564937554624:3:4:5:2564926603288 ENGINE_TRANSACTION_ID: 16747 THREAD_ID: 55 EVENT_ID: 4 OBJECT_SCHEMA: test OBJECT_NAME: t_student PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 2564926603288 LOCK_TYPE: RECORD LOCK_MODE: X,GAP LOCK_STATUS: GRANTED LOCK_DATA: 30 4 rows in set (0.00 sec)
此时，插入id=26的这个事务，想要获取插入意向锁，但是获取不到，被阻塞了。说明GAP锁与插入意向锁互斥。
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update t_student set score = 100 where id = 20; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
查看锁信息
mysql> select * from data_locks\G; *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 2564937557728:1065:2564927920152 ENGINE_TRANSACTION_ID: 16716 THREAD_ID: 52 EVENT_ID: 37 OBJECT_SCHEMA: test OBJECT_NAME: t_student PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 2564927920152 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 2564937557728:3:4:4:2564926668824 ENGINE_TRANSACTION_ID: 16716 THREAD_ID: 52 EVENT_ID: 37 OBJECT_SCHEMA: test OBJECT_NAME: t_student PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 2564926668824 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 20 2 rows in set (0.00 sec)
说明 在表上加入了IX锁，在记录上加入了X锁，此时没有加GAP锁。
update t_student set score = 100 where id = 25; 表中并不存在id=25的记录。但是这里锁住了（20-30）这个区间，其他事务此时向这个区间插入数据会被阻塞。
update t_student set score = 100 where id = 20; 表中存在id=20的记录，这里会锁住20这个记录，不会有gap锁，其他事务此时向任意区间插入数据都不会被阻塞。
两个事务的间隙锁之间是相互兼容的，不会产生冲突。
在MySQL官网上还有一段非常关键的描述：
Gap locks in InnoDB are “purely inhibitive”, which means that their only purpose is to prevent other transactions from Inserting to the gap. Gap locks can co-exist. A gap lock taken by one transaction does not prevent another transaction from taking a gap lock on the same gap. There is no difference between shared and exclusive gap locks. They do not conflict with each other, and they perform the same function.
间隙锁的意义只在于阻止区间被插入，因此是可以共存的。一个事务获取的间隙锁不会阻止另一个事务获取同一个间隙范围的间隙锁，共享（S型）和排他（X型）的间隙锁是没有区别的，他们相互不冲突，且功能相同。
在MySQL的官方文档中有以下重要描述：
An Insert intention lock is a type of gap lock set by Insert operations prior to row Insertion. This lock signals the intent to Insert in such a way that multiple transactions Inserting into the same index gap need not wait for each other if they are not Inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to Insert values of 5 and 6, respectively, each lock the gap between 4 and 7 with Insert intention locks prior to obtaining the exclusive lock on the Inserted row, but do not block each other because the rows are nonconflicting.
这段话表明尽管插入意向锁是一种特殊的间隙锁，但不同于间隙锁的是，该锁只用于并发插入操作。
如果说间隙锁锁住的是一个区间，那么「插入意向锁」锁住的就是一个点。因而从这个角度来说，插入意向锁确实是一种特殊的间隙锁。
插入意向锁与间隙锁的另一个非常重要的差别是：尽管「插入意向锁」也属于间隙锁，但两个事务却不能在同一时间内，一个拥有间隙锁，另一个拥有该间隙区间内的插入意向锁（当然，插入意向锁如果不在间隙锁区间内则是可以的）。所以，插入意向锁和间隙锁之间是冲突的。
另外，我补充一点，插入意向锁的生成时机：
每插入一条新记录，都需要看一下待插入记录的下一条记录上是否已经被加了间隙锁，如果已加间隙锁，此时会生成一个插入意向锁，然后锁的状态设置为等待状态（PS：MySQL 加锁时，是先生成锁结构，然后设置锁的状态，如果锁状态是等待状态，并不是意味着事务成功获取到了锁，只有当锁状态为正常状态时，才代表事务成功获取到了锁），现象就是 Insert 语句会被阻塞。
不按id更新
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update t_student set score=100 where name='Tom'; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from data_locks\G; *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 2564937554624:1065:2564927917080 ENGINE_TRANSACTION_ID: 16750 THREAD_ID: 55 EVENT_ID: 17 OBJECT_SCHEMA: test OBJECT_NAME: t_student PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 2564927917080 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 2564937554624:3:4:1:2564926603288 ENGINE_TRANSACTION_ID: 16750 THREAD_ID: 55 EVENT_ID: 17 OBJECT_SCHEMA: test OBJECT_NAME: t_student PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 2564926603288 LOCK_TYPE: RECORD LOCK_MODE: X LOCK_STATUS: GRANTED LOCK_DATA: supremum pseudo-record *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 2564937554624:3:4:2:2564926603288 ENGINE_TRANSACTION_ID: 16750 THREAD_ID: 55 EVENT_ID: 17 OBJECT_SCHEMA: test OBJECT_NAME: t_student PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 2564926603288 LOCK_TYPE: RECORD LOCK_MODE: X LOCK_STATUS: GRANTED LOCK_DATA: 15 *************************** 4. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 2564937554624:3:4:3:2564926603288 ENGINE_TRANSACTION_ID: 16750 THREAD_ID: 55 EVENT_ID: 17 OBJECT_SCHEMA: test OBJECT_NAME: t_student PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 2564926603288 LOCK_TYPE: RECORD LOCK_MODE: X LOCK_STATUS: GRANTED LOCK_DATA: 18 *************************** 5. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 2564937554624:3:4:4:2564926603288 ENGINE_TRANSACTION_ID: 16750 THREAD_ID: 55 EVENT_ID: 17 OBJECT_SCHEMA: test OBJECT_NAME: t_student PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 2564926603288 LOCK_TYPE: RECORD LOCK_MODE: X LOCK_STATUS: GRANTED LOCK_DATA: 20 *************************** 6. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 2564937554624:3:4:5:2564926603288 ENGINE_TRANSACTION_ID: 16750 THREAD_ID: 55 EVENT_ID: 17 OBJECT_SCHEMA: test OBJECT_NAME: t_student PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 2564926603288 LOCK_TYPE: RECORD LOCK_MODE: X LOCK_STATUS: GRANTED LOCK_DATA: 30 *************************** 7. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 2564937554624:3:4:6:2564926603288 ENGINE_TRANSACTION_ID: 16750 THREAD_ID: 55 EVENT_ID: 17 OBJECT_SCHEMA: test OBJECT_NAME: t_student PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 2564926603288 LOCK_TYPE: RECORD LOCK_MODE: X LOCK_STATUS: GRANTED LOCK_DATA: 37 *************************** 8. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 2564937554624:3:4:7:2564926603288 ENGINE_TRANSACTION_ID: 16750 THREAD_ID: 55 EVENT_ID: 17 OBJECT_SCHEMA: test OBJECT_NAME: t_student PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 2564926603288 LOCK_TYPE: RECORD LOCK_MODE: X LOCK_STATUS: GRANTED LOCK_DATA: 49 *************************** 9. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 2564937554624:3:4:8:2564926603288 ENGINE_TRANSACTION_ID: 16750 THREAD_ID: 55 EVENT_ID: 17 OBJECT_SCHEMA: test OBJECT_NAME: t_student PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 2564926603288 LOCK_TYPE: RECORD LOCK_MODE: X LOCK_STATUS: GRANTED LOCK_DATA: 50 9 rows in set (0.00 sec)
说明表上加了IX锁，所有记录都加上了Next Key Lock.
三、Select语句分析
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from t_student where id>20; +----+-------+------+-----+-------+ | id | no | name | age | score | +----+-------+------+-----+-------+ | 30 | S0004 | Eric | 23 | 91 | | 37 | S0005 | Tom | 22 | 22 | | 49 | S0006 | Tom | 25 | 83 | | 50 | S0007 | Rose | 23 | 89 | +----+-------+------+-----+-------+ 4 rows in set (0.00 sec)
查看锁信息：
mysql> select * from data_locks\G; Empty set (0.00 sec)
说明该语句没有使用任何锁。
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from t_student where id>20 for update; +----+-------+------+-----+-------+ | id | no | name | age | score | +----+-------+------+-----+-------+ | 30 | S0004 | Eric | 23 | 91 | | 37 | S0005 | Tom | 22 | 22 | | 49 | S0006 | Tom | 25 | 83 | | 50 | S0007 | Rose | 23 | 89 | +----+-------+------+-----+-------+ 4 rows in set (0.00 sec)
mysql> select * from data_locks\G; *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 2564937554624:1065:2564927917080 ENGINE_TRANSACTION_ID: 16749 THREAD_ID: 55 EVENT_ID: 12 OBJECT_SCHEMA: test OBJECT_NAME: t_student PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 2564927917080 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 2564937554624:3:4:1:2564926603288 ENGINE_TRANSACTION_ID: 16749 THREAD_ID: 55 EVENT_ID: 12 OBJECT_SCHEMA: test OBJECT_NAME: t_student PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 2564926603288 LOCK_TYPE: RECORD LOCK_MODE: X LOCK_STATUS: GRANTED LOCK_DATA: supremum pseudo-record *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 2564937554624:3:4:5:2564926603288 ENGINE_TRANSACTION_ID: 16749 THREAD_ID: 55 EVENT_ID: 12 OBJECT_SCHEMA: test OBJECT_NAME: t_student PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 2564926603288 LOCK_TYPE: RECORD LOCK_MODE: X LOCK_STATUS: GRANTED LOCK_DATA: 30 *************************** 4. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 2564937554624:3:4:6:2564926603288 ENGINE_TRANSACTION_ID: 16749 THREAD_ID: 55 EVENT_ID: 12 OBJECT_SCHEMA: test OBJECT_NAME: t_student PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 2564926603288 LOCK_TYPE: RECORD LOCK_MODE: X LOCK_STATUS: GRANTED LOCK_DATA: 37 *************************** 5. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 2564937554624:3:4:7:2564926603288 ENGINE_TRANSACTION_ID: 16749 THREAD_ID: 55 EVENT_ID: 12 OBJECT_SCHEMA: test OBJECT_NAME: t_student PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 2564926603288 LOCK_TYPE: RECORD LOCK_MODE: X LOCK_STATUS: GRANTED LOCK_DATA: 49 *************************** 6. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 2564937554624:3:4:8:2564926603288 ENGINE_TRANSACTION_ID: 16749 THREAD_ID: 55 EVENT_ID: 12 OBJECT_SCHEMA: test OBJECT_NAME: t_student PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 2564926603288 LOCK_TYPE: RECORD LOCK_MODE: X LOCK_STATUS: GRANTED LOCK_DATA: 50 6 rows in set (0.00 sec)
该语句获取了所有id>20的间隙锁。也就是 (20,30],(30,37],(37,49],(49,50],(50,+∞]。
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from t_student where id>20 lock in share mode; +----+-------+------+-----+-------+ | id | no | name | age | score | +----+-------+------+-----+-------+ | 30 | S0004 | Eric | 23 | 91 | | 37 | S0005 | Tom | 22 | 22 | | 49 | S0006 | Tom | 25 | 83 | | 50 | S0007 | Rose | 23 | 89 | +----+-------+------+-----+-------+ 4 rows in set (0.00 sec)
查看锁信息：
mysql> select * from data_locks\G; *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 2564937554624:1065:2564927917080 ENGINE_TRANSACTION_ID: 284039914265280 THREAD_ID: 55 EVENT_ID: 21 OBJECT_SCHEMA: test OBJECT_NAME: t_student PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 2564927917080 LOCK_TYPE: TABLE LOCK_MODE: IS LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 2564937554624:3:4:1:2564926603288 ENGINE_TRANSACTION_ID: 284039914265280 THREAD_ID: 55 EVENT_ID: 21 OBJECT_SCHEMA: test OBJECT_NAME: t_student PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 2564926603288 LOCK_TYPE: RECORD LOCK_MODE: S LOCK_STATUS: GRANTED LOCK_DATA: supremum pseudo-record *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 2564937554624:3:4:5:2564926603288 ENGINE_TRANSACTION_ID: 284039914265280 THREAD_ID: 55 EVENT_ID: 21 OBJECT_SCHEMA: test OBJECT_NAME: t_student PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 2564926603288 LOCK_TYPE: RECORD LOCK_MODE: S LOCK_STATUS: GRANTED LOCK_DATA: 30 *************************** 4. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 2564937554624:3:4:6:2564926603288 ENGINE_TRANSACTION_ID: 284039914265280 THREAD_ID: 55 EVENT_ID: 21 OBJECT_SCHEMA: test OBJECT_NAME: t_student PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 2564926603288 LOCK_TYPE: RECORD LOCK_MODE: S LOCK_STATUS: GRANTED LOCK_DATA: 37 *************************** 5. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 2564937554624:3:4:7:2564926603288 ENGINE_TRANSACTION_ID: 284039914265280 THREAD_ID: 55 EVENT_ID: 21 OBJECT_SCHEMA: test OBJECT_NAME: t_student PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 2564926603288 LOCK_TYPE: RECORD LOCK_MODE: S LOCK_STATUS: GRANTED LOCK_DATA: 49 *************************** 6. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 2564937554624:3:4:8:2564926603288 ENGINE_TRANSACTION_ID: 284039914265280 THREAD_ID: 55 EVENT_ID: 21 OBJECT_SCHEMA: test OBJECT_NAME: t_student PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 2564926603288 LOCK_TYPE: RECORD LOCK_MODE: S LOCK_STATUS: GRANTED LOCK_DATA: 50 6 rows in set (0.00 sec)
表上加了IS锁，每条记录都加了IS锁。
结论：
Select
1. select * from t_student 不会使用任何锁
2. select * from t_student where id=xxx for update 表上加IX锁，当id=xxx的记录存在时，在id索引上加记录锁。当id=xxx不存在时，加在id索引上GAP锁。
3. select * from t_student where no=xxx for update no是唯一索引字段，表上加IX锁
no=xxx存在时，在主键索引上，在no=xxx对应的主键索引上加记录锁，在idx_no索引中，在no=xxx的索引条目上加记录锁
no=xxx不存在时，在idx_no索引中，在no=xxx的索引条目上加Gap lock
4. select * from t_student where name=xxx for update name是普通索引字段，表上加IX锁，
name=xxx存在时，在主键索引上，在name=xxx对应的主键索引上加记录锁，在idx_name索引中，在name=xxx的索引条目上加Next Key Lock
name=xxx不存在时，在idx_name索引中，在name=xxx的索引条目上加Gap锁
5. select * from t_student where age=xxx for update ,age是非索引字段，表上加IX锁，表中所有记录的主键索引 加Next Key Lock，当有事务试图更新这些记录时，需要加X锁，因此会被阻塞。
6. select * from t_student where id=xxxx lock in share mode 表上加IS锁，当id=xxx的记录存在时，加记录锁（S锁，S型记录锁），当id=xxx不存在时，加GAP锁（S锁，S型的间隙锁）。
7. select * from t_student where no=xxxx lock in share mode 表上加IS锁。
no=xxx存在时，在主键索引上，在no=xxx对应的主键索引上加记录锁(S型)，在idx_no索引中，在no=xxx的索引条目上加记录锁（S型）
no=xxx不存在时，在idx_no索引中，在no=xxx的索引条目上加Gap lock（S型）
8. select * from t_student where name=xxxx lock in share mode 表上加IS锁
name=xxx存在时，在主键索引上，在name=xxx对应的主键索引上加记录锁(S型)，在idx_name索引中，在name=xxx的索引条目上加记录锁（S型）
name=xxx不存在时，在idx_name索引中，在name=xxx的索引条目上加Gap lock（S型）
9. select * from t_student where age=xxx lock in share mode ,age是非索引字段，表上加IS锁，表中所有记录的主键索引 加Next Key Lock(S型)，当有事务试图更新这些记录时，需要加X锁，因此会被阻塞。
Update
1. update t_student set xxx where id =xxx 表上加IX锁，如果存在id=xxx的记录，则加X型记录锁，如果不存在加GAP锁。
2. update t_student set xxx where no=xxx, no是唯一索引字段，表上加IX锁
no=xxx存在时，在主键索引上，在no=xxx对应的主键索引上加记录锁，在idx_no索引中，在no=xxx的索引条目上加记录锁
no=xxx不存在时，在idx_no索引中，在no=xxx的索引条目上加Gap lock
3. update t_student set xxx where name=xxx, name是普通索引字段，表上加IX锁
name=xxx存在时，在主键索引上，在name=xxx对应的主键索引上加记录锁，在idx_name索引中，在name=xxx的索引条目上加Next Key Lock
name=xxx不存在时，在idx_name索引中，在name=xxx的索引条目上加Gap锁
4. update t_student set xxx where age=xxx, age是非索引字段，表上加IX锁，表中所有记录的主键索引 加Next Key Lock，当有事务试图更新这些记录时，需要加X锁，因此会被阻塞。
Delete
表现与update相同。
其他 where条件 如 > < like 等 读者可以自行思考和验证。
参考：
https://blog.csdn.net/weixin_44681349/article/details/134898609
https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html
https://dev.mysql.com/doc/mysql-perfschema-excerpt/8.0/en/performance-schema-data-locks-table.html
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· Manus的开源复刻OpenManus初探
· AI 智能体引爆开源社区「GitHub 热点速览」
· 从HTTP原因短语缺失研究HTTP/2和HTTP/3的设计差异
· 三行代码完成国际化适配，妙~啊~