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

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	备注
set  autocommit=0;		模拟事务，让其不立即提交，下同。
	set  autocommit=0;
update  office set Cnt=Cnt-50 where id=1;		可以执行成功
	update  office set Cnt=Cnt-50 where id=1;	由于事务A更新了该行记录，未提交，即A上了排它锁，此时将等待下去
commit;		提交了事务，这时锁释放了,事务B将自动执行
	Commit;	事务
select  Cnt from office where id=1;		查询一下结果

 

 

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

 

l  数据产生了不一致情况

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

 

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

 

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

 

l  死锁情况

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

 

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

 

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

 

l  完善的解决方案

 

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

 

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

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

 

3.3    事务隔离级别

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

 

隔离级别	脏读	非重复读	幻像读
read uncommitted	允许	允许	允许
read committed		允许	允许
repeatable  read			允许
serializable

 

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

 

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

 

 

 

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.blogjava.net/neverend/archive/2012/03/31/373127.html