MySql事务隔离级别锁机制实际案例深入分析

矫情吗；* 2022-08-14 04:35 162阅读 0赞

# 1      什么是隔离级别？锁？ #

首先我在此不强调定义，而详细的讨论实际开发中，什么时的情况下会用到隔离级别，什么情况下又会用到锁呢？在我面试过的开发人员中似乎很多人都没有关注过这些，甚至有人听不懂“隔离级别”为何物，的确很多情况下你跟本不用考虑这些。这么说不是没用？在什么情况下会产生影响？会造成那些不一致的情况？带着这些问题，让我们用一个简单的实例来说明，你将恍然大悟。

# 2      实例场景说明 #

在那些场景下会用到呢？不访先举出一些常见的实例，如网上订票、公司内部会议室预定等，这些案例都有一个特点，即都会有很多用户竞争获取这些资源，而从程序的角度看，就是同时有很多事务对数据库中的记录进行更新，如某个用户订到会议室了，则除了会记录登记会议室的地点、使用时间、预订者之外，还会把这段时间会议室标记为不可再申请，即该资源已分配了。

# 3      实例详解 #

## 3.1    创建表、记录 ##

以上述的会议室预订为例，这里为了简单，即不考虑会议室的位置、时间等业务，只有一个标识ID字段和表示会议室个数的字段，申请人预定时提供一个数字表示要预订会议室的数量，创建表和数据的脚本如下：

