浅析MySQL InnoDB的隔离级别

MySQL
MySQL

前言

还是老规矩，首先提出几个待解决的问题：

  • MySQL InnoDB存储引擎中事务的隔离级别有哪些？
  • 对应隔离级别的实现机制是什么？

本文就将对上面这两个问题进行解答，分析事务的隔离级别以及相关锁机制。

隔离性简介

隔离性主要是指数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的”独立”环境执行，意思就是多个事务并发执行时，一个事务的执行不应影响其它事务的执行。

4种隔离级别介绍

在SQL标准中定义了4种隔离级别，分别是：

  • Read uncommitted: 未提交读，事务中的修改，即使没有提交，对其他事务也是可见的。存在脏读
  • Read committed: 提交读，大多数数据库系统的默认隔离级别(MySQL不是), 一个事务从开始到提交之前，所做的修改对其他事务不可见。解决脏读，存在幻读和不可重复读
  • repeatable read: 可重复读，该级别保证在同一事务中多次读取同样记录的结果是一致的。解决脏读和不可重复读，理论上存在幻读，但是在InnoDB引擎中解决了幻读
  • Serializable：可串行化，强制事务串行执行。

上面4种隔离级别是SQL标准定义的，但是在不同的存储引擎中，实现的隔离级别不尽相同。本文主要介绍MySQL InnoDB 存储引擎中的隔离级别，在InnoDB存储引擎中，Repeatable Read 是默认的事务隔离级别，同时该引擎的实现基于多版本的并发控制协议——MVCC (Multi-Version Concurrency Control)，解决了幻读问题，当然 脏读和不可重复读也是不存在的。MVCC最大的好处就在于读不加锁，读写不冲突，这样极大的增加了系统的并发性能

Read uncommitted

未提交读，这种情况下，一个事务A可以看到另一个事务B未提交的数据，如果此时事务B发生回滚，那么事务A拿到的就是脏数据，这也就是脏读的含义。此隔离级别在MySQL InnoDB一般不会使用，不做过多说明。

Read Committed

提交读，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。解决了脏读问题，但是存在幻读现象。

所谓幻读，指的是在同一事务下，连续执行两次同样的SQL语句可能导致不同的结果，第二次的SQL语句可能会返回之前不存在的行，也就是”幻行”。

比如下面这个例子：

  1. 首先创建一张表,

    1
    2
    3
    4
    5
    6
    7
    8
    CREATE TABLE `t` (
      `a` int(11) NOT NULL,
      PRIMARY KEY (`a`)
    ) ENGINE=InnoDB

    insert into t(a) values(1);
    insert into t(a) values(2);
    insert into t(a) values(4);

  2. 分别执行事务1和事务2：

read_committed

可以从上图看出，Read Committed这种隔离级别存在幻读现象。实际上，Read Committed还可能存在不可重复读的问题，不可重复读，指的是一个事务内根据同一条件对行记录进行多次查询，但是查询出的数据结果不一致，原因就是查询区间数据被其他事务修改了。

不可重复读感觉和幻读有点像，实际上，前者强调是同一行记录数据结果不一样，后者强调的时多次查询返回的结果集不一样，增加了或减少了。

Repeatable Read

可重复读，该级别保证在同一事务中多次读取同样记录的结果是一致的，在InnoDB存储引擎中同时解决了幻读和不可重复读问题。至于InnoDB通过什么方式解决幻读和不可重复读问题，后续内容揭晓。

Serializable (可串行化)

Serializable 是最高的隔离级别，它通过 强制事务串行执行，避免了幻读的问题，但是 Serializable 会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题,因此并发度急剧下降，在MySQL InnoDB不被建议使用

Read Committed隔离级别下的加锁分析

隔离级别的实现与锁机制密不可分，所以需要引入锁的概念，首先我们看下InnoDB存储引擎提供的两种标准的行级锁：

  • 共享锁(S Lock)：又称为读锁，可以允许多个事务并发的读取同一资源，互不干扰。即如果一个事务T对数据A加上共享锁后，其他事务只能对A再加共享锁，不能再加排他锁，只能读数据，不能修改数据
  • 排他锁(X Lock): 又称为写锁，如果事务T对数据A加上排他锁后，其他事务不能再对A加上任何类型的锁，获取排他锁的事务既能读数据，也能修改数据。

注意： 共享锁和排他锁是不相容的。

MySQL InnoDB存储引擎是使用多版本并发控制的，读不加锁，读写不冲突，除非特定场景下的显示加读锁（这里不去探究）。本小节主要分析Read Committed隔离级别下的加锁情况，在MVCC的作用下，一般也就是写操作加X锁了。

加锁操作是和索引紧密相关的，对一个SQL语句进行加锁分析时，也要仔细考究其属性列上的索引类型。假设有数据表t1，有两个列，name列和id列，插入了几条数据，没有明确索引情况：

