本章是mysql技术内幕这本书的读书笔记。可以点击超链接查看所有的读书笔记。

1. Innodb存储引擎中的锁

1.1 数据库锁分类(latch和lock)

  1. latch:轻量级锁,要求锁定时间非常短。如果持续时间长,则应用的性能会非常差。在InnoDB存储引擎中,latch可以分为mutex(互斥量)和rwlock(读写锁)
  2. lock: 锁的对象是事务。用来锁定的是数据库中的表、页、行对象。

关于两者的比较可以参考下图:

查看锁信息的方式;

# 查看latch信息
show engine innodb mutex
# 查看lock信息, 主要通过查询INFORMATION_SCHEMA下的INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS三张表
use information_schema
desc INNODB_TRX;
desc INNODB_LOCKS;
desc INNODB_LOCK_WAITS;

1.2 三张锁状态表的含义

  1. INNODB_TRX

  1. INNODB_LOCKS

  1. INNODB_LOCK_WAITS

1.3 innodb中 lock锁的分类

  1. 共享锁(S lock): 允许事务读一行数据
  2. 排它锁(X lock): 允许事务删除或更新一行数据

锁兼容: 只有S锁和S锁是兼容的。例如对一行记录的多个读操作可以一起拿到S锁。锁兼容情况见下图:

1.4 意向锁(Intent Lock)

意向锁是InnoDB支持的表级锁,表明被锁的表中存在S锁或者X锁。

  1. 意向共享锁(IS): 使用该锁表明事务想要获得一张表中某几行的共享锁
  2. 意向排它锁(IX): 使用该锁表明事务想要获得一张表中某几行的排它锁

为什么需要意向锁?
以下解答来自CSDN上网友welyngj的解答
意向锁的存在价值在于在定位到特定的行所持有的锁之前,提供一种更粗粒度的锁,可以大大节约引擎对于锁的定位和处理的性能,因为在存储引擎内部,锁是由一块独立的数据结构维护的,锁的数量直接决定了内存的消耗和并发性能。例如,事务A对表t的某些行修改(DML通常会产生X锁),需要对t加上意向排它锁,在A事务完成之前,B事务来一个全表操作(alter table等),此时直接在表级别的意向排它锁就能告诉B需要等待(因为t上有意向锁),而不需要再去行级别判断。

简单总结: 通过意向锁,判断锁不兼容就会快很多,提升了性能。

InnoDB加行锁之前,都需要先加表级的意向锁。在S锁之前加IS,在X锁前加IX。InnoDB存储引擎中锁的兼容情况见下图:

1.5 一致性非锁定读(MVCC)

事务的四种隔离级别一文中我们讨论过事务的隔离级别。较高的隔离性不会有脏读、幻读、不可重复读等问题,但是并发性比较低。本小节讨论的一致性非锁定读则解决了之前的隔离性所带来的问题。这种隔离级别也叫做快照隔离级别。

下图演示了基本原理,就是对已经上X锁的数据,去访问它的快照数据。快照数据通过undo段来实现,因此额外开销也比较小。快照数据的读取不需要上锁。InnoDB默认采用这种读取数据的方式。

MVCC主要是解决了不可重复读的问题。因为最新快照版本上锁后,指定了其他事务再读取的时候固定读取哪个快照版本。只依靠MVCC还是无法解决幻读的问题,因为其他事务插入的数据,仍然会影响当前正在读的事务。要解决幻读的问题,还是需要依靠锁。解决幻读可以看后面提到的Next key lock。

1.5.1 快照隔离级别

快照隔离级别其实是为了方便理解而这么说的,官方实际上没这么定义。所谓的快照隔离级别就是:

  1. READ COMMITTED隔离级别+MVCC: 非一致性读总是读取锁定行的最新一份快照数据(有不可重复读的问题)
  2. REPEATABLE READ隔离级别+MVCC(默认方式): 非一致性读总是读取事务开始时的行数据版本(没不可重复读的问题)

PS: 可以用select @@tx_isolation\G; 这个命令来看隔离级别

1.6 一致性锁定读

用户为了保证逻辑一致性,有时候需要对读操作加锁,可以用以下的方式:

/*对读取的行加X锁*/
select ... for update
/*对读取的行加S锁*/
select ... lock in share mode

1.7 自增长与锁

自增长计数器会有他自己的自增锁来负责对其本身的自增操作进行加锁。该锁是一个表锁。为了提高插入的性能,锁在完成对自增长值插入的SQL语句后立即释放,不需要等待事务完成才释放。这点可以区别X锁和S锁。另外注意,如果插入的行不存在自增列,自然也不会有自增锁了。

