Mysql数据库事务和锁的关系以及演化

女爷i 2022-05-13 08:36 183阅读 0赞

### 文章目录 ###

*   *  前言
     *  mysql架构
     *  快照读／当前读
     *  MVCC
     *  S锁／X锁
     *  两阶段锁
     *  隔离级别
     *  幻读
     *  不可重复读
     *  间隙锁／next-key锁／插入意向锁
     *  死锁分析
     *   *  死锁原理
         *  一个next-key引发死锁的案例
         *   *  模拟事件
             *  测试结果
             *  原因分析
             *  解决方案
     *  参考文档

## 前言 ##

*  一直认为事务的隔离基本实现方式就是锁，这次可以总结一下
 *  最近阅读了很多大佬对于mysql事务隔离级别和锁相关的文章，终于茅塞顿开，特此总结

## mysql架构 ##

这个其实可以做一个专门的主题来讲，这里先过

## 快照读／当前读 ##

快照读：select \* from T;简单的select操作，属于快照读，不加锁。  
当前读：**select \* from table where ? lock in share mode;**  
select \* from table where ? for update;  
insert into table values (…);  
update table set ? where ?;  
delete from table where ?;  
特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

快照读 (snapshot read)与当前读 (current read)。快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。

## MVCC ##

MVCC (Multi-Version Concurrency Control)  
**简单理解**：MVCC，多版本并发控制技术。在InnoDB中，在每一行记录的后面增加两个隐藏列，记录创建版本号和删除版本号。通过版本号和行锁，从而提高数据库系统并发性能。  
**真实原理**：[Mysql中的MVCC][Mysql_MVCC]  
[关于innodb中MVCC的一些理解][innodb_MVCC]

## S锁／X锁 ##

S锁：select \* from table where ? lock in share mode;  
X锁：增删改都是，select \* from table where ? for update;也是

## 两阶段锁 ##

2PL就是将加锁/解锁分为两个完全不相交的阶段。加锁阶段：只加锁，不放锁。解锁阶段：只放锁，不加锁。

## 隔离级别 ##

**Serializable**  
从MVCC并发控制退化为基于锁的并发控制。不区别快照读与当前读，所有的读操作均为当前读，读加读锁 (S锁)，写加写锁 (X锁)。  
Serializable隔离级别下，读写冲突，因此并发度急剧下降，在MySQL/InnoDB下不建议使用。  
**Repeatable Read (RR)**  
针对当前读，RR隔离级别保证对读取到的记录加锁 (记录锁)，同时保证对读取的范围加锁，新的满足查询条件的记录不能够插入 (间隙锁)，不存在幻读现象。  
**Read Committed (RC)**  
针对当前读，RC隔离级别保证对读取到的记录加锁 (记录锁)，存在幻读现象。  
还存在不可重复读现象  
**Read Uncommited**  
可以读取未提交记录（出现幻读／不可重复读／脏读）。此隔离级别，不会使用

## 幻读 ##

上文提到的幻读现象，即一个事务读到另一个事务已提交的insert数据。  
网上很多地方说mysql的RR级别能解决幻读，这句话让我困扰好久。  
先来个例子：（RR,n非唯一索引，没有使用gap锁）  
例子1:t(id,n)

<table> 
 <thead> 
  <tr> 
   <th>时间点</th> 
   <th>事务1</th> 
   <th>事务2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>begin</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>select * from t where n =1 ;</td> 
   <td>begin</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td></td> 
   <td>insert into t values(2,1);</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td></td> 
   <td>commit;</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>select * from t where n = 1;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>commit;</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

请问上面第五步事务1查出来的结果有几条，是否包括事务2的那一条？  
答案是1条，不包括事务2的那一条。  
那就奇怪了，不是RR级别有幻读吗？这不就是幻读吗？  
其实这个只是还没达到幻读出现的条件。事务1的第2步和第5步，都是快照读，而且是同一个版本

