枫秀学习日志

枫秀学习日志

0%

InnoDB存储引擎 之 锁

发表于 更新于 分类于 ， Disqus： Valine：
本文字数： 5.1k 阅读时长 ≈ 5 分钟

概述

开发多用户、数据库驱动的应用时，最大的一个难点是：一方面要最大成都地利用数据库的并发访问，另外一方面还要确保每个用户能以一致的方式读取和修改数据，为此就有了锁的机制，同时这也是数据库系统区别于文件系统的一个关键特性。InnoDB存储引擎较之mysql数据库的其它存储引擎在这方面技高一筹。而只有正确的了解这些锁的内部机制才能充分发挥InnoDB存储引擎在这方面的优势。本篇文章会详细介绍InnoDB存储引擎对表中数据的锁定，同时分析InnoDB存储引擎会以怎样的力度锁定数据。

lock与latch

这里首先要区分锁中容易让人产生混淆概念的lock与latch。在数据库中lock与latch都被称为锁。但是俩者有着截然不同的含义，本章主要讲的是lock。

latch是一种锁，使用来保证并发线程操作临界资源的正确性，可分为互斥锁和读写锁，并且没有死锁检测机制。

lock的对象时事务，用来锁定的是数据库中的对象，如表，页、行等。此外lock在大多数数据库中会有死锁机制的处理。

他们俩之间的区别如下：

upload successful

InnoDB存储引擎中的锁

锁的类型

InnoDB存储引擎实现类来钟标准的行级锁：

  • 共享锁(s lock)：允许事务读一行数据
  • 拍他锁(x lock)：允许事务删除或更新一行数据。

如果一个事务T1已经获得了行r的共享锁，那么另外的事务T2可以立即获得行r的共享锁，因为读取并没有改变行r的数据，称这种情况为锁兼容。锁的兼容性如下：

- s x
x 不兼容 不兼容
s 兼容 不兼容

从表中可以发现X锁与任何锁都不兼容，而s仅与s锁兼容，这里需要注意的一点是，s和x锁都是行锁，兼容是指对同一记录锁的兼容性情况。

另外，InnoDB还支持多粒度锁定，这种锁定允许事务在行级上的锁定和表级别的上锁定同时存在。而支持这种方式，引入了一种额外的锁称之为意向锁，意向锁将锁定的对象分为多个层次，如下图：

upload successful

若将上述看做一棵树，那么对最下层的对象上锁，也就是对最细粒度的对象进行上锁，那么首先需要对粗粒度的对象上锁。如上图，如果需要对页上的记录行r上X行上锁，那么需要分别对数据库A、表、页上一项锁IX，最后对记录r上X锁。在上锁的路径中，任何一个节点上锁发生非相容性，则上锁需要等待。
InnoDB存储引擎支持意向锁设计比较简练，其意向锁为表级别的锁。设计的主要目的是为了在一个事务中揭示下一行将被请求的锁的类型。支持俩种意向锁：

  1. 共享意向锁(IS):事务想要获得一张表中的某几行的共享锁
  2. 排他意向锁：事务想要获得一张表中某几行的排他锁

由于InnoDB存储引擎支持的是行级别的锁，因此意向锁其实不会阻塞除权标扫以外的任何请求。表级意向锁与行级锁的兼容性如下表：

- IS IX S X
IS 兼容 兼容 兼容 不兼容
IX 兼容 兼容 不兼容 不兼容
S 兼容 不兼容 兼容 不兼容
X 不兼容 不兼容 不兼容 不兼容

一致性非锁定读

一致性非锁定读是指InnoDB存储引擎通过行多版本控制的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行DELETE或UPDATE，这时读取操作不会因此去等待行上锁的释放。相反地，InnoDB存储引擎会去读取行的一个快照数据。如下图

Xnip2019-03-22_15-57-14

上图显示了InnoDB存储引擎一致性非锁定读。快照数据是指该行之前的版本数据，而实现是通过undo段来完成。而undo是用来回滚数据，因此快照本身是没有额外的开销。此外，读取数据是不需要上锁，因为没有数据需要对历史的数据进行修改操作。这也是为什么叫一致性非锁定读。

