幻读、间隙锁、行锁、next-key lock、加锁规则、间隙锁导致的死锁、隔离级别设置、for update的理解

朴灿烈づ我的快乐病毒、 2022-03-26 12:00 373阅读 0赞

假设存在如下表：

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);

### 可重复读级别下的幻读实例 ###

<table style="width:575px;"> 
 <tbody> 
  <tr> 
   <td>&nbsp;</td> 
   <td style="width:322px;">session1</td> 
   <td style="width:218px;">session2</td> 
  </tr> 
  <tr> 
   <td>T1</td> 
   <td style="width:322px;"> <p>set autocommit =0;</p> <p>begin;<br> select * from t where d=5;<br> #上面sql结果只有id=5</p> </td> 
   <td style="width:218px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td>T2</td> 
   <td style="width:322px;">&nbsp;</td> 
   <td style="width:218px;"> <p>set autocommit =0;</p> <p>begin;<br> insert into t values(1,1,5);<br> commit;</p> </td> 
  </tr> 
  <tr> 
   <td>T3</td> 
   <td style="width:322px;">insert into t values(1,1,5);<br> #1062 - Duplicate entry '1' for key 'PRIMARY', Time: 0.000000s，但是根本查不到id=1的记录<br> select * from t where d=5;<br> #上面sql结果还是只有id=5，那insert语句的1062错误是怎么报的，这里就是幻读<br> commit;</td> 
   <td style="width:218px;"> <p>&nbsp;</p> </td> 
  </tr> 
 </tbody> 
</table>

分析：上面session1执行insert into t values(1,1,5);报的1062错误就是幻读，session1的第二处select查询是快照读（session1事务对应版本的数据），是不会看到别的事务插入的数据，但session1的insert进行的是当前读，读取的都是最新版本的数据，能读到session2的insert的记录。

幻读：幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。

这里，我需要对“幻读”做一个说明：

*  在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。
 *  幻读仅专指“新插入的行”。

###    可重复读级别下避免幻读 ###

<table style="width:575px;"> 
 <tbody> 
  <tr> 
   <td>&nbsp;</td> 
   <td style="width:322px;">session1</td> 
   <td style="width:218px;">session2</td> 
  </tr> 
  <tr> 
   <td>T1</td> 
   <td style="width:322px;"> <p>begin;<br> select * from t where d=5 &nbsp;for update;<br> #上面sql结果只有id=5</p> </td> 
   <td style="width:218px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td>T2</td> 
   <td style="width:322px;">&nbsp;</td> 
   <td style="width:218px;"> <p>begin;<br> #session1的select增加for update后，下面语句会被blocked，<br> #如果大于50秒session1还没提交，则会报timeout并停止<br> #如果小于50秒session1提交，则会duplicate primary<br> insert into t values(1,1,1) ;<br> commit;</p> </td> 
  </tr> 
  <tr> 
   <td>T3</td> 
   <td style="width:322px;">#下面的语句能正常执行<br> insert into t values(1,1,5);<br> select * from t where d=5;<br> commit;</td> 
   <td style="width:218px;"> <p>&nbsp;</p> </td> 
  </tr> 
 </tbody> 
</table>