再来个例子：（RC,n非唯一索引，没有使用gap锁）  
例子2:t(id,n)

<table> 
 <thead> 
  <tr> 
   <th>时间点</th> 
   <th>事务1</th> 
   <th>事务2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>begin</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>select * from t where n = 1;</td> 
   <td>begin</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td></td> 
   <td>insert into t values(2,1);</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td></td> 
   <td>commit;</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>select * from t where n = 1;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>commit;</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

这种情况下，会出现幻读，事务1 的第2步和第5步拿到的结果不一样，第5步读取到了事务2已提交的数据。这是为什么呢？

解答： 不同事务隔离级别下，快照读的区别： READ COMMITTED 隔离级别下，每次读取都会重新生成一个快照，所以每次快照都是最新的，也因此事务中每次SELECT也可以看到其它已commit事务所作的更改；REPEATED READ 隔离级别下，快照会在事务中第一次SELECT语句执行时生成，只有在本事务中对数据进行更改才会更新快照，因此，只有第一次SELECT之前其它已提交事务所作的更改你可以看到，但是如果已执行了SELECT，那么其它事务commit数据，你SELECT是看不到的。

那么，RR级别情况下，怎么会出现幻读呢？看例子：  
例子3:t(id,n)

<table> 
 <thead> 
  <tr> 
   <th>时间点</th> 
   <th>事务1</th> 
   <th>事务2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>begin</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>select * from t;</td> 
   <td>begin</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td></td> 
   <td>insert into t values(1,1);</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td></td> 
   <td>commit;</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>select * from t;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>update t set n = 3;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>select * from t;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>8</td> 
   <td>commit;</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

这个实验发现，第7步和2，5步结果不一样，第七步能查到t(1,3)这条数据，而2，5步却查到的是空  
这就是出现了幻读，事务1中对数据进行了更改，所以，第7步生成的快照更新了，读取了事务2已提交的数据，这个幻读该如何处理呢？  
放心，mysql的gap锁可以解决这个问题。但是，InnoDB提供了next-key locks，但需要应用程序自己去加锁，这句话怎么理解呢？  
对于例子3，即使开启了间隙锁，也没有用，需要自己手动加锁，比如：

SELECT * FROM child WHERE id > 100 FOR UPDATE;

这样，InnoDB会给id大于100的行（假如child表里有一行id为102），以及100-102，102+的gap都加上锁。  
例子4:

<table> 
 <thead> 
  <tr> 
   <th>时间点</th> 
   <th>事务1</th> 
   <th>事务2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>begin</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>SELECT * FROM t_bitfly WHERE id&lt;=1 FOR UPDATE;</td> 
   <td>begin</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td></td> 
   <td>INSERT INTO t_bitfly VALUES (2, ‘b’);</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>SELECT * FROM t_bitfly;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td></td> 
   <td>INSERT INTO t_bitfly VALUES (0, ‘0’);(waiting for lock …then timeout)ERROR 1205 (HY000):Lock wait timeout exceeded;try restarting transaction</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>SELECT * FROM t_bitfly;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td></td> 
   <td>commit;</td> 
  </tr> 
 </tbody> 
</table>

第5步，间隙锁生效，等待。

此处见详细链接：[MySQL的InnoDB的幻读问题][MySQL_InnoDB]

## 不可重复读 ##

上面分析了幻读，再来分析一下不可重复读。不可重复读，顾名思义，就是不能重复读，神马意思呢？同一条数据，同一个事务中，两次读取的结果不一样，这样就不能重复读啦！这里指的是，读取到别的事务已提交的结果。  
为什么会这样呢？  
其实区别上面已经说了，RC级别下，每次快照读都是拿的最新的版本数据，所以会读到其他事物已提交的数据。

很多人容易搞混不可重复读和幻读，确实这两者有些相似。但不可重复读重点在于update和delete，而幻读的重点在于insert。

## 间隙锁／next-key锁／插入意向锁 ##