一致性非锁定读是InnoDB存储引擎默认的读取方式，因为读取不需要等待，这可以极大的提高数据库的并发性。在不同的事务隔离级别下，读取的方式可能是不同的，也就是可能不采用一致性非锁定读，采用其他的读取方式。

在Innod存储引擎中，Repeatable read是默认的事物隔离级别，它采用的是一致性非锁定读。即使都是使用非锁定的一致性读，在不同的隔离性级别下对于快照数据的定义也是不相同的。InnoDB存储引擎中，Read commited和Repeatable Read都是采用一致性非锁定读，Read Commit事务隔离级别下，对于快照数据，一致性非锁定读总是读取被锁定行的最新一份快照数据。而在Repeatable Read事务隔离级别下，对于快照数据，一致性非锁定读总是读取事务开始时的数据版本。下面是俩个事务的执行流程

Xnip2019-03-22_16-22-51

如果是在Read Commited事务隔离级别下，事务A最后读取的数据是空集。而在Repeatable Read事务隔离级别下，事务A最后读取的数据是id=1的数据，也就是数据没有被事务B更改前的数据。

一致性锁定读

上面说到，在默认配置下，即事务的隔离级别是Repeatable Read模式下，InnoDB存储引擎的Select操作使用一致性非锁定读。但是在某些情况下需要用户显示对读的数据加锁以保证数据逻辑的一致性。因此需要提供对于只读操作加锁的的语句。mysql中提供了俩种一致性锁定读：

1
2
select ..... from update
select ..... lock in share mode

上面俩个语句，一个在读操作时加上排他锁，后一个加上共享锁。
需要注意的一点是，上面俩个语句必须被包裹在事务中，才会生效。

自增长与锁

自增长是数据库中非常常见的一种属性，也是很多表选择的主键。在InnoDB存储引擎的内存结构中，对每个含有自增长值的表都有一个自增长计数器。当对含有自增长的计数器的表进行插入操作时，这个计数器会被初始化，执行如下语句来得到计数器的值：

1
select max(auto_inc_col) form t for update

插入操作会依据这个得出的值加1赋予自增长序列。这种方式称为auto-inc locking。这种锁其实是以各种特殊的表锁机制，为了提高插入的性能，这种锁不是在事务结束之后释放，而是在完成自增长值插入的sql语句后立即释放。
虽然这种方式从一定程度上提高了并发插入的效率，但是会存在并发插入的问题，对于自增长值的列并发插入性能比较差。因此在mysql最新的版本中实现了下面集中自增长机制的方式。下图是我从MySQL技术内幕(InnoDB存储引擎)第2版截取的，有一些我也不是很懂，等我理解透了会替换掉，哈哈哈、、

Xnip2019-03-22_16-47-28

外键和锁

外键主要用于完整性的约束检查。在InnoDB存储引擎中，对于一个外键列，如果没有显示的对这个列加索引，InnoDB存储引擎会自动对齐加一个索引，这样可以避免表锁。

对于外键值的插入或更新，首先需要查询父表中的记录，即select操作。但是对于父表的select操作不是使用一致性非锁定读的方式，这样会发生数据的不一致性，因此此时采用的是select ...lock in share mode方式，对父表会加入一个S锁。

锁的算法

InnoDB存储引擎中有3中行锁的算法，其分别是

  • Record Lock：单个行记录上的锁
  • Gap Lock：间隙锁，锁定一个范围，但不包含记录本身。
  • Next-key lock：Gap lock加上record lock，锁定一个范围，并且锁定记录本身

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

Next Lock是结合了Record Lock和Gap Lock的一种锁定方法。InnoDB对于行的查询都采用这种锁定算法。假设索引值有1，3，5，8，11，其记录的GAP的区间如下：是一个左开右闭的空间（原因是默认主键的有序自增的特性）