避免幻读的方式：上面session1的select增加for update后，session2的insert语句会被blocked，直到session1事务提交，这样幻读就不会出现了，那么这个for update究竟做了什么，实际上，因为d=5没有走索引，所以进行了全表扫描，因此使用了表锁，这里表锁可以这样理解，已有的6条记录均加了行锁，上面6条记录之间也被加了间隙锁，分别是 (-∞,0）、(0,5）、(5,10）、(10,15）、(15,20）、(20, 25）、(25, +suprenum）；下面介绍下间隙锁。

### 间隙锁 ###

注意：可重复读级别下才会有间隙锁！！！！  
数据行是可以加上锁的实体，数据行之间的间隙，也是可以加上锁的实体。但是间隙锁跟我们之前碰到过的锁都不太一样。行锁有冲突关系的是“另外一个行锁”。但是间隙锁不一样，间隙锁之间都不存在冲突关系，跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作，所以上面的例子增加间隙锁以后，session2中就不能插入记录了。

### 间隙锁导致的死锁问题 ###

需求：针对上面的表和里面6条记录，任意锁住一行，如果这一行不存在的话就插入，如果存在这一行就更新它的数据，代码如下：  
begin;  
select \* from t where id=N for update;  
/\* 如果行不存在 \*/  
insert into t values(N,N,N);  
/\* 如果行存在 \*/  
update t set d=N where id=N;  
commit;

假设上面N=9，并发场景如下：  
session A 执行 select ... for update 语句，由于 id=9 这一行并不存在，因此会加上间隙锁 (5,10);  
session B 执行 select ... for update 语句，同样会加上间隙锁 (5,10)，间隙锁之间不会冲突，因此这个语句可以执行成功；  
session B 试图插入一行 (9,9,9)，被 session A 的间隙锁挡住了，只好进入等待；  
session A 试图插入一行 (9,9,9)，被 session B 的间隙锁挡住了。  
至此，两个 session 进入互相等待状态，形成死锁。当然，InnoDB 的死锁检测马上就发现了这对死锁关系，让 session A 的 insert 语句报错返回了。  
所以 间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。

### 间隙锁导致的死锁的解决 ###

因为间隙锁是在可重复读隔离级别下才会生效，所以把隔离级别设置为读提交的话，就没有间隙锁了。但同时，你要解决可能出现的数据和日志不一致问题，需要把 binlog 格式设置为 row。这，也是现在不少公司使用的配置组合。

### 读已提交下的数据和日志不一致问题 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gyNjA0Mzk2NzM5_size_16_color_FFFFFF_t_70][]

因为binlog的落盘按照事务提交的顺序，所以在binlog里面，执行序列是这样的：  
insert into t values(1,1,5); /\*(1,1,5)\*/  
update t set c=5 where id=1; /\*(1,5,5)\*/  
update t set d=100 where d=5;/\* 所有 d=5 的行，d 改成 100\*/  
update t set d=5 where id=0; /\*(0,0,5)\*/  
update t set c=5 where id=0; /\*(0,5,5)\*/

id=1这一行，  
在主库中，因为语句执行顺序原因，执行session1的update t set d=100 where d=5时，session3中id=1的记录还没有插入，所以id=1的行最终也还是d=5  
在binlog中，因为session3先提交，所以先记录session3的insert into t values(1,1,5);，再执行session2的update t set d=100 where d=5，且update执行的操作是当前读（读最新），所以binglog执行id=1的结果是d=100  
这样如果你拿着binglog做主从或者克隆一个库，id=1记录都会因为d发生数据的不一致性。

问题： binlog 格式设置 row，那么binlog是怎么记录的呢？难道不是按照事务，而是按照sql实际的执行顺序？--笔者也不清楚

### mysql设置隔离级别 ###

SET \[SESSION|GLOBAL\] TRANSACTION ISOLATION LEVEL \[READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE\]

SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;  
上面语句执行后你执行SELECT @@tx\_isolation，会发现隔离级别并没有改变，只要你关闭所有的数据库会话，然后重新连接，新的隔离级别才会生效

### 隔离级别和条件是否走索引对select for update的影响 ###

对select for update而言，只有通过索引条件检索数据，InnoDB才会使用行级锁，否则，InnoDB将使用表锁！

还是开始的表结构，需要明确的是字段c是索引，字段d不是索引；set autocommit =0;自动提交已关闭！

where条件不走索引  
select \* from t where d=5  for update;（c是索引，d不是索引）  
可重复读级别下：上锁（next-key lock）范围内insert都不可以，一般的select都可以，select for update都不可以，因为d=5条件没有走索引，所以此时是表锁  
读已提交级别下：insert都可以，一般的select都可以，select for update都不可以，此时是表锁

这里再说明下，可重复读级别下不走索引的上锁方式，因为不走索引，所以是进行了全表扫描，实际是表锁，即锁住所有行和间隙，分别是 (-∞,0\]、(0,5\]、(5,10\]、(10,15\]、(15,20\]、(20, 25）、(25, +suprenum\]

where条件走索引：  
select \* from t where c=5  for update;（c是索引），

可重复读级别下：上锁范围内insert不可以，一般的select都可以，select c=5  for update不可以，别的for update都可以，因为走了索引，此时行锁，仅仅锁了id=5一行记录，同时加了所有的间隙锁  
读已提交级别下：insert都可以，一般的select都可以，select \* from t where c=5  for update 不可以，别的for update都可以，此时仅仅锁了c=5那一行，因为c是索引。

总结：  
Innodb中select for update的使用：  
只要是可重复读隔离级别下，此时一定会有间隙锁，被锁范围内insert一定不可以  
只要where条件走了索引，则只会对条件命中的记录上行锁，此时别的select for update不会阻塞；如果where条件没有走索引，就会上表锁，导致所有的select for update阻塞。其实和表锁效果一样，此时加的就是表锁。

### innodb中行锁、间隙锁与表的增删该查的关系 ###

行锁包括两种锁，行共享锁和行排他锁，排他锁与两种锁均互斥，共享锁之间不互斥  
行锁的加锁时间：执行语句需要的时候加上，用完也不释放，事务commit完才会释放  
间隙锁与插入操作互斥  
select可以获得两种锁：普通select不加锁，select lock in share mode获取行共享锁，select for update获得排他锁  
update delete和insert均在执行的时候获取行排他锁。

### 间隙锁，next-key  lock、行锁之间的关系 ###

以上面(0,0,0),(5,5,5)两条记录为例来讲解，  
间隙锁是(0,5),此时id=0和id=5都没有上锁，但是0<id<5范围内上锁，不允许另一事务在该范围内插入、删除和update  
行锁是id=0和id=5两行上面的锁，不能对id=0和id=5做插入删除和update的操作  
next-key lock：对（0,5\]范围内上锁，实际是间隙锁+行锁，此时0<id<=5上锁，该范围内不允许插删更。

### next-key加锁规则里面，包含了两个“原则”、两个“优化”和一个“bug” ###

原则 1：加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间,如（5，10\]。  
原则 2：查找过程中访问到的对象才会加锁。  
优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。  
优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。  
一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

下面介绍下几种上锁的实际案例，用于理解上面的规则。环境都是 INnodb ，可重复读隔离级别

初始案例  
\#session1  
begin;  
select \* from t where c=5  lock in share mode ;  
\#session2  
\#因为c=9，会被阻塞  
insert into t values(28,9,22);  
\#不会被阻塞  
insert into t values(28,10,22);  
c=5条件会加两个next-key lock，c的值上锁（0，5\]和（5，10\],根据优化2，（5，10\]会退化成（5，10）  
所以最终锁：（0，5\]和（5，10)，即c的值在上面范围内均不可插入。

这里说明一点，where 中条件的字段是那一列，就会对那一列上锁，例如上面where c =5则只会对c字段上锁。

案例1-行锁可以加到整行上，也可以加到索引上  
innodb 可重复读隔离级别 字段id主键，字段c索引，关闭自动提交  
\#session1  
begin;  
select \* from t where c=5  lock in share mode ;  
\#session2 下面的sql会被阻塞  
update t  set d=d+1 where id=5

但是如果将session1中的select \* 修改为select id，你会发现session2中的update不会阻塞，会正常执行，原因：select id时走的是覆盖索引，此时并没有将id=5的记录上行锁，仅仅是将c字段对应的索引上c=5处加了行锁，故如果你在session2中继续执行update t  set c=c+1 where id=5;就会被阻塞。

案例二  
begin;  
update t set d=d+1 where id=7 ;  
刚开始加的锁是（5，10\],根据优化2，最终锁范围是（5，10）的间隙锁，不包括id=10

