纠正互联网上关于MySQL行锁和表锁的言论

作者: 赵信信官属 | 来源:发表于2023-03-31 11:00 被阅读0次

mysql中的"锁"事儿总结
InnoDB引擎的行锁和表锁
总结Mysql中的锁
MS汇总
MySQL知识点
Mysql的锁
MySQL锁系列之锁的种类和概念
Mysql的全局锁和表锁
MySQL锁机制
Mysql的行级锁 -- 共享锁和排他锁

某up主视频

以上图片是截取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

隔离级别改成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隔离级别.

【结论】普通字段作为WHERE条件的更新操作
1.如果是RR隔离级别，会将主键的(负无穷，正无穷)加Next-Key锁
2.如果是RC隔离级别，会将对应记录的主键上加行级锁

网络上有许多技术言论，大家听之信之，认为就是这样，应该是这样，然而它与实际的情况还有许多距离，需要我们亲自去探秘.