-- 创建会议室表
    DROP TABLE IF EXISTS `Office`;
    CREATE TABLE `Office` (
      `Id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
      `Cnt` int(11) NOT NULL COMMENT '会议室数量',
      PRIMARY KEY (`Id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;
    
    truncate table Office;
    
    -- 初始100个会议室
    insert into Office values(1,100);

## 3.2    模拟分析各种情况下剩余会议室数量 ##

同样为了方便说明问题，我们用两个查询窗口（实际上通过网站中调用数据库效果一样），来模拟两个或多个事务同时请求情况，这里打开两个窗口，本例我们采用的是Navicate for Mysql工具完成模拟，其中两个事务的执行顺序请按下方的行号来执行（由于事务可以是完全随机发生的，所以这里给出的很多不同的执行顺序）。

l  正常的更新

剩作会议室有两种情况，一种是在事务A提交前事务B超时，这时事务B失败回滚，即不影响数据，那么该值为50，另一种是事务B提交也成功了，则结果为0了。

l  数据产生了不一致情况

上面的业务在订会议室前，没有考虑会议室的数量，就去减，这样很可能会减出负数，一般程序会先判断一下是否有足够的会议室再去减操作，如下：

<table> 
 <tbody> 
 <tr> 
 <td style="background:rgb(217,217,217)"> 事务A </td> 
 <td style="background:rgb(217,217,217)"> 事务B </td> 
 <td style="background:rgb(217,217,217)"> 备注 </td> 
 </tr> 
 <tr> 
 <td> set&nbsp; autocommit=0; </td> 
 <td> &nbsp; </td> 
 <td> 模拟事务，让其不立即提交，下同。 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> set&nbsp; autocommit=0; </td> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> select&nbsp; Cnt from office where id=1; </td> 
 <td> &nbsp; </td> 
 <td> 事务A先查出来看看数量够不够 </td> 
 </tr> 
 <tr> 
 <td> update&nbsp; office set Cnt=Cnt-50 where id=1; </td> 
 <td> &nbsp; </td> 
 <td> 事务A占用了50个 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> select&nbsp; Cnt from office where id=1; </td> 
 <td> 事务B也查出来看看数量够不够，此时仍有100个未占用，这里就产生的判断失误了，失际上只有50个了，这是因为Mysql的默认隔离级别为可重复读 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> update&nbsp; office set Cnt=Cnt-60 where id=1; </td> 
 <td> 事务B占用了60个 </td> 
 </tr> 
 <tr> 
 <td> commit; </td> 
 <td> &nbsp; </td> 
 <td> 提交了事务，这时锁释放了,事务B将自动执行 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> Commit; </td> 
 <td> 事务 </td> 
 </tr> 
 <tr> 
 <td> select&nbsp; Cnt from office where id=1; </td> 
 <td> &nbsp; </td> 
 <td> 查询一下结果 </td> 
 </tr> 
 </tbody> 
</table>

此时的结果，如超时则同上述一样，正常则仍为\-10，这个结果是一致性的，但不是业务上预期的，即不能把会议室订到剩下\-10个，这是由于事务B的中间的一个判断引起的，即可重复读的隔离级别下，数据在整个事务期间读取是一致的，不会受到外部事务的影响。

l  死锁情况

为了避免上述情况，我们在读取时上共享锁，由于更新是独占锁，它们不相容，这样可以避免上述数量判断失误的情况，因为不等两个共享锁释放它们都无法更新，但带来的新的问题，即两个都在无限期的等待对方释方，引起了死锁，如下：

<table> 
 <tbody> 
 <tr> 
 <td style="background:rgb(217,217,217)"> 事务A </td> 
 <td style="background:rgb(217,217,217)"> 事务B </td> 
 <td style="background:rgb(217,217,217)"> 备注 </td> 
 </tr> 
 <tr> 
 <td> set&nbsp; autocommit=0; </td> 
 <td> &nbsp; </td> 
 <td> 模拟事务，让其不立即提交，下同。 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> set&nbsp; autocommit=0; </td> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> select&nbsp; Cnt from office where id=1 lock in share mode; </td> 
 <td> &nbsp; </td> 
 <td> 事务A先查出来看看数量够不够并上共享锁，防止其它事务更新 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> select&nbsp; Cnt from office where id=1 lock in share mode; </td> 
 <td> 事务B也查出来看看数量够不够并上共享锁，防止其它事务更新 </td> 
 </tr> 
 <tr> 
 <td> update&nbsp; office set Cnt=Cnt-50 where id=1; </td> 
 <td> &nbsp; </td> 
 <td> 事务A等待中 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> update&nbsp; office set Cnt=Cnt-50 where id=1; </td> 
 <td> 事务B也提交更新，此时系统检测到死锁，返回了Deadlock found when trying to get lock; try&nbsp; restarting transaction，即事务B失败回滚，这时事务A自动成功执行 </td> 
 </tr> 
 <tr> 
 <td> commit; </td> 
 <td> &nbsp; </td> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> select&nbsp; Cnt from office where id=1; </td> 
 <td> &nbsp; </td> 
 <td> 查询一下结果 </td> 
 </tr> 
 </tbody> 
</table>

此时的结果为50，预期中的结果，但美中不足是事务B失败了，成了牺牲品，这种结果在事务上是一致性的，数据结果符合预期，但是用户体验不好，没有都成功。

l  完善的解决方案

<table> 
 <tbody> 
 <tr> 
 <td style="background:rgb(217,217,217)"> 事务A </td> 
 <td style="background:rgb(217,217,217)"> 事务B </td> 
 <td style="background:rgb(217,217,217)"> 备注 </td> 
 </tr> 
 <tr> 
 <td> set&nbsp; autocommit=0; </td> 
 <td> &nbsp; </td> 
 <td> 模拟事务，让其不立即提交，下同。 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> set&nbsp; autocommit=0; </td> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> select&nbsp; Cnt from office where id=1 for update; </td> 
 <td> &nbsp; </td> 
 <td> 事务A先查出来看看数量够不够并上更新锁（独占），防止其它事务更新 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> select&nbsp; Cnt from office where id=1 for update; </td> 
 <td> 事务B也查出来看看数量够不够并上更新锁（独占），防止其它事务更新，但此时不能再加这个锁，等待中 </td> 
 </tr> 
 <tr> 
 <td> update&nbsp; office set Cnt=Cnt-50 where id=1; </td> 
 <td> &nbsp; </td> 
 <td> 事务A成功执行 </td> 
 </tr> 
 <tr> 
 <td> commit; </td> 
 <td> &nbsp; </td> 
 <td> 事务A提交了，此时事务B自动执行上意向独占锁，并向下执行 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> update&nbsp; office set Cnt=Cnt-50 where id=1; </td> 
 <td> 成功 </td> 
 </tr> 
 <tr> 
 <td> select&nbsp; Cnt from office where id=1; </td> 
 <td> &nbsp; </td> 
 <td> 查询一下结果 </td> 
 </tr> 
 </tbody> 
</table>

此时结果完全符合预期，并且中间的数量验证也符合业务规则，数据可以保证一致性。

通过以上分析，我想你对事务的并发执行、锁机制有一个了解了吧，下面我们，再把焦点转向事务隔离级别。

## 3.3    事务隔离级别 ##

ISO／ANSISQL92规范中就提出了几种不同的事务隔离级别，当然隔离程度是逐步加强的，从最低的级别允许脏读到最高的可串行化，总体理解来说，隔离级别越高，事务间的并发性越是差，即可能因为两个事务有用到相关的数据，会造成一个事务要完全等另一个执行完成才能开始，造成并发和性能大打折扣，所以主流的数据库如Sql server与     oracle都把隔离级别设为已提交读（当然它们已提交读的策略上仍有差异），mysql则为重复读，下面表格为不同的级别下读取情况（注各数据库实现上可能存在差异）：

<table> 
 <tbody> 
 <tr> 
 <td> 隔离级别 </td> 
 <td> 脏读 </td> 
 <td> 非重复读 </td> 
 <td> 幻像读 </td> 
 </tr> 
 <tr> 
 <td> read uncommitted </td> 
 <td> 允许 </td> 
 <td> 允许 </td> 
 <td> 允许 </td> 
 </tr> 
 <tr> 
 <td> read committed </td> 
 <td> &nbsp; </td> 
 <td> 允许 </td> 
 <td> 允许 </td> 
 </tr> 
 <tr> 
 <td> repeatable&nbsp; read </td> 
 <td> &nbsp; </td> 
 <td> &nbsp; </td> 
 <td> 允许 </td> 
 </tr> 
 <tr> 
 <td> serializable </td> 
 <td> &nbsp; </td> 
 <td> &nbsp; </td> 
 <td> &nbsp; </td> 
 </tr> 
 </tbody> 
</table>

这么看，可能没有感觉，下面同样用一个例子来说明，如下：

<table> 
 <tbody> 
 <tr> 
 <td style="background:rgb(217,217,217)"> 事务A </td> 
 <td style="background:rgb(217,217,217)"> 事务B </td> 
 <td style="background:rgb(217,217,217)"> 当前步骤说明 </td> 
 </tr> 
 <tr> 
 <td> SELECT&nbsp; @@GLOBAL.tx_isolation, @@tx_isolation; </td> 
 <td> &nbsp; </td> 
 <td> 则可以看到全局和当前会话所使用的隔离级别为： REPEATABLE-READ即重复读 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> SELECT&nbsp; @@GLOBAL.tx_isolation, @@tx_isolation; </td> 
 <td> 则可以看到全局和当前会话所使用的隔离级别为： REPEATABLE-READ即重复读 </td> 
 </tr> 
 <tr> 
 <td> SET&nbsp; SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT&nbsp; @@GLOBAL.tx_isolation, @@tx_isolation; </td> 
 <td> &nbsp; </td> 
 <td> 把当前会话设置为已提交读 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> SET&nbsp; SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT&nbsp; @@GLOBAL.tx_isolation, @@tx_isolation; </td> 
 <td> 把当前会话设置为已提交读 </td> 
 </tr> 
 <tr> 
 <td> set&nbsp; autocommit=0; </td> 
 <td> &nbsp; </td> 
 <td> 模拟事务，让其不立即提交，下同。 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> set&nbsp; autocommit=0; </td> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> update&nbsp; office set Cnt=Cnt-50 where id=1; </td> 
 <td> &nbsp; </td> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> commit; </td> 
 <td> &nbsp; </td> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> select&nbsp; Cnt from office where id=1 ; </td> 
 <td> 此时读取的是预期值50 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> update&nbsp; office set Cnt=Cnt-50 where id=1; </td> 
 <td> 成功 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> &nbsp; </td> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> select&nbsp; Cnt from office where id=1; </td> 
 <td> &nbsp; </td> 
 <td> 查询一下结果 </td> 
 </tr> 
 </tbody> 
</table>

## 3.4    延伸&拓展 ##

l  本实例使用的所有源码，见附件：

SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
    
    -- 运行设置和不设置已提交读情况
    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
    SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
    
    set autocommit=0; 
    
    -- 分别运行：没有任何执行时和下方的任意一种
    select RunCount from unit where id=53; -- 常规时，互视对方不存在
    select RunCount from unit where id=53 lock in share mode; -- 两者都可以加，但下方更新时会存在死锁情况
    select RunCount from unit where id=53 for update; -- 意向更新锁，此时另一个不能再加这个锁
    
    update unit set RunCount=RunCount-6 where id=53;
    
    select RunCount from unit where id=53;
    
    commit;

你完全可以按照你希望的执行顺序，随意的调整并查看执行结果，从而更深入的体会到事务、锁、隔离级别的奇妙之处。

l  本文保持100%原创，不复制任何其它文章中的内容，当然如果你希望进一步的了解更全面的内容，可访问以下相关网页：

http://www.cnblogs.com/ggjucheng/archive/2012/11/14/2770445.html

[http://www.cnblogs.com/ggjucheng/archive/2012/11/14/2770445.html][http_www.cnblogs.com_ggjucheng_archive_2012_11_14_2770445.html]

http://www.blogjava.net/neverend/archive/2012/03/31/373127.html

[http_www.cnblogs.com_ggjucheng_archive_2012_11_14_2770445.html]: http://www.cnblogs.com/ggjucheng/archive/2012/11/14/2770445.html