案例三 主键索引范围锁  
select \* from t where id>=10 and id<11 for update;  
对于条件id>=10,（5，10\]的next-key lock，因为是id是唯一索引，根据优化1，退化为id=10的行锁  
id<11,加（10，15\]的next-key lock，并且是范围查询，不会执行优化2  
最终的锁： id=10行锁+（10，15\]的next-key lock

案例四 非唯一索引范围锁  
select \* from t where c>=10 and c<11 for update;  
对于条件c>=10,c索引加上（5，10\]的next-key lock，因为是c不是唯一索引，锁不会退化  
c<11，c索引加（10，15\]的next-key lock，并且是范围查询，不会执行优化2  
最终的锁： （5，10\]的next-key lock+（10，15\]的next-key lock

案例五  
select \* from t where id>10 and id<=15 for update;  
条件id>10 会加（10，15\] next-key lock  
id<=15 会根据上面规则中的bug加一个（15，20\] next-key lock  
所以总共的锁：（10，15\] +（15，20\]

案例6  
先插入一条记录，insert into t values(30,10,30);，此时c的索引结构如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gyNjA0Mzk2NzM5_size_16_color_FFFFFF_t_70 1][]

执行delete from t where c=10;  
先访问第一个 c=10 的记录。同样地，根据原则 1，这里加的是 (c=5,id=5) 到 (c=10,id=10) 这个 next-key lock。  
然后， 向右查找，直到碰到 (c=15,id=15) 这一行，循环才结束。根据优化 2，这是一个等值查询，向右查找到了不满足条件的行，所以会退化成 (c=10,id=10) 到 (c=15,id=15) 的间隙锁。所以delete 语句在索引 c 上的加锁范围，就是下图中蓝色区域覆盖的部分：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gyNjA0Mzk2NzM5_size_16_color_FFFFFF_t_70 2][]

