数据库基础之锁（7. 理解InnoDB的锁 record，gap，next-key lock）
转载自Mysql加锁过程详解（8）-理解innodb的锁(record,gap,Next-Key lock)系列文章，仅做备份与温故之用。
Record lock
单条索引记录上加锁，record lock锁住的永远是索引，而非记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。所以说当一条sql没有走任何索引时，那么将会在每一条聚集索引后面加X锁，这个类似于表锁，但原理上和表锁应该是完全不同的。
Gap Lock
在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。gap lock的机制主要是解决可重复读模式下的幻读问题，关于幻读的演示和gap锁如何解决了幻读。关于这一块，先给出几个定义：
快照读
简单的select操作，没有lock in share mode或for update，快照读不会加任何的锁，而且由于mysql的一致性非锁定读的机制存在，任何快照读也不会被阻塞。但是如果事务的隔离级别是SERIALIZABLE的话，那么快照读也会被加上共享的next-key锁，本文不对SERIALIZABLE隔离级别做叙述。
当前读
官方文档的术语叫locking read，也就是insert，update，delete，select..in share mode和select..for update，当前读会在所有扫描到的索引记录上加锁，不管它后面的where条件到底有没有命中对应的行记录。当前读可能会引起死锁。
意向锁
innodb的意向锁主要用于多粒度的锁并存的情况。比如事务A要在一个表上加S锁，如果表中的一行已被事务B加了X锁，那么该锁的申请也应被阻塞。如果表中的数据很多，逐行检查锁标志的开销将很大，系统的性能将会受到影响。为了解决这个问题，可以在表级上引入新的锁类型来表示其所属行的加锁情况，这就引出了“意向锁”的概念。
举个例子，如果表中记录1亿，事务A把其中有几条记录上了行锁了，这时事务B需要给这个表加表级锁，如果没有意向锁的话，那就要去表中查找这一亿条记录是否上锁了。如果存在意向锁，那么假如事务Ａ在更新一条记录之前，先加意向锁，再加Ｘ锁，事务B先检查该表上是否存在意向锁，存在的意向锁是否与自己准备加的锁冲突，如果有冲突，则等待直到事务Ａ释放，而无须逐条记录去检测。事务Ｂ更新表时，其实无须知道到底哪一行被锁了，它只要知道反正有一行被锁了就行了。
说白了意向锁的主要作用是处理行锁和表锁之间的矛盾，能够显示“某个事务正在某一行上持有了锁，或者准备去持有锁”。
不可重复读
指的是在同一个事务中，连续几次快照读，读取的记录应该是一样的。不可重复读的演示较为简单，本文不做讨论。
幻读
指的是一个事务A中执行了一个当前读操作，而另外一个事务B在事务A的影响区间内insert了一条记录，这时事务A再执行一个当前读操作时，出现了幻行。这和不可重复读的主要区别就在与事务A中一个是快照读，一个当前读；并且事务B中一个是任何的dml操作，一个只是insert。比如在A中select * from test where id<10 lock in share mode结果集为（1,2,3），这时在B中对test表插入了一条记录4，这时在A中重新查询结果集就是(1,2,3,4），和事务A在第一次查询出来的结果集不一致，这里的4就是幻行。
演示条件：由于可重读的隔离复级别下，默认采用Next-Key Locks，就是Record lock和gap lock的结合，即除了锁住记录本身，还要再锁住索引之间的间隙，所以这个gap lock机制默认打开，并不会产生幻行，那么我们要演示幻行的话，要么将隔离级别改为read-commited，要么在REPEATABLE-READ模式下禁用掉gap lock，这里我们采用的是第二种方式。
幻读的演示
在演示之前又引入了innodb_locks_unsafe_for_binlog参数，该参数可以禁用gap lock。
innodb_locks_unsafe_for_binlog：静态参数，默认为0，表示启动gap lock，如果设置为1，表示禁用gap lock，这时mysql就只有record lock了，不过值得注意的是，即使了设置了1，关于外键和唯一键重复检查方面用到的gap lock依旧有效。这时可以简单地理解成事务的隔离级别退化成可重复读，然后两者应该还是有所区别的。建议是不要随便设置，我们这里设置只是做个简单的幻读演示，mysql后续的版本可能都会废弃掉这个参数了。
session 1 先将myid>95的记录加一个当前读 mysql> show create table test_gap_lock\G *************************** 1. row *************************** Table: test_gap_lock Create Table: CREATE TABLE `test_gap_lock` ( `id` int(11) NOT NULL, `name` varchar(100) DEFAULT NULL, `myid` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniq_name` (`name`), KEY `idex_myid` (`myid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
mysql> begin; mysql> select * from test_gap_lock where myid>95 for update; +----+------------+------+ | id | name | myid | +----+------------+------+ | 1 | jiang | 99 | | 2 | hubingmei | 99 | | 5 | hubingmei4 | 100 | +----+------------+------+ 3 rows in set (0.00 sec)
session 2 这时session 2插入myid=98的记录成功了。 insert into test_gap_lock values(6,'jiang2',98); Query OK, 1 row affected (0.00 sec) session 1 这时session 1再次查看时发现记录myid=98的记录已经存在了，这条记录就是幻行。 mysql> select * from test_gap_lock where myid>95 for update; +----+------------+------+ | id | name | myid | +----+------------+------+ | 1 | jiang | 99 | | 2 | hubingmei | 99 | | 5 | hubingmei4 | 100 | | 6 | jiang2 | 98 | +----+------------+------+ 4 rows in set (0.00 sec)
Gap Lock机制解决幻读问题演示
【条件】我们再把innodb_locks_unsafe_for_binlog值改回默认值0，并且tx_isolation为REPEATABLE-READ，演示时务必explain下，确保该sql走了非唯一索引idx_myid(因为如果测试数据较少的话，可能优化器直接走全表扫描，那就导致锁住所有记录，无法模拟出gap锁)
演示范例 1（非唯一索引+范围当前读）
mysql> show create table test_gap_lock\G *************************** 1. row *************************** Table: test_gap_lock Create Table: CREATE TABLE `test_gap_lock` ( `id` int(11) NOT NULL, `name` varchar(100) DEFAULT NULL, `myid` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniq_name` (`name`), KEY `idex_myid` (`myid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) session 1 先explain确保session的当前读sql执行走了索引idx_myid mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> explain select * from test_gap_lock where myid>100 for update; +----+-------------+---------------+-------+---------------+-----------+---------+------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------+-------+---------------+-----------+---------+------+------+-----------------------+ | 1 | SIMPLE | test_gap_lock | range | idex_myid | idex_myid | 5 | NULL | 2 | Using index condition | +----+-------------+---------------+-------+---------------+-----------+---------+------+------+-----------------------+ 1 row in set (0.00 sec) mysql> select * from test_gap_lock where myid>100 for update; +----+------------+------+ | id | name | myid | +----+------------+------+ | 5 | hubingmei4 | 101 | | 98 | test | 105 | +----+------------+------+ 2 rows in set (0.00 sec) session 2 先插入myid=56的成功，因为锁住的间隙是myid>100,56不在该范围内；再插入myid=109时，会一直卡住直到session 1 commit，rollback或者直接锁等待超时，在锁等待超时前在session 1中执行同样的sql，得到的结果依旧只有id=5,98的记录，这样就避免了幻读问题。 mysql> insert into test_gap_lock values(999,'test2',56); Query OK, 1 row affected (0.00 sec) mysql> insert into test_gap_lock values(123,'test4',109); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
演示范例2（非唯一索引+等值当前读）
mysql> select * from test_gap_lock; +-----+------------+------+ | id | name | myid | +-----+------------+------+ | 1 | jiang | 98 | | 2 | hubingmei | 99 | | 5 | hubingmei4 | 101 | | 6 | jiang2 | 100 | | 7 | jiang22 | 70 | | 67 | jiang222 | 80 | | 98 | test | 105 | | 123 | test4 | 109 | | 999 | test2 | 56 | +-----+------------+------+ 9 rows in set (0.00 sec)
session 1 mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> explain delete from test_gap_lock where myid=100; +----+-------------+---------------+-------+---------------+-----------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------+-------+---------------+-----------+---------+-------+------+-------------+ | 1 | SIMPLE | test_gap_lock | range | idex_myid | idex_myid | 5 | const | 2 | Using where | +----+-------------+---------------+-------+---------------+-----------+---------+-------+------+-------------+ 1 row in set (0.00 sec) mysql> delete from test_gap_lock where myid=100; Query OK, 2 rows affected (0.00 sec) session 2 插入myid=99的记录依旧阻塞，存在gap锁；插入myid=97的记录成功 mysql> insert into test_gap_lock values(676,'gap recored test',99); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into test_gap_lock values(675,'gap recored test1',97); Query OK, 1 row affected (0.00 sec)
演示范例3（主键索引+范围当前读）
mysql> select * from test_gap_lock ; +-----+------------+------+ | id | name | myid | +-----+------------+------+ | 1 | jiang | 98 | | 2 | hubingmei | 98 | | 5 | hubingmei4 | 100 | | 6 | jiang2 | 100 | | 7 | jiang22 | 70 | | 67 | jiang222 | 80 | | 98 | test | 105 | | 123 | test4 | 109 | | 999 | test2 | 56 | +-----+------------+------+ 9 rows in set (0.00 sec) session 1 mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> explain select * from test_gap_lock where id > 100 for update; +----+-------------+---------------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | test_gap_lock | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where | +----+-------------+---------------+-------+---------------+---------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> select * from test_gap_lock where id > 100 for update; +-----+-------+------+ | id | name | myid | +-----+-------+------+ | 123 | test4 | 109 | | 999 | test2 | 56 | +-----+-------+------+ 2 rows in set (0.00 sec) session 2(id=3可插入；id=108无法插入，存在gap lock；id=123的记录无法select..in share mode,因为该记录上存在record lock；id=125可以被select..in share mode和update,这点比较奇怪，应该这也算是当前读，不过后来查看官方文档得知，gap锁只会阻塞insert操作，因为gap间隙中是不存在任何记录的，除了insert操作，其他的操作结果应该都等价于空操作，mysql就不去阻塞它了) mysql> insert into test_gap_lock values(108,'gap lock test3',123); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into test_gap_lock values(3,'gap lock test3',123); Query OK, 1 row affected (0.00 sec) mysql> select * from test_gap_lock where id=125 lock in share mode; Empty set (0.00 sec) mysql> explain select * from test_gap_lock where id=125 lock in share mode; +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ 1 row in set (0.00 sec) mysql> update test_gap_lock set myid=12345 where id=125; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0
Gap lock的内部加锁原理
gap lock的前置条件
1 事务隔离级别为REPEATABLE-READ，innodb_locks_unsafe_for_binlog参数为0，且sql走的索引为非唯一索引；
2 事务隔离级别为REPEATABLE-READ，innodb_locks_unsafe_for_binlog参数为0，且sql是一个范围的当前读操作，这时即使不是非唯一索引也会加gap lock；
gap lock的加锁步骤
针对上面的范例1（非唯一索引+范围当前读）和范例3（主键索引+范围当前读）比较好理解，那为什么范例2（非主键索引+等值当前读）为什么也会产生gap lock，这要从btree索引的原理讲起，我们都知道，btree索引是按照顺序排列的，并且innodb存在主键聚集索引，本人绘图能力有限，已范例2的加锁过程分析举例，手写加锁过程如下图：
从图中的数据组织顺序可以看出，myid=100的记录有两条，如果加gap锁就会产生三个间隙，分别是gap1（98,100），gap2（100,100），gap3（100,105），在这三个开区间（如果我高中数学没记错的话）内的myid数值无法插入，显然gap1还有(myid=99，id=3)(myid=99,id=4)等记录，gap2无实际的间隙，gap3还有（myid=101，id=7）等记录。并且，在myid=100的两条记录上加了record lock，也就是这两条数据业务无法被其他session进行当前读操作（范例三可以看出）。
Next-Key Locks
在默认情况下，mysql的事务隔离级别是可重复读，并且innodb_locks_unsafe_for_binlog参数为0，这时默认采用next-key locks。所谓Next-Key Locks，就是Record lock和gap lock的结合，即除了锁住记录本身，还要再锁住索引之间的间隙。
下面我们针对大部分的SQL类型分析是如何加锁的，假设事务隔离级别为可重复读。
select .. from
不加任何类型的锁。
select...from lock in share mode
在扫描到的任何索引记录上加共享的（shared）next-key lock，还有主键聚集索引加排它锁。
select..from for update
在扫描到的任何索引记录上加排它的next-key lock，还有主键聚集索引加排它锁。
update..where delete from..where
在扫描到的任何索引记录上加next-key lock，还有主键聚集索引加排它锁。
insert into..
简单的insert会在insert的行对应的索引记录上加一个排它锁，这是一个record lock，并没有gap，所以并不会阻塞其他session在gap间隙里插入记录。不过在insert操作之前，还会加一种锁，官方文档称它为insertion intention gap lock，也就是意向的gap锁。这个意向gap锁的作用就是预示着当多事务并发插入相同的gap空隙时，只要插入的记录不是gap间隙中的相同位置，则无需等待其他session就可完成，这样就使得insert操作无须加真正的gap lock。想象一下，如果一个表有一个索引idx_test，表中有记录1和8，那么每个事务都可以在2和7之间插入任何记录，只会对当前插入的记录加record lock，并不会阻塞其他session插入与自己不同的记录，因为他们并没有任何冲突。
假设发生了一个唯一键冲突错误，那么将会在重复的索引记录上加读锁。当有多个session同时插入相同的行记录时，如果另外一个session已经获得该行的排它锁，那么将会导致死锁。
insert导致的死锁现象演示1
mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `i` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`i`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) session 1 mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t1 VALUES(1); Query OK, 1 row affected (0.00 sec) session 2 这时session2一直被卡住 mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t1 VALUES(1); session 3 这时session3也一直被卡住 mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t1 VALUES(1); session 1 这时我们回滚session1 mysql> rollback; Query OK, 0 rows affected (0.00 sec)
发现session 2的insert成功，而session3检测到死锁回滚 session 2 Query OK, 1 row affected (28.87 sec) session 3 ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
死锁原因分析
首先session1插入一条记录，获得该记录的排它锁，这时session2和session3都检测到了主键冲突错误，但是由于session1并没有提交，所以session1并不算插入成功，于是它并不能直接报错吧，于是session2和session3都申请了该记录的共享锁，这时还没获取到共享锁，处于等待队列中。这时session1 rollback了，也就释放了该行记录的排它锁，那么session2和session3都获取了该行上的共享锁。而session2和session3想要插入记录，必须获取排它锁，但由于他们自己都拥有了共享锁，于是永远无法获取到排它锁，于是死锁就发生了。如果这时session1是commit而不是rollback的话，那么session2和session3都直接报错主键冲突错误。查看死锁日志也是一目了然：
insert导致的死锁现象2
另外一个类似的死锁是session1删除了id=1的记录并未提交，这时session2和session3插入id=1的记录。这时session1 commit了，session2和session3需要insert的话，就需要获取排它锁，那么死锁也就发生了；session1 rollback，则session2和session3报错主键冲突。这里不再做演示。
INSERT ... ON DUPLICATE KEY UPDATE
这种sql和insert加锁的不同的是，如果检测到键冲突，它直接申请加排它锁，而不是共享锁。
replace
replace操作如果没有检测到键冲突的话，那么它的加锁策略和insert相似；如果检测到键冲突，那么它也是直接再申请加排它锁。
INSERT INTO T SELECT ... FROM S WHERE ...
在T表上的加锁策略和普通insert一致，另外还会在S表上的相关记录上加共享的next-key lock。（如果是可重复读模式，则不会加锁）
CREATE TABLE ... SELECT ...
在select的表上加共享的next-key lock。
自增id的加锁策略
当一张表的某个字段是自增列时，innodb会在该索引的末位加一个排它锁。为了访问这个自增的数值，需要加一个表级锁，不过这个表级锁的持续时间只有当前sql，而不是整个事务，即当前sql执行完，该表级锁就释放了。其他session无法在这个表级锁持有时插入任何记录。
外键检测的加锁策略
如果存在外键约束，任何的insert，update，delete将会检测约束条件，将会在相应的记录上加共享的record lock，无论是否存在外键冲突。
