纠正互联网上关于MySQL普通字段的行锁和表锁的实验
共 7548字，需浏览 16分钟
·
2023-07-17 16:34
以上图片是截取B站某up主的视频，我们通过实验验证这句话的正确性.
CREATE TABLE `t_order` (
`id` int NOT NULL,
`t_no` varchar(64) DEFAULT NULL,
`t_name` varchar(64) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_t_no` (`t_no`)
) ENGINE=InnoDB
表中包含主键（id），普通索引（t_no），以及没有索引的字段（t_name）
表中数据
数据库版本 5.7.37
事务隔离级别RR
开启事务，执行普通字段的更新操作
begin 表示开启事务
update t_order set t_no=‘N00A’ where t_name=‘A’; 中的t_name字段是一个普通字段
通过 show engine innodb status 命令查询事务的锁情况，输出信息如下
------------
TRANSACTIONS
------------
Trx id counter 518176
Purge done for trx's n:o < 518175 undo n:o < 0 state: running but idle
2 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 140556094682880, query id 52 localhost root
在表上加了一个意向排他锁
TABLE LOCK table `db0`.`t_order` trx id 518175 lock mode IX
在主键索引上加了Next-Key锁，具体在哪些主键上加了Next-Key锁呢? 看下面
RECORD LOCKS space id 51 page no 3 n bits 72 index PRIMARY of table `db0`.`t_order` trx id 518175 lock_mode X
在正无穷上加了Next-Key锁
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
在id=1上加了Next-Key锁
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 00000007e81f; asc ;;
2: len 7; hex 38000002040b10; asc 8 ;;
3: len 4; hex 4e303041; asc N00A;;
4: len 1; hex 41; asc A;;
在id=2上加了Next-Key锁
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 6; hex 00000007e814; asc ;;
2: len 7; hex b1000001250110; asc % ;;
3: len 4; hex 4e303032; asc N002;;
4: len 1; hex 42; asc B;;
在id=3上加了Next-Key锁
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000003; asc ;;
1: len 6; hex 00000007e819; asc ;;
2: len 7; hex b4000001280110; asc ( ;;
3: len 4; hex 4e303033; asc N003;;
4: len 1; hex 43; asc C;;
在id=4上加了Next-Key锁
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000004; asc ;;
1: len 6; hex 00000007e81a; asc ;;
2: len 7; hex b5000001290110; asc ) ;;
3: len 4; hex 4e303034; asc N004;;
4: len 1; hex 42; asc B;;
【1】TABLE LOCK table db0.t_order trx id 518175 lock mode IX
表示在表上加了一个意向排他锁，因为事务在获取行级排他锁之前,必须先获取表级意向排他锁
【2】RECORD LOCKS space id 51 page no 3 n bits 72 index PRIMARY of table db0.t_order trx id 518175 lock_mode X
表示在主键索引上需要加Next-Key锁
【3】Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
表示在主键上的正无穷上加了一把Next-Key锁. 主键上的最大值存储在heap no = 1， 主键上的最小值存储在heap no = 0，业务数据是从heap no = 2开始存储
【4】分别在表的每个主键值上加了Next-Key锁
综上，相当于在 (负无穷,1] (1,2] (2,3] (3,4] (4,正无穷) 都加了行级锁，形式上等效于表锁，但是它并不是表锁，它依然是行级锁. 如果加的是一把表锁，反而开销会小很多， 我们的实验数据只有4条，就加了4个行级锁，如果表里有10万条数据，那么就会加10万个行级锁，开销是相当大的.
因此网络上，关于 普通字段检索数据的时候将使用表锁 这个观点是不正确的，且不严谨. 之所以不严谨，是因为它并没有使用表锁，它依然是行级锁，只是这个行级锁将(负无穷，正无穷)都锁住了. 之所以说它不正确，是因为我们的实验是基于RR隔离级别，如果把隔离级别改成RC，再把上面的操作执行一次
将隔离级别改成RC
开启事务，执行更新操作
通过 show engine innodb status 命令查询事务的锁情况
------------
TRANSACTIONS
------------
Trx id counter 518182
Purge done for trx's n:o < 518182 undo n:o < 0 state: running but idle
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 140556094682880, query id 62 localhost root
在表上加了一个意向排他锁
TABLE LOCK table `db0`.`t_order` trx id 518177 lock mode IX
在主键索引上加了行级锁，具体在哪些主键上加了行级锁呢? 看下面
RECORD LOCKS space id 51 page no 3 n bits 72 index PRIMARY of table `db0`.`t_order` trx id 518177 lock_mode X locks rec
but not gap
在id=1上加了行级锁
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 00000007e821; asc !;;
2: len 7; hex 39000001db0e66; asc 9 f;;
3: len 4; hex 4e303041; asc N00A;;
4: len 1; hex 41; asc A;;
【1】TABLE LOCK table db0.t_order trx id 518177 lock mode IX
表示在表上加了一个意向排他锁，因为事务在获取行级排他锁之前,必须先获取表级意向排他锁
【2】RECORD LOCKS space id 51 page no 3 n bits 72 index PRIMARY of table db0.t_order trx id 518177 lock_mode X locks rec but not gap
表示在主键索引上需要加行级锁
【3】在表的id=1的主键上加了行级锁
综上，在RC隔离级别下，它只是在对应记录的主键上加了行级锁，并没有表锁，也没有将(负无穷，正无穷)整个范围加锁.
在实际生产中，大多数选择RC隔离级别.
2.如果是RC隔离级别，会将对应记录的主键上加行级锁
网络上有许多技术言论，大家听之信之，认为就是这样，应该是这样， 然而它与实际的情况还有许多距离，需要我们亲自去探秘.