1
（-∞,1]，(1,3]，(3,5]，(5,8]，(8,11]，(11,+∞）

但是，如果查询的索引含有唯一索引时，InnoDB存储引擎会对Next-key lock进行优化，降级为Record Lock，即紧锁住索引本身，而不是范围。

下面看俩个例子：第一个例子包含一个主键，第二个例子既包含主键也包含辅助索引。

示例1

创建表以及插入的语句如下

1
2
create table t (a int primary key)
insert into t values(1),(2),(5)
时间 事务A 事务B
1 begin
2 select * from t where a =5 for update
3 begin
4 insert into t value(4)
5 commit
6 commit
上面是基于表t执行的俩个事务。在会话A中首先对a=5进行X锁定，而由于主键是唯一的，因此使用的是Record Lock锁定。因此会话B在执行插入的时候可以立即执行而不会阻塞。

示例2

创建表以及插入的语句如下

1
2
3
4
5
6
7
8
9
10
create table z (
  a int primar key,
  b int ,
  key(b)
)
insert into z value(1,1)
insert into z value(3,1)
insert into z value(5,3)
insert into z value(7,6)
insert into z value(10,8)

假设在事务A中执行下面的sql语句，

1
select * from where b=3 for update

从前面我们知道，通过索引列b进行查询，其使用的是Next-key lock即使加锁。并且由于其有俩个索引，因此要分别进行加锁。对于主键索引a，仅对列a=5的索引加上Record Lock。而对于辅助索引，加上的就是Next-key Lock，锁定的范围是(1,3],需要特别注意的是，InnoDB存储引擎会对辅助索引下一个键值加上gap Lock，即还有一个辅助索引范围为(3,6)的锁。因此总的范围区间是(1,3),(3,6)

假设上面的事务A还没有提交，现在有三个语句要运行：

1
2
3
select * from z where a=5 lock in share mode
insert into z value(4,2)
insert into z value(6,5)
  • 第一个sql语句执行阻塞，因为上面事务A执行中a=5的值加上X锁。
  • 第二个sql语句执行阻塞，虽然主键值4插入没有问题，但是辅助索引值2在锁定的范围(1,3)中。
  • 第三个sql语句执行阻塞，和第二个sql原因一样。

下面的sql语句不会被阻塞

1
2
3
insert into z value(8,6)
insert into z value(2,0)
insert into z value(6,7)

从上面可以看到，Gap Lock的作用是为了防止多个事务将记录插入到同一个范围内，而插入到相同的范围内会导致幻读问题。如果您对脏读、不可重复读和幻读不是很了解，可以看这篇文章事务隔离性简介。而InnoDB存储引擎通过Next-key lock解决了幻读问题，幻读是因为前后执行相同的操作，而导致返回的数据行不同，next-key lock在查询的时候回加上范围锁，因此如果在前后俩次查找的范围内，是不能进行插入操作。所以也就没有幻读问题的产生。

此外next-key lock是可以被关闭的，但是除非你非常懂，否则还是不要这么做。最后要提醒的是，如果唯一索引包含多个列，仅对唯一索引的某一列当做索引来查询，使用的还是next-key lock。比如唯一索引是(a,b)，查询使用的是条件是a=5，这时会对列a使用next-key lock。

阻塞

由于不同锁之间的兼容性问题，在有些时刻，一个事务中的锁需要等待另一个事务中的锁释放它占用的资源，这就是阻塞。在InnoDB存储引擎中，有下面来个参数可以控制阻塞的行为：

  1. 超时时间的参数：innodb_lock_wait_timeout ，默认是50秒。
  2. 超时是否回滚参数：innodb_rollback_on_timeout 默认是OFF。

类如下面这个列子，表t中只有一列数据a数据列。表中数据如下

1
2
a
1

事务执行情况

时间 事务A 事务B
1 begin
2 select * from t where a = 8 for update;
3 - begin
4 - insert into t values(2)
5 insert into t values(3)
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
6 commit
在上面俩个事务执行完之后，执行查询操作，得到的结果如下 
1
2
3
a
1
3

默认情况下，InnoDB不会回滚超时引发的错误异常，而等待超时这个异常会抛出给用户，让用户来觉得是commit还是rollback。

参考

  1. MySQL技术内幕(InnoDB存储引擎)第2版
  2. Innodb锁机制：Next-Key Lock 浅谈
相关文章推荐