我们知道InnoDB提供了8种不同类型的锁,在《MySQL InnoDB锁介绍及应用方式(上)》一文中,我们已经详细介绍了其中4种类型的锁,那么剩下4种是什么类型的锁呢?让我们往下看:

InnoDB提供的8种不同类型的锁

下一个键锁(Next-Key Locks)

next-key lock 是 (索引记录上的索引记录锁) + (该索引记录前面的间隙上的锁) 二者的合体,它锁定索引记录以及该索引记录前面的间隙。有shard或exclusive两种模式。

LOCK_MODE分别是:S或X。

当InnoDB 搜索或扫描索引时,InnoDB在它遇到的索引记录上所设置的锁就是next-key lock,它会锁定索引记录本身以及该索引记录前面的gap("gap" immediately before that index record)。

即:如果事务T1 在索引记录r上有一个next-key lock,则T2无法在 紧靠着r前面的那个间隙中插入新的索引记录(gap immediately before r in the index order)。

next-key lock还会加在“supremum pseudo-record”上,什么是supremum pseudo-record呢?

它是索引中的伪记录(pseudo-record),代表此索引中可能存在的最大值,设置在supremum pseudo-record上的next-key lock锁定了“此索引中可能存在的最大值”,以及 这个值前面的间隙,“此索引中可能存在的最大值”在索引中是不存在的,因此,该next-key lock实际上锁定了“此索引中可能存在的最大值”前面的间隙,也就是此索引中当前实际存在的最大值后面的间隙。例如,下图中,supremum pseudo-record上的next-key lock锁定了区间(18, 正无穷),正是此next-key lock阻止其他事务插入例如19, 100等更大的值。

supremum pseudo-record上的next-key lock锁定了“比索引中当前实际存在的最大值还要大”的那个间隙,“比大还大”,“bigger than bigger”

图7:supremum_pseudo-record.png

插入意向锁(Insert Intention Locks)

一种特殊的gap lock。INSERT操作插入成功后,会在新插入的行上设置index record lock,但,在插入行之前,INSERT操作会首先在索引记录之间的间隙上设置insert intention lock,该锁的范围是(插入值, 向下的一个索引值)。有shard或exclusive两种模式,但,两种模式没有任何区别,二者等价。

LOCK_MODE分别是:S,GAP,INSERT_INTENTION或X,GAP,INSERT_INTENTION。

insert intention lock发出按此方式进行插入的意图:多个事务向同一个index gap并发进行插入时,多个事务无需相互等待。

假设已存在值为4和7的索引记录,事务T1和T2各自尝试插入索引值5和6,在得到被插入行上的index record lock前,俩事务都首先设置insert intention lock,于是,T1 insert intention lock (5, 7),T2 insert intention lock (6, 7),尽管这两个insert intention lock重叠了,T1和T2并不互相阻塞。

如果gap lock或next-key lock 与 insert intention lock 的范围重叠了,则gap lock或next-key lock会阻塞insert intention lock。隔离级别为RR时正是利用此特性来解决phantom row问题;尽管insert intention lock也是一种特殊的gap lock,但它和普通的gap lock不同,insert intention lock相互不会阻塞,这极大的提供了插入时的并发性。

总结如下:

1. gap lock会阻塞insert intention lock。事实上,gap lock的存在只是为了阻塞insert intention lock

2. gap lock相互不会阻塞

3. insert intention lock相互不会阻塞

4. insert intention lock也不会阻塞gap lock

INSERT插入行之前,首先在索引记录之间的间隙上设置insert intention lock,操作插入成功后,会在新插入的行上设置index record lock。

我们用下面三图来说明insert intention lock的范围和特性

图8:gap_lock_blocks_insert_intention_lock

上图演示了:T1设置了gap lock(13, 18),T2设置了insert intention lock(16, 18),两个锁的范围重叠了,于是T1 gap lock(13, 18)阻塞了T2 insert intention lock(16, 18)。

图9:insert_intention_lock_not_block_gap_lock.png

上图演示了:T1设置了insert intention lock(13, 18)、index record lock 13;T2设置了gap lock(17, 18)。尽管T1 insert intention lock(13, 18) 和 T2 gap lock(17, 18)重叠了,但,T2并未被阻塞。因为insert intention lock 并不阻塞 gap lock。

图10:index_record_lock_blocks_next_key_lock.png

上图演示了:T1设置了insert intention lock(11, 18)、index record lock 11;T2设置了next-key lock(5, 11]、PRIMARY上的index record lock 'b'、gap lock(11, 18)。此时:T1 index record lock 11 和 T2 next-key lock(5, 11]冲突了,因此,T2被阻塞。

自增锁(AUTO-INC Locks)