**间隙锁**，锁间隙的意思就是锁定某一个范围，间隙锁又叫gap锁，其不会阻塞其他的gap锁，但是会阻塞插入间隙锁，这也是用来防止幻读的关键。innodb自动开启，设置参数：  
innodb\_locks\_unsafe\_for\_binlog：默认值为0，即启用gap lock。

**next-key锁**：  
这个锁本质是记录锁加上gap锁。在RR隔离级别下(InnoDB默认)，Innodb对于行的扫描锁定都是使用此算法，但是如果查询扫描中有唯一索引会退化成只使用记录锁。为什么呢?  
因为唯一索引能确定行数，而其他索引不能确定行数，有可能在其他事务中会再次添加这个索引的数据会造成幻读。

因为InnoDB对于行的查询都是采用了Next-Key Lock的算法，锁定的不是单个值，而是一个范围。上面索引有1，3，5，8，11，被Next-Key Locking的区间为：  
（-∞,1），（1,3\]，（3,5\]，（5,8\]，（8,11\]，（11,+∞）

**插入意向锁**：  
可以看出插入意向锁是在插入的时候产生的,在多个事务同时写入不同数据至同一索引间隙的时候，并不需要等待其他事务完成，不会发生锁等待。假设有一个记录索引包含键值4和7，不同的事务分别插入5和6，每个事务都会产生一个加在4-7之间的插入意向锁，获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突。

这里要说明的是如果有间隙锁了，插入意向锁会被阻塞。

## 死锁分析 ##

### 死锁原理 ###

死锁的发生与否，并不在于事务中有多少条SQL语句，死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。而使用本文上面提到的，分析MySQL每条SQL语句的加锁规则，分析出每条语句的加锁顺序，然后检查多个并发SQL间是否存在以相反的顺序加锁的情况，就可以分析出各种潜在的死锁情况，也可以分析出线上死锁发生的原因。

### 一个next-key引发死锁的案例 ###

#### 模拟事件 ####

创建表student:

