注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

phperwuhan的博客

记载一个phper的历程!phperwuhan.blog.163.com

 
 
 

日志

 
 

MySQL InnoDB 隔离级别探索  

2010-03-26 10:42:04|  分类: mysql |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

来源:http://phpoo.com/?action-viewnews-itemid-92

概述

本文会简单介绍 Mysql 使用的支持事务的存储引擎 InnoDB 的隔离级别,以及每个隔离级别下回产生的并发问题。同时为了更加深刻的理解 InnoDB 引擎的隔离级别,还会探讨如何通过加锁解决不同隔离级别下的并发问题。本文使用的实验环境是 mysql-5.1.33-win32,其他版本的 MySQL 可能会有不同。

隔离级别标准

SQL 标准中定义了四个隔离级别,他们分别是:

READ-UNCOMMITTED

读未提交

READ-COMMITTED

读提交

REPEATABLE-READ

可重复读

SERIALIZABLE

串行化

在 InnoDB 中根据 SQL:1992 事务隔离级别,使用 REPEATABLE-READ 作为默认隔离级别。

并发产生的问题

为了说明下面的并发问题,首先建立一个表 foobar:

DROP TABLE IF EXISTS `foobar`;

CREATE TABLE `foobar` (

`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,

`value` int(10) UNSIGNED NOT NULL DEFAULT '0',

  PRIMARY KEY (`id`)

) ENGINE=InnoDB;

并插入一条数据:

INSERT INTO `foobar`(`value`) VALUES(0);

为了避免隔离级别本身对并发问题的影响,需要将 MySQL 的全局隔离级别设置为最低的 READ-UNCOMMITTED:

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

同时为了模拟并发,下面的存储过程中使用了 SLEEP 函数,以保证语句按照预定顺序执行。

脏读

当两个事物并发时,事务 A 可以读到另事务 B 的中间状态的数据。若事务 B 回滚,或在之后的语句中修改了这个数据,就会有不一致的现象发生。

例如,下面这两个事务:

CREATE PROCEDURE `PROC_FOOBAR_A` ()

BEGIN

START TRANSACTION;

UPDATE `foobar` SET `value`= 100 WHERE `id` = 1;

#SELECT SLEEP(10);

ROLLBACK;

END

CREATE PROCEDURE `PROC_FOOBAR_B` ()

BEGIN

START TRANSACTION;

SELECT * FROM `foobar` f WHERE `id` = 1;

COMMIT;

END

其中,PROC_FOOBAR_A 的 SLEEP 函数的调用是为了模拟并发,让 PROC_FOOBAR_B 的语句正好在 UPDATE 和 ROLLBACK 之间执行。

在两个 session 中按次序执行这两个存储过程。会发现,PROC_FOOBAR_B 的查询语句读取到 id = 1 的记录中 value = 100,而在存储过程执行过后,由于 PROC_FOOBAR_A 的 ROLLBACK 语句的关系,foobar 包中 id = 1 的记录中实际 value = 0。

写覆盖 

当两个事物并发时,事务 A 读出、运算、修改了某一个数据,事务 B 在事务 A 修改之前读出了同一份数据,在事务 A 修改之后修改了同一数据。从而造成事务 A 的修改丢失。

创建下面的存储过程,使得每执行一次这个存储过程 id = 1 的 value 就增加 100:

CREATE PROCEDURE  `PROC_FOOBAR_C`()

BEGIN

START TRANSACTION;

SELECT @v:=`value` FROM `foobar` WHERE `id` = 1;

#SELECT SLEEP(10);

UPDATE `foobar` SET `value`= @v + 100 WHERE `id` = 1;

COMMIT;

END

在两个不同的 session 中顺序执行两次 PROC_FOOBAR_C,由于 SLEEP 函数的存在,就保证了第二次执行的 SELECT 一定在第一次执行的查询语句之后,更新语句之前。假设初始 value = 0,由于写覆盖的存在,两次 PROC_FOOBAR_C 的执行,value 只增长了 100,而不是预期的 200。

实际上,由于 InnoDB 的特性,PROC_FOOBAR_C 这个存储过程即使在 SERIALIZABLE 级别下也是会产生问题的。后面会详细解释这个问题产生的原因以及对应策略。

不可重复读

当两个事务并发时,事务 A 读出了数据,然后事务 B 修改了数据,这时事务 A 再次读出数据时,第一次读出的数据和第二次读出的数据不一致。

创建下面的存储过程:

CREATE PROCEDURE `PROC_FOOBAR_D` ()

BEGIN

START TRANSACTION;

SELECT `value` FROM `foobar` WHERE `id` = 1;

#SELECT SLEEP(10);

SELECT `value` FROM `foobar` WHERE `id` = 1;

COMMIT;

END

CREATE PROCEDURE `PROC_FOOBAR_E` ()

BEGIN

START TRANSACTION;

UPDATE `foobar` SET `value`= 1 WHERE `id` = 1;

COMMIT;

END

在两个 session 中按次序执行这两个存储过程。会发现 PROC_FOOBAR_D 的两次查询结果不一致,对于一些需要复审数据的业务中这会带来严重的影响。

幻像

当两个事务并发时,事务 A 读出了一组数据,然后事务 B 在这组数据上进行了增加或者删除,这样就产生了幻像。

创建下面的存储过程:

CREATE PROCEDURE `PROC_FOOBAR_F` ()

BEGIN

START TRANSACTION;

SELECT * FROM `foobar` WHERE `value` > 100;

#SELECT SLEEP(10);

SELECT * FROM `foobar` WHERE `value` > 100;

DELETE FROM `foobar` WHERE `value` > 100;

SELECT * FROM `foobar` WHERE `value` > 100;

COMMIT;

END

CREATE PROCEDURE `PROC_FOOBAR_G` ()

BEGIN

START TRANSACTION;

SELECT * FROM `foobar` WHERE `value` > 100;

INSERT INTO `foobar` (`value`) VALUES(101),(102),(103);

SELECT * FROM `foobar` WHERE `value` > 100;

#SELECT SLEEP(10);

SELECT * FROM `foobar` WHERE `value` > 100;

COMMIT;

END

并且向表中再插入两条记录:

INSERT INTO `foobar` (`value`) VALUES(200),(300);

在两个 session 中按次序执行这两个存储过程。PROC_FOOBAR_F 在查询时在第一次查询时有 value = {200, 300};在第二次查询时,由于 PROC_FOOBAR_G 插入了三条记录 value = {101, 102, 103},从而得到结果 value = {200, 300, 101, 102, 103};执行 DELETE 语句执行后,第三次查询得到空数据集。PROC_FOOBAR_G 第一次查询时得到 value = {200, 300},第二次查询时已经插入三条记录得到 value = {200, 300, 101, 102, 103},在 PROC_FOOBAR_F 执行了删除操作后的查询反而得到 value = {101, 102, 103}。两个存储过程在执行了删除后得到的数据产生了一个不一致现象。

需要说明的是,这里存在 MySQL 的 InnoDB 在处理上面的特殊性,与其他数据库产生的幻像呈现方式并不一致。

不同隔离级别下可能的并发问题

下表描述了四个隔离级别和并发时产生的问题之间的关系,使用以上存储过程进行测试。这里由于 InnoDB 的全局隔离级别是在设置了隔离级别之后的所有新的 session 都使用的默认隔离级别,这里为了方便起见(不用反复开启新的 session),只设置 session 的隔离级别:

SET SESSION TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED| REPEATABLE READ | SERIALIZABLE};

脏读 写覆盖 不可重复读 幻像

READ-UNCOMMITTED

READ-COMMITTED

REPEATABLE-READ

SERIALIZABLE

否(死锁)

“是”表示会发生并发问题,“否”表示不会发生并发问题。

写覆盖到底怎么了?

可以发现,使用 PROC_FOOBAR_C 做写覆盖的测试时,在所有隔离级别都未能真正解决写覆盖的问题。虽然在 SERIALIZABLE 级别并未发生写覆盖,但是两个事务中必有一个因为死锁而异常中断。这是因为 InnoDB 对于 SELECT 语句处理的一些特殊性决定的。

InnoDB 在 READ-UNCOMMITTED、READ-COMMITTED 和 REPEATABLE-READ 级别中,未明确加锁的 SELECT 语句都使用“持续非锁定读”的查询方式,这种方式下,查询语句不对读取的表加任何锁。在事务内看到的是事务开始时刻前,所有已经提交的事务的结果的快照(利用多版本的方式)。而在 SERIALIZABLE 级别,未明确加锁的 SELECT 语句被隐式转换为 SELECT … LOCK IN SHARE MODE,由于增加了共享锁(读锁),两个并发的事务发生了资源争夺,导致了死锁的发生(默认情况下,MySQL 会中断代价小的那个事务的运行,通常是后执行的事务)。

为了验证这点,对 PROC_FOOBAR_C 做如下修改:

CREATE PROCEDURE `PROC_FOOBAR_C_1`()

BEGIN

START TRANSACTION;

SELECT @v:=`value` FROM foobar WHERE `id` = 1 LOCK IN SHARE MODE;

#SELECT SLEEP(5);

UPDATE `foobar` SET `value`= @v + 100 WHERE `id` = 1;

COMMIT;

END

用同样的方式测试写覆盖,会发现在所有隔离级别下,都会发生死锁。

虽然通过加共享锁的方式解决了写覆盖的问题,但是每次都使用了死锁的方式来避免。这对于一个应用系统来说是很不好的,需要额外增加很多错误处理。

那么让事务真正可序列化的方法是加排他锁(写锁):

CREATE PROCEDURE `PROC_FOOBAR_C_2`()

BEGIN

START TRANSACTION;

SELECT @v:=`value` FROM foobar WHERE `id` = 1 FOR UPDATE;

#SELECT SLEEP(5);

UPDATE `foobar` SET `value`= @v + 100 WHERE `id` = 1;

COMMIT;

END

使用这种方式,可以让两个并发的事务执行,通过其中一个事务等待,而变成顺序执行(序列化执行)。也就真正解决了在 MySQL 的 InnoDB 引擎中的写覆盖问题。

幻像是怎么样解决的?

而对于幻像或者说幽灵问题,InnoDB 使用 Next-Key 锁定,通俗的说也就是对索引加锁(谓词锁)。Next-Key 锁定是联合了记录锁和间隙锁的一个锁形式,通常也被称作谓词锁。记录锁是指加在索引记录上的锁。间隙锁是指加在索引记录之间的缝隙、第一条记录前或最后一条记录后的锁。(基于路神的建议,这里采用更加严谨的方式来描述。)特别值得说明的是,在手册上有这样的描述:InnoDB对索引记录设置的锁定也映像索引记录之前的“间隙”。如果一个用户对一个索引上的记录R有共享或独占的锁定,另一个用户 不能紧接在R之前以索引的顺序插入一个新索引记录。说明 Next-Key 锁在记录上存在共享或独占锁时生效。由于 READ-UNCOMMITTED 级别实际上是不加任何锁的,所以 Next-Key 锁定并不在该级别生效。

请帮助我

本文是为了完成高级数据库这门课的学期作业“默认隔离级别下写覆盖的处理”而编写。但在实验过程中发现 InnoDB 并没有像 SQL Server 那样使用 READ-COMMITTED 作为默认隔离级别,同时对于查询和加锁方式也有不同。觉得有必要仔细探索一下 InnoDB 引擎的隔离级别,以及各个隔离级别下并发问题的处理方式。如果我在实验中有什么遗漏或者错误,务必请指出!谢谢!

  评论这张
 
阅读(970)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017