表锁。向带有AUTO_INCREMENT列 的表时插入数据行时,事务需要首先获取到该表的AUTO-INC表级锁,以便可以生成连续的自增值。插入语句开始时请求该锁,插入语句结束后释放该锁(注意:是语句结束后,而不是事务结束后)。

你可能会想,日常开发中,我们所有表都使用AUTO_INCREMENT作主键,所以会非常频繁的使用到该锁。不过,事情可能并不像你想的那样。在介绍AUTO-INC表级锁之前,我们先来看下和它密切相关的SQL语句以及系统变量innodb_autoinc_lock_mode

INSERT-like语句

2. insert ... select

4. replace ... select

5. load data

simple-inserts

待插入记录的条数,提前就可以确定(语句初始被处理时就可以提前确定)因此所需要的自增值的个数也就可以提前被确定。

包括:不带嵌入子查询的 单行或多行的insert, replace。不过,insert ... on duplicate key update不是

bulk-inserts

待插入记录的条数,不能提前确定,因此所需要的自增值的个数 也就无法提前确定

包括:insert ... select, replace ... select, load data

在这种情况下,InnoDB只能每次一行的分配自增值。每当一个数据行被处理时,InnoDB为该行AUTO_INCREMENT列分配一个自增值

mixed-mode-inserts

也是simple-inserts语句,但是指定了某些(非全部)自增列的值。也就是说,待插入记录的条数提前能知道,但,指定了部分的自增列的值。

INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

INSERT ... ON DUPLICATE KEY UPDATE也是mixed-mode,最坏情况下,它就是INSERT紧跟着一个UPDATE,此时,为AUTO_INCREMENT列所分配的值在UPDATE阶段可能用到,也可能用不到。

再看一下系统变量innodb_autoinc_lock_mode,它有三个候选值0,1,和2

8.0.3之前,默认值是1,即“连续性的锁定模式(consecutive lock mode)”;8.0.3及之后默认值是2,即“交织性锁定模式(interleaved lock mode)”

a. 当innodb_autoinc_lock_mode=0时,INSERT-like语句都需要获取到AUTO-INC表级锁;

b. 当innodb_autoinc_lock_mode=1时,如果插入行的条数可以提前确定,则无需获得AUTO-INC表级锁;如果插入行的条数无法提前确定,则就需要获取AUTO-INC表级锁。因此,simple-inserts和mixed-mode inserts都无需AUTO-INC表级锁,此时,使用轻量级的mutex来互斥获得自增值;bulk-inserts需要获取到AUTO-INC表级锁;

c. 当innodb_autoinc_lock_mode=2时,完全不再使用AUTO-INC表级锁;

我们生产数据库版本是5.6.23-72.1,innodb_autoinc_lock_mode=1,而且,我们日常开发中用到大都是simple-inserts,此时根本就不使用AUTO-INC表级锁,所以,AUTO-INC表级锁用到的并不多哦。

LOCK_MODE:AUTO-INC表级锁用到的并不多,且,AUTO-INC锁是在语句结束后被释放,较难在performance_schema.data_locks中查看到,因此,没有进行捕获。感兴趣的同学可以使用INSERT ... SELECT捕获试试。

空间索引(Predicate Locks for Spatial Indexes)

我们平时很少用到MySQL的空间索引。所以,本文忽略此类型的锁,到此为止,MySQL InnoDB 8种类型的锁我们就介绍完了。我们以一个例子结束8种类型的介绍。

图11:put_them_together.png

T1先执行,事务ID是8428;T2后执行,事务ID是8429

上图演示了:

1. 任何事务,在锁定行之前,都需要先加表级锁intention lock,即:第三行的IX和第一行的IX。

2. idx_c是辅助索引,InnoDB扫描idx_c时遇到了c=222,于是,在idx_c上加了next-key lock,即:第四行的X。next-key lock就是 index record lock+gap lock,于是此next-key lock锁定了idx_c上值为222的索引记录,以及222前面的间隙,也就是间隙(22, 222)。

performance_schema.data_locks表中并不能看到T2的全部锁,比如,T2也得在iux_b上设置insert intention lock,但,performance_schema.data_locks中并没有这个锁。关于performance_schema.data_locks中显示了哪些锁,请见本文最后一段。

把这些锁及其范围列出来如下图所示

图12:summary.png

本文和《MySQL InnoDB锁介绍及应用方式(上)》已对InnoDB提供的8种不同类型的锁做了详细介绍,那么,不同的SQL语句在实际应用时应该加什么样的锁呢?让我们带着这个疑问,详见《MySQL InnoDB锁介绍及应用方式(下)》。

敲门砖:“途牛技术”