CREATE TABLE `LockTest` (
       `order_id` varchar(20) NOT NULL,
       `id` bigint(20) NOT NULL AUTO_INCREMENT,
       PRIMARY KEY (`id`),
       KEY `idx_order_id` (`order_id`)
     ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

例子4:

<table> 
 <thead> 
  <tr> 
   <th>时间点</th> 
   <th>事务1</th> 
   <th>事务2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>begin</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>delete from LockTest where order_id = ‘D20’</td> 
   <td>begin</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td></td> 
   <td>delete from LockTest where order_id = ‘D19’</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>insert into LockTest (order_id) values (‘D20’)</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td></td> 
   <td>insert into LockTest (order_id) values (‘D19’)</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>commit</td> 
   <td>commit;</td> 
  </tr> 
 </tbody> 
</table>

#### 测试结果 ####

事务1 执行到insert语句会block住，事务2执行insert语句会提示死锁错误。

#### 原因分析 ####

*  1、首先看测试表的建表语句，id是主键索引，同时该主键是自增主键。order\_id是普通索引。
 *  2、事务1执行delete from LockTest where order\_id = ‘D20’;语句时，由于数据库的隔离级别是RR，因此此时事务1在主键id上获得了一个next-key lock，这个锁的范围是\[16, +∞)。  
    这个16就来自于AUTO\_INCREMENT=16，因为LockTest目前是张空表。
 *  3、同理，事务2执行delete from LockTest where order\_id = ‘D19’;语句时，由于数据库的隔离级别是RR，事务2在主键id上也获得了一个next-key lock，这个锁的范围是\[16, +∞)。  
    也就是说此时，事务1和事务2获得的锁是一样的。
 *  4、事务1继续执行insert into LockTest (order\_id) values (‘D20’);语句，这个时候由于该语句企图往LockTest表insert一行id=16，order\_id=D20的数据，  
    但是由于在事务2的delete语句中，主键id上已经有了一个范围为\[16, +∞)的锁，导致事务1此时想插入数据插不进去，被阻塞了。
 *  5、继续事务2的插入语句insert into LockTest (order\_id) values (‘D19’); 该插入语句同样也想往LockTest表insert一行id=16，order\_id=D19的数据，  
    但是由于由于在事务1的delete语句中，主键id上已经有了一个范围为\[16, +∞)的锁，导致事务2此时想插入数据插不进去，被阻塞了。  
    此时，可以发现，事务1和事务2的锁是互相持有，互相等待的。所以innodb判断该事务遇到了死锁，直接将事务2进行了回滚。然后回头去看事务1，insert into LockTest (order\_id) values (‘D20’)；被成功执行。

**原因总结**：间隙锁和插入意向锁的冲突，导致了阻塞

#### 解决方案 ####

*  方案一:隔离级别降级为RC，在RC级别下不会加入间隙锁，所以就不会出现毛病了，但是在RC级别下会出现幻读，可提交读都破坏隔离性的毛病，所以这个方案不行。
 *  方案二:隔离级别升级为可序列化，小明经过测试后发现不会出现这个问题，但是在可序列化级别下，性能会较低，会出现较多的锁等待，同样的也不考虑。
 *  方案三:修改代码逻辑，不要直接删，改成每个数据由业务逻辑去判断哪些是更新，哪些是删除，那些是添加，这个工作量稍大，小明写这个直接删除的逻辑就是为了不做这些复杂的事的，所以这个方案先不考虑。
 *  方案四:较少的修改代码逻辑，在删除之前，可以通过快照查询(不加锁)，如果查询没有结果，则直接插入，如果有通过主键进行删除，在之前第三节实验2中，通过唯一索引会降级为记录锁，所以不存在间隙锁。

## 参考文档 ##

*  [https://tech.meituan.com/innodb\_lock.html][https_tech.meituan.com_innodb_lock.html]
 *  [http://hedengcheng.com/?p=771\#\_Toc374698317][http_hedengcheng.com_p_771_Toc374698317]
 *  [https://juejin.im/post/5b6c5be86fb9a04fb30a2bc7][https_juejin.im_post_5b6c5be86fb9a04fb30a2bc7]
 *  [https://dbaplus.cn/news-160-1729-1.html][https_dbaplus.cn_news-160-1729-1.html]
 *  [http://blog.sina.com.cn/s/blog\_499740cb0100ugs7.html][MySQL_InnoDB]
 *  [https://blog.csdn.net/chen77716/article/details/6742128][Mysql_MVCC]
 *  [https://blog.csdn.net/cxm19881208/article/details/79415726][https_blog.csdn.net_cxm19881208_article_details_79415726]
 *  [https://blog.csdn.net/mysteryhaohao/article/details/51669741][https_blog.csdn.net_mysteryhaohao_article_details_51669741]

[Mysql_MVCC]: https://blog.csdn.net/chen77716/article/details/6742128
[innodb_MVCC]: http://www.cnblogs.com/chenpingzhao/p/5065316.html
[MySQL_InnoDB]: http://blog.sina.com.cn/s/blog_499740cb0100ugs7.html
[https_tech.meituan.com_innodb_lock.html]: https://tech.meituan.com/innodb_lock.html
[http_hedengcheng.com_p_771_Toc374698317]: http://hedengcheng.com/?p=771#_Toc374698317
[https_juejin.im_post_5b6c5be86fb9a04fb30a2bc7]: https://juejin.im/post/5b6c5be86fb9a04fb30a2bc7
[https_dbaplus.cn_news-160-1729-1.html]: https://dbaplus.cn/news-160-1729-1.html
[https_blog.csdn.net_cxm19881208_article_details_79415726]: https://blog.csdn.net/cxm19881208/article/details/79415726
[https_blog.csdn.net_mysteryhaohao_article_details_51669741]: https://blog.csdn.net/mysteryhaohao/article/details/51669741