如果delete语句修改为elete from t where c=10 limit 2;呢  
delete 语句明确加了 limit 2 的限制，因此在遍历到 (c=10, id=30) 这一行之后，满足条件的语句已经有两条，循环就结束了。  
因此，索引 c 上的加锁范围就变成了从（c=5,id=5) 到（c=10,id=30) 这个前开后闭区间，如下图所示：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gyNjA0Mzk2NzM5_size_16_color_FFFFFF_t_70 3][]

案七：next-key lock 实际上是间隙锁和行锁加起来的结果

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gyNjA0Mzk2NzM5_size_16_color_FFFFFF_t_70 4][]

现在，我们按时间顺序来分析一下为什么是这样的结果。  
session A 启动事务后执行查询语句加 lock in share mode，在索引 c 上加了 next-key lock(5,10\] 和间隙锁 (10,15)；  
session B 的 update 语句也要在索引 c 上加 next-key lock(5,10\] ，进入锁等待；  
然后 session A 要再插入 (8,8,8) 这一行，被 session B 的间隙锁锁住。由于出现了死锁，InnoDB 让 session B 回滚。你可能会问，session B 的 next-key lock 不是还没申请成功吗？  
其实是这样的，session B 的“加 next-key lock(5,10\] ”操作，实际上分成了两步，先是加 (5,10) 的间隙锁，加锁成功；然后加 c=10 的行锁，这时候才被锁住的。  
 也就是说，我们在分析加锁规则的时候可以用 next-key lock 来分析。但是要知道，具体执行的时候，是要分成间隙锁和行锁两段来执行的。

说明：部分内容借鉴于极客时间 mysql实战45讲。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gyNjA0Mzk2NzM5_size_16_color_FFFFFF_t_70]: /images/20220326/81f631593cad47beade7d3fb5e526a52.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gyNjA0Mzk2NzM5_size_16_color_FFFFFF_t_70 1]: /images/20220326/55119b949fc14f59b8615b19b717c7a5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gyNjA0Mzk2NzM5_size_16_color_FFFFFF_t_70 2]: /images/20220326/6d98f2c1feb14c16be89a17605dc86db.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gyNjA0Mzk2NzM5_size_16_color_FFFFFF_t_70 3]: /images/20220326/9137e5a7e13549f2bd526b18f85fb03c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gyNjA0Mzk2NzM5_size_16_color_FFFFFF_t_70 4]: /images/20220326/4f3f495736624a2c9187e6625977dbdb.png