1
2
3
4
insert into t1(name,id) values("a",10);
insert into t1(name,id) values("b",11);
insert into t1(name,id) values("c",13);
insert into t1(name,id) values("d",20);

下面执行 delete from t1 where id = 10 这条SQL语句，这里的隔离级别设置为Read Committed，从这条SQL语句不能得知id列的索引情况，所以需要分情况讨论：

  • id列是主键
  • id列是二级唯一索引
  • id列是二级非唯一索引
  • id列上没有索引

下面是对以上几种情况的加锁情况进行归纳总结，更详细的内容可以参阅数据库大牛的文章：MySQL 加锁处理分析

id列是主键

id是主键时，上述SQL只需要在id=10这条记录上加X锁即可

id列是二级唯一索引

若id列是唯一索引，而主键是name列，那么SQL需要加上两个X锁，一个对应于id索引上的id=10的记录，另一把锁对应于主键索引上的[name=”a”,id=10]的记录

id列是二级非唯一索引

若id列上有非唯一索引，那么对应的所有满足SQL查询条件的记录，都会被加锁，同时，这些记录在主键索引上的记录也会被加锁。

id列上没有索引

若id列上没有索引，SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQL Sever层面进行的，因此每条记录，无论是否满足条件，都会被加上X锁。

Repeatable Read隔离级别下的加锁分析

前面说过，在Repeatable Read隔离级别下，InnoDB存储引擎解决了幻读和不可重复读问题，具体的原理是怎么样的呢？

之前简短的介绍了InnoDB中行锁的知识，下面来看下行锁的三种算法：

  • Record Lock: 单个索引记录上的锁，即加X锁
  • Gap Lock： 间隙锁，锁定一个范围，但不包含记录自身
  • Next-Key Lock: Gap Lock + Record Lock，锁定一个范围，并且锁定本身。

Record Lock总是会去锁住索引记录，如果InnoDB存储引擎在建表的时候没有设置任何一个索引，那么这时InnoDB会使用隐式的主键来进行锁定。(表没有定义主键的情况，InnoDB会默认添加一个隐式的主键索引)

Next-Key Lock是结合了Gap Lock和Record Lock的一种锁定算法，比如一个索引列有10,11,13和20这4个值，那么该索引可能被Next-Key Locking的区间为：

  • ($-\infty$,10)
  • (10,11]
  • (11,13]
  • (13,20]
  • (20,$+\infty$)

需要注意一点的是，当查询的索引含有唯一属性时，即是主键索引或者唯一索引时，InnoDB存储引擎会对Next-Key Lock进行优化，将其降级为Record Lock,即仅锁住索引本身，一般加上X锁。

Next-Key Lock机制设计的目的就是为了解决幻读问题，主要针对查询列索引为非唯一索引的时候。以下面这个例子进行说明：

  1. 首先创建测试表t1，name是主键索引，id为非唯一索引，即辅助索引

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    CREATE TABLE `t1` (
      `id` int(11) NOT NULL,
      `name` varchar(200) DEFAULT NULL,
      PRIMARY KEY (`name`),
      KEY `id_indx` (`id`)
    ) ENGINE=InnoDB

    insert into t1(name,id) values("a",10);
    insert into t1(name,id) values("b",11);
    insert into t1(name,id) values("c",13);
    insert into t1(name,id) values("d",20);

  2. 执行 delete from t1 where id = 11,其加锁情况如下图所示

gap_lock

这条SQL通过索引列id进行删除操作，该索引为非唯一索引，所以其使用传统的Next-Key
Locking 技术加锁，并且由于有主键索引和辅助索引两个，需要分别进行锁定。对于主键索引（即聚集索引），其仅对列name = “b”的索引加上 Record Lock，实际上就是X锁。

而对于非唯一索引，其加上的时Next-Key Lock，锁定范围是(10,11)，对其加上Gap Lock(间隙锁)，GAP锁实际上就是加在两条边界记录之间的位置。还需要注意的是，InnoDB还会对辅助索引下一个键值加上gap lock，即看到在(11,13)之间加了一个GAP锁。对于11值本身加上Record Lock，即X锁。

若此时开启另外一个事务执行下面的语句，就会阻塞：

1
2
1. select * from t1 where name = "b";
2. insert into t1(name,id) values("c",12);

比如第一条语句不能执行，因为在开始的事务中已经对聚集索引中的列name=”b”的值加上了X锁。因此执行会被阻塞。而第二个SQL，同样不能执行，插入的值12在锁定范围(11,13)中，需要阻塞等待。

所以，从上例就可以看出，GAP Lock的作用就是为了阻止多个事务将记录插入到同一范围内，这样就有效的解决了幻读问题。

隔离级别总结

下面总结下InnoDB存储引擎下的各种隔离级别：

隔离级别 脏读可能性 不可重复读可能性 幻读可能性 加锁读
Read Uncommitted Yes Yes Yes No
Read Committed No Yes Yes No
Repeatable Read No No No No
Serializable No No No Yes

参考资料 & 鸣谢

Bagikan Komentar