现在自增锁也有不同的模式可以选择(通过innodb_autoinc_lock_mode设置):

上面提到的插入类型的说明见下图:

1.8 外键锁

外键用于引用完整性的约束检查。对于一个外键列,如果没有显式地对这个列加索引,InnoDB存储引擎自动对其加上一个索引,这样可以避免表锁。注意外键对父表的查询操作不使用一致性非锁定读的方式,这样会导致数据不一致。数据不一致的例子见书本P264

2. 锁的算法

InnoDB存储引擎有三种行锁的算法:

  1. Record Lock
  2. Gap Lock
  3. Next-Key Lock

在学习这个知识的过程中看到一篇文章不错,可以作文本文的补充阅读:
美团技术博客——Innodb中的事务隔离级别和锁的关系

2.1 Record Lock(也称作Index Record Lock)

只对索引生效,对索引上单个行记录加锁。在InnoDB里面行锁的加锁对象都是索引记录。

PS: innodb存储引擎修改数据的时候会在存储引擎层面根据索引来对要修改的行进行加锁。如果索引找不到,就会直接对整个表进行加锁,然后由mysql server层进行过滤,这个需要注意。 另外MYSQL在mysql server进行条件过滤的时候,如果发现不满足,会调用unlock_row方法,把不满足条件的记录释放锁,提升性能(虽然这个不符合二阶段协议的标准,但是为了性能只能妥协了)。这点的说明见:参见《高性能MySQL》中文第三版p181

2.2 Gap Lock

Gap Lock: 间隙锁,锁定一个范围,但不包含记录本身。作用是为了阻止多个事务将记录插入到同一范围内,避免幻读。例如一个事务A在执行SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; 同时另一个事务B要insert 一个 c1=15的行。此时事务B是拿不到gap lock的,因为10到20直接的gaps locks都被事务A持有。此时并不会管有没有一条c1=15的记录存在,事务B都拿不到Gap。

2.3 Next-Key Lock

Next-Key Lock: Gap Lock+Record Lock, 锁定一个范围,并且锁定索引中记录本身。但是如果查询的索引含有唯一属性时,InnoDB存储引擎会把Next-Key Lock降级为Record Lock,锁住索引本身。一个next-key lock是结合了一个index lock和它之前的gap lock。net-key lock在查询的列是唯一索引的情况下回降级为Record Lock.

Next-key Lock的锁定区间符合以下规则: 例如辅助索引上有某一列a的值为,10,11,13,20,那么在其上可以采用next key lock的区域为:

InnoDB的默认隔离级别是:REPEATABLE_READ,这种隔离级别下,InnoDB使用在index scan 时,采用的是next-key。Next-key 本身不存在,只代表了index lock和它之前的gap lock。如果不想使用gap lock(外键约束和唯一性检查仍然会用gap lock),可以使用RC的隔离级别或者设置innodb_locks_unsafe_for_binlog参数。不过强烈不建议改成RC的,性能并没有太大差别,还会造成replication的时候主从数据不一致。

Next-Key Lock可以防止幻读的问题。
PS:幻读指的是在同一事务下,连续执行两次同样的SQL语句可能导致不同的结果,第二次SQL语句可能返回之前不存在的行。前面的例子也可以看到幻读是怎样的了。

RR级别中,事务A在update后加锁,事务B无法插入新数据,这样事务A在update前后读的数据保持一致,避免了幻读。这个锁,就是Gap锁。

2.3.1 Next-Key Lock例1

举个例子,下图的两个事务:

  1. 如果不使用Next-Key Lock,事务A执行完毕后,发现多了一条事务B插入的数据并且没有被修改(teacher-id=30的记录没有被修改为class_name为初三四班)。

  2. 如果使用了Next-Key Lock,则事务A修改teach-id=30的那行数据时,会在两边加上gap锁。如下面第二幅图所示。gap锁锁住的区域为(5,30]和(30,正无穷)这个区间。 PS: 下面第二幅图中我们假设了还有另外一个事务C锁住了teach-id=5这条记录。如果没有这个事务C的话,则gap锁锁住的范围则是(负无穷,5]和(30,正无穷)

在SQL标准里面,我们知道RR这种隔离级别时无法消除幻读的情况的,如上面所示。但是MYSQL通过采用Next-Key Lock的方式解决了幻读的问题。

2.3.1 Next-Key Lock例2

