概述

本文会简单介绍 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_B 的 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 引擎的隔离级别，以及各个隔离级别下并发问题的处理方式。如果我在实验中有什么遗漏或者错误，务必请指出！谢谢！

This entry was posted on Tuesday, May 12th, 2009 at 09:28 and is filed under Database. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.