MySQL InnoDB 隔离级别探索

概述

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

4 thoughts on “MySQL InnoDB 隔离级别探索”

  1. 很棒的东西,我这两天也在搞写覆盖的处理方法,得出的结论和你一样。呵呵。
    “幻象”部分的实验结果我还没太理解,为什么会产生这样的幻象呢?能再深入分析分析么?谢谢哈:)

  2. 说的很对:)
    transaction serializable才能解决write cover的问题.
    NEXT-KEY LOCK是来解决phantom rows问题.
    在innodb engine里,deadlocks是来解决transaction concurrency问题.

Leave a Reply

Your email address will not be published. Required fields are marked *