首先创建一张表,在a上建聚集索引,在b上建辅助索引。


然后执行以下语句:

对于聚集索引,其仅对列a等于5的索引加上Record Lock。对于辅助索引,加Next key lock ,锁的范围是b的值为[1,3)的区间和[3,6) 的区间,正好锁住了前后。注意前面的区间的右边是闭的,后面的区间右边是开的。

如果在运行以下的SQL语句,则都会被阻塞:

第一个SQL阻塞原因: 前面已经上了X锁,没法再加S锁了。
第二个SQL阻塞原因: b的区间(1,3)已经上锁
第三个SQL阻塞原因: b的区间(3,6)已经上锁

2.4 Next key lock与应用程序的唯一性检查

这个和唯一性约束还是不一样的,就是多个事务一起插入数据的时候,产生冲突时保证只有一个事务插入成功。

可以自己在MYSQL里面运行下以下的例子:

3. 锁问题

引入锁,就会带来类似脏读、不可重复读、幻读等问题。这个我们在事务的四种隔离级别中也提到过。这里再来复习下。

这次复习主要以放例子为主,结合例子比较容易明白。

3.1 脏读

定义: 脏读指的就是在不同的事务下,当前事务可以读到另外事务没有提交的数据(脏数据)。

例子如下:

使用场景: 一般来说不会用这种读未提交的隔离级别。不过replciation里面的slave节点可以考虑用这种隔离级别提升性能。因为slave上的查询并不需要特别精准的返回值。

3.2 不可重复读

定义:一个事务单元内连续读取同一行数据,但是由于其他事务的干扰,导致读取的结果的不同。不可重复读违反了数据库事务一致性的要求。虽然没有严格满足一致性要求,不过毕竟是读取已经提交的数据,不会带来太大的影响。像ORACLE、SQL SERVER都会采用RC作为默认的隔离级别。

不可重复读和脏读的区别: 脏读是读到未提交的数据,不可重复读读到的是已经提交的数据。

例子:

3.2.1 不可重复读和幻读的关系

书上是把不可重复读和幻读来等价理解,其实还是稍微有点差别的。书上内容如下:

其实一般来说幻读就是指一个事务单元连续运行得到的结果不同,不可重复读单指一个事务单元内的SQL重复读取同一行得到的结果不同。 按照SQL标准的话,不可重复的隔离级别解决了不可重复读(禁止对正在读取的数据写入避免了不可重复读)的问题,但是幻读(其他事务的新数据插入仍然会干扰事务本身的查询结果)仍然会存在。在innodb是通过next key lock来解决了幻读的问题。

关于两者的区别也可以查看下SF上的回答:what is difference between non-repeatable read and phantom read?

3.3 丢失更新

定义:一个事务的操作被另一事务的更新操作覆盖。

例子:

解决方法:事务串行化

3.4 阻塞

由于等待获取锁会造成阻塞。通过innodb_lock_wait_timeout可以控制阻塞等待的超时时间,默认50秒。

4. 死锁

两个或两个以上事务执行过程中互相等待获取锁就会死锁了。

死锁一般的解决方式有,以下两种方式在innodb当中都有

  1. 超时机制(最简单的方法)
  2. 等待图(wait-for graph):相比超时机制是更加主动的策略。

4.1 wait-for graph

保存以下信息:

  1. 锁的信息链表
  2. 事务等待链表

下面来看看等待图如何画及其含义:

4.2 死锁概率

事务发生死锁概率与以下因素有关:

  1. 系统中事务的数量(n),数量越多发生死锁的概率越大
  2. 每个事务操作的数量(r),每个事务操作的数量 越多,发生死锁的概率越大
  3. 操作数据的集合(R),越小则发生死锁的概率越大。

4.3 死锁示例

PS: Oracle数据库中产生死锁的常见原因是没有对外键添加索引,而InnoDB存储引擎会自动对其进行添加,避免了死锁。

4.4 锁升级

锁升级指的是将当前锁的粒度降低。例如把1000个行锁升级为页锁或者表锁。

InnoDB不存在锁升级的问题,因为其不是根据每个记录来产生行锁的,相反,其根据每个事务访问的每个页对锁进行管理,采用的是位图的方式。因此不管一个事务锁住页中一个记录还是多个记录,其开销通常是一直的。SQL SERVER则有锁升级的概念。

InnoDb锁开销使用位图方式管理页来节约锁开销。

参考资料:

  1. mysql意向锁的概念和用途
  2. 美团技术博客——Innodb中的事务隔离级别和锁的关系