MySQL加锁分析

系列文章目录

第一章：sql_mode模式
第二章：optimize table、analyze table、alter table、gh-ost
第三章：InnoDB MVCC原理
第四章：sql语句执行过程
第五章：Percona Toolkit工具简介
第六章：MySQL索引
第七章：MySQL的锁
第八章：MySQL加锁分析

---

---

前言

MySQL InnoDB默认的事务隔离级别是可重复读，可重复读事务隔离级别为了解决幻读的问题，加了next-key 锁。本文以可重复读事务隔离级别，数据库是MySQL 8.0.22版本数据上面操作实践。不同的版本，autocommit=1加锁范围有一些区别。

测试所用的表结构和数据如下所示：

CREATE TABLE `t` ( 
 `id` int(11) NOT NULL,  
 `c` int(11) DEFAULT NULL, 
  `d` int(11) DEFAULT NULL,  
  PRIMARY KEY (`id`),  
  KEY `c` (`c`)) ENGINE=InnoDB;
  
  insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);
  
  show engine innodb status\G
  
  SELECT ENGINE,ENGINE_TRANSACTION_ID,THREAD_ID,EVENT_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE, LOCK_MODE,LOCK_STATUS,LOCK_DATA FROM performance_schema.data_locks;

一、等值查询

1.唯一索引

1.1 查询存在的值

1.1.1 场景

sessionA	sessionB	sessionC
begin；select * from t where id = 10 for update;
	insert into t values (8,8,8); (Insert OK)
		select * from t where id = 15 for update; (Query OK)
commit;

1.1.2 结果

索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。所以，sessionA锁定的是id=10这一行，和sessionB、sessionC不会出现冲突。实际执行结果如下所示：

查看data_locks的结果如下，在访问数据表，会自动加上MDL锁。此外，可以看出来select * from t where id = 10 for update; 执行，将独占记录锁加在主键上，此次锁住的是id=10。

1.2 查询不存在的值

1.2.1 场景

sessionA	sessionB	sessionC
begin；select * from t where c = 7 for update;
	insert into t values(8,8,8); (Query Block)
		select * from t where c = 10 for update; (Query OK)
commit;

1.2.2 结果

加锁单位是 next-key lock，session A 加锁范围就是 (5,10]；由于这是一个等值查询 (id=7)，而 id=10 不满足查询条件，next-key lock 退化成间隙锁，因此最终加锁的范围是 (5,10)。所以sessionB被锁住，sessionC查询成功。

2.普通索引

2.1 查询存在的值

2.1.1 场景

sessionA	sessionB	sessionC
begin；select * from t where c = 10 for update;
	insert into t values (8,8,8); (Query Block)
		select * from t where c = 15 for update; (Query OK)
commit;

2.1.2 结果

加锁单位是 next-key lock，session A 加锁范围就是 (5,10], (10, 15]；由于这是一个等值查询 (id=10)，而 id=15 不满足查询条件，next-key lock 退化成间隙锁，因此(10, 15]加锁的范围变为 (10,15)，最终锁的范围是(5, 10], (10, 15)。所以sessionB被锁住，sessionC查询成功。

从data_locks查询的数据，可以看出来，不仅仅锁定了索引c，也锁了主键索引。但是这个也不是绝对，要分两种情况，一个是查询的数据，只有访问到的对象才会加锁。另外就是lock in share mode 只锁覆盖索引，但是for update 时，系统会认为接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。

• select * from t where c = 10 for update;
• select id from t where c = 10 for update; - select * from t where c = 10 lock in share mode;
• select id from t where c = 10 lock in share mode;

1.2 查询不存在的值

1.2.1 场景

sessionA	sessionB	sessionC
begin；select * from t where c = 7 for update;
	insert into t values(8,8,8); (Query Block)
		select * from t where c = 10 for update; (Query OK)
commit;

1.2.2 结果

加锁单位是 next-key lock，session A 加锁范围就是 (5,10]；由于这是一个等值查询 (id=7)，而 id=10 不满足查询条件，next-key lock 退化成间隙锁，因此最终加锁的范围是 (5,10)。所以sessionB被锁住，sessionC查询成功。

3.无索引

除了唯一索引，其他的查询，不管值是否存在，都会琐间隙，因此放在一起讨论。

3.1 场景

sessionA	sessionB	sessionC
begin；select * from t where d = 10 for update;
	insert into t values (8,8,8); (Query Block)
		select * from t where d = 15 for update; (Query OK)
commit;

3.1.1 结果

加锁单位是 next-key lock，但是由于没有索引，所以sessionA的加锁范围是整个表。

二、范围查询

1.唯一索引

1.1 范围查询

1.1.1 场景

sessionA	sessionB	sessionC
begin；select * from t where id >= 10 and id <11 for update;
	insert into t values (8,8,8); (Insert OK)
	insert into t values (13,13,13); (Insert Block)
		select * from t where d = 15 for update; (Query OK)
commit;

1.1.2 结果

开始执行的时候，要找到第一个 id=10 的行，因此本该是 next-key lock(5,10]。 根据主键 id 上的等值条件，退化成行锁，只加了 id=10 这一行的行锁。范围查找就往后继续找，找到 id=15 这一行停下来，因此需要加 next-key lock(10,15]。

1.2 范围查询+指定顺序

1.2.1 场景

sessionA	sessionB	sessionC
begin；select * from t where id >= 10 and id <11 order by id desc for update;
	insert into t values (8,8,8); (Insert Block)
	insert into t values (13,13,13); (Insert Block)
		select * from t where d = 15 for update; (Query OK)
commit;

1.2.2 结果

所以搜索就是找到第一个值，然后向左或向右遍历，order by desc 就是用最大值来找第一个；order by asc 就是用最小值来找找第一个。在引擎内部，其实是要找到 id=11 的这个值，只是最终没找到，但找到了 (10,15) 这个间隙。范围查找就往后继续找，查找id=10，找到[5, 10]，所以锁定的范围为[5,10],(10, 15).

1.3 范围查询加limit

1.3.1 场景

sessionA	sessionB	sessionC
begin；select * from t where id >= 10 and id <11 order by id desc for update limit 1;
	insert into t values (8,8,8); (Insert OK)
	insert into t values (13,13,13); (Insert Block)
		select * from t where d = 15 for update; (Query OK)
commit;

1.3.2 结果

所以搜索就是找到第一个值，然后向左或向右遍历，order by desc 就是用最大值来找第一个；order by asc 就是用最小值来找找第一个。在引擎内部，其实是要找到 id=11 的这个值，只是最终没找到，但找到了 (10,15) 这个间隙。范围查找就往后继续找，查找id=10符合条件，则(5, 10]，所以锁定的范围为(5,10],(10, 15).

2.普通索引

与唯一索引类似，不在赘述。

3.无索引

与唯一索引类似，不在赘述。