关于锁，mysql8.0官方文档 https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

1.intention locks【意向共存】

注意，意向锁是表级锁
Intention locks are table-level locks（表级锁） that indicate which type of lock (shared or exclusive) a transaction requires later for several rows in a table. 用来体现当前事务将要对表中的若干行加S或者X锁的意向

There are two types of intention locks:

• An intention shared lock (IS) indicates that a transaction intends to set a shared lock on individual rows in a table. 表明要对表的某几行加共享锁的意向
• An intention exclusive lock (IX) indicates that a transaction intends to set an exclusive lock on individual rows in a table. 表明要对表的某几行加互斥锁的意向

The intention locking protocol is as follows:

• Before a transaction can acquire a shared lock on a row in a table, it must first acquire an IS lock or stronger on the table.
• Before a transaction can acquire an exclusive lock on a row in a table, it must first acquire an IX lock on the table.

即：get IS then get S, get IX then get X，事务真正获取锁之前，都要表示自己的意向
For example, [SELECT ... FOR SHARE] sets an IS lock first, and [SELECT ... FOR UPDATE] sets an IX lock first.

Table-level lock type compatibility is summarized in the following matrix

表级锁的兼容性矩阵（注意，表内的锁都是【表级别】的）

	X	IX	S	IS
X	Conflict	Conflict	Conflict	Conflict
IX	Conflict	Compatible	Conflict	Compatible
S	Conflict	Conflict	Compatible	Compatible
IS	Conflict	Compatible	Compatible	Compatible

Intention locks do not block anything except full table requests (for example, LOCK TABLES ... WRITE). 它只是为了表明一个意向
The main purpose of intention locks is to show that someone is locking a row, or going to lock a row in the table.

IS IX的任意组合都是可共存的，即【意向共存】

思考：为什么要表明意向？

表明意向本质上是加了表锁
当一个新的事务想要发起 LOCK TABLES ... WRITE 等X锁表的操作，这时如果没有意向锁的话，就需要逐行检测是否所在表中的某行是否存在写锁，从而引发冲突，效率太低
而引入意向锁的话，可以通过意向提示当前这张表正处于被“占用”的状态，新事务要X锁表的话还得等等

2.Record Locks【加锁位置是索引】

A record lock is a lock on an index record, even if a table is defined with no indexes. For such cases, InnoDB creates a hidden clustered index and uses this index for record locking.

record lock是加在索引上的

3.Gap Locks【gap lock共存，但gap lock不兼容insert intention lock，而insert intention lock兼容gap lock】

A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record.
For example, SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; prevents other transactions from inserting a value of 15 into column t.c1, whether or not there was already any such value in the column, because the gaps between all existing values in the range are locked.

gap锁的目的是防止其他事务在索引的间隙中插入新记录，避免幻读的发生。gap锁用在RR事务隔离级别

注意，不同的事务可以在同一间隙上持有相互冲突的锁，或者这么说更好，间隙上的锁被设计成是共存的
例如，事务 A 可以在间隙上持有共享间隙锁（间隙 S 锁），而事务 B 在同一间隙上持有排他间隙锁（间隙 X 锁）
允许冲突间隙锁共存的原因是，如果我们从索引中清除一条记录，如1 3 5，把3去掉，则原来13间隙和35间隙会合并成一个间隙，如果不同事务持有互斥的间隙锁的话，就无法实现这一点
gap locks’ only purpose is to prevent other transactions from inserting to the gap. Gap locks can co-exist. A gap lock taken by one transaction does not prevent another transaction from taking a gap lock on the same gap. There is no difference between shared and exclusive gap locks.

4.Next-Key Locks【gap lock + record lock】

A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.
邻键锁，下一键锁，顾名思义，自然是锁定【从当前索引结点开始，一直到下一索引结点的所有位置，就是间隙+下一结点】，但不包括当前索引结点
next-key lock = gap lock + record lock，注意gap lock + record lock先后顺序

If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order

在RR隔离级别下，next-key lock 是 innodb加锁的最小单位

RR隔离级别下innodb的加锁原则

基本原则

• 锁，都是作用在索引上的。索引是b+tree，因此锁是作用在b+tree结点上的
• next-key lock 是加锁的基本单位

锁的优化场景

• 对索引的等值查询，当在唯一索引上命中唯一索引记录的时候，next-key lock退化为record lock。因为唯一索引保证了不重复性，这种情况下在前后间隙向插入新记录，都不会发生幻读
• 对索引的等值查询，从满足等值条件的值开始，向右遍历到第一个不满足等值条件记录结束，然后将最后不满足条件记录的next-key lock退化为gap lock。这实际上就是把满足等值条件的若干记录及其前后和内部间隙加锁了，因为这里的索引不保证唯一性，所以需要把前后间隙也给加上gap锁，这样其他事务无法插入，避免幻读

个人认为， 非得去理解【next-key lock是加锁的基本单位并附带优化场景】，有点死板。实际上，要从加锁的目的去理解，加锁是为了防止幻读，

• 对于唯一索引，既然索引自身已经保证了不重复性，那锁住索引结点（非叶子结点，因为叶子结点是索引到的最终值）本身就足够，索引结点前后的间隙还是可以随意insert的
• 对于非唯一索引，索引结点（非叶子结点）本身可以重复的，当在前后间隙插入时，一旦插入值相同的索引结点，就产生了幻读。因此对于非唯一索引，需要在锁住所有命中的索引结点的同时，再把前后间隙一并锁上

next-key lock是加锁的基本单位并附带优化场景仅仅是上述思想的具体实现，无需过多纠结

下面看个加锁的例子：

准备工作

create database `mysql_test`;

use mysql_test;

create table `test_next_key_lock` (
   `id` int(11) NOT NULL
   `age` int(11) NOT NULL
    PRIMARY KEY (`id`),
    KEY `ix_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into `test_next_key_lock` 
values (10,10),(20,20),(22,20),(30,30);

起两个session

session1	session2
START TRANSACTION;
select * from test_next_key_lock where age = 20 for update; /*age = 20对应的ix_age索引结点及其前后间隙都加上了锁*/
	insert into test_next_key_lock values (11,10); /*阻塞*/
	insert into test_next_key_lock values (9,10); /*成功*/
	insert into test_next_key_lock values (29,30); /*阻塞*/
	insert into test_next_key_lock values (31,30); /*成功*/
comit;

一图胜千言
由https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html可以得到ix_age索引的样例结构

innodb锁索引示例

当(id, age) = (11,10) or (id, age) = (29, 30)，如要插入记录，则待插入的索引都处于被锁住的间隙中，因为阻塞而无法插入。而(id, age) = (9,10) or (id, age) = (31, 30)，则可以成功insert
因此，next-key lock无所谓什么左闭右开还是左开右闭，就只看待插入的索引是否处于被锁住的位置

mysql RR级别下gap lock可能造成死锁

数据依然使用insert into test_next_key_lock values (10,10),(20,20),(22,20),(30,30);
2个session

session1	session2
START TRANSACTION;
	START TRANSACTION;
select * from test_next_key_lock where age = 25 for update; /*间隙20-30上gap lock */
	select * from test_next_key_lock where age = 25 for update; /*间隙20-30上gap lock */
insert into test_next_key_lock values (25, 25); /*阻塞，因为session2持有gap lock*/
	insert into test_next_key_lock values (25, 25); /*dead lock*/

5.Insert Intention Locks【插入意向锁，支持并发插入的gap锁】

区别于Intention Locks，Insert Intention Locks属于gap lock而不是table_level lock

对已有数据行进行update or delete，必须加强互斥的next-key lock
对于数据的insert，加的是Insert Intention Lock

An insert intention lock is a type of gap lock set by [INSERT] (https://dev.mysql.com/doc/refman/8.0/en/insert.html "13.2.6 INSERT Statement") operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.

插入意向锁是在插入一条记录行前，由 INSERT 操作产生的一种间隙锁。该锁用以表示插入意向，当多个事务在同一区间（gap）插入位置不同的多条数据时，事务之间不需要互相等待。假设存在两条值分别为 4 和 7 的记录，两个不同的事务分别试图插入值为 5 和 6 的两条记录，每个事务在获取插入行上独占的（排他）锁前，都会获取（4，7）之间的间隙锁，但是因为数据行之间并不冲突，所以两个事务之间并不会产生冲突（阻塞等待）

再次强调，虽然insert intention lock本质上是gap lock，但它是特殊的gap lock，insert intention lock与常规的gap lock是互斥的

---

现在来看一下insert操作会加什么锁，加锁的顺序是什么

关于insert操作要加的锁 （an insert intention gap lock, [and a possible shared lock on the duplicate index record], and an exclusive lock on the inserted row, in order）

• Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6 each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.
  （If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock. This can occur if another session deletes the row.）
• INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.

为什么a shared lock on the duplicate index record可能会导致死锁？

本质上是，多个事务同时持有了同一个duplicate index record的S lock，然后又各自试图加 X lock，于是死锁
看这个case

说明：
事务1 insert xx 但没提交，获得了xx的 X lock。但事务1没提交，对于事务2和事务3，它们都处于一种可能的duplicate index record的状态，这个状态需要等待事务1 commit 或者 rollback 才能确定。一旦事务1 commit，这个可能的duplicate index record就确定为 duplicate index record，于是事务2和事务3都拿到S lock，两者各自想更新就dead lock了

---

2个 gap lock 与 insert intention lock的case：

case1 (gap lock 阻塞insert intention lock)

session1	session2
START TRANSACTION; 403794617
	START TRANSACTION; 403794611
select * from test_next_key_lock where age = 25 for update; /*间隙20-30上gap lock */
	insert into test_next_key_lock values (26, 26); /*尝试加insert intention lock被间隙20-30上的gap lock阻塞*/

用show engine innodb status看下现在的事务情况

---TRANSACTION 403794617, ACTIVE 259 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 42053316, OS thread handle 140201663678208, query id 156367206095 10.89.5.111 root update
insert into test_next_key_lock values (26,26)
------- TRX HAS BEEN WAITING 259 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1424 page no 4 n bits 80 index ix_age of table `mysql_test`.`test_next_key_lock` trx id 403794617 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000001e; asc     ;;
 1: len 4; hex 8000001d; asc     ;;

------------------
---TRANSACTION 403794611, ACTIVE 1248 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 42053320, OS thread handle 140201751918336, query id 156367614898 10.89.5.111 root starting
show engine innodb status
Trx read view will not see trx with id >= 403794612, sees < 403794612

lock_mode X locks gap before rec insert intention waiting说明尝试加insert intention lock被间隙20-30上的gap lock阻塞

case2 (insert intention lock 不阻塞 gap lock)

session1	session2
START TRANSACTION; # 419999541
	START TRANSACTION; # 419999542
insert into test_next_key_lock values (23, 23); # 往gap里插入新行，正常
	select * from test_next_key_lock where age > 20 for update; # 阻塞

TRANSACTION 419999542, ACTIVE 384 sec starting index read 
mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 2 row lock(s) 
MySQL thread id 42353702, OS thread handle 140203119326976, query id 157525298563 10.89.5.111 root Sending data 
select * from test_next_key_lock where age > 20 for update 
------- TRX HAS BEEN WAITING 231 SEC FOR THIS LOCK TO BE GRANTED: 
RECORD LOCKS space id 1424 page no 4 n bits 80 index ix_age of table `mysql_test`.`test_next_key_lock` trx id 419999542 lock_mode X waiting Record lock, heap no 10 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 
0: len 4; hex 80000017; asc ;; 
1: len 4; hex 80000017; asc ;;

TRANSACTION 419999541, ACTIVE 391 sec 
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1 MySQL thread id 42330810, OS thread handle 140208852051712, query id 157525555491 10.89.5.111 root

可以看到，事务419999542处于lock_mode X waiting, 等待一个Record lock。这说明，事务419999541持有了 index ix_age 上 23 的Record lock，事务419999542必须等待事务419999541commit或者rollback释放这个锁

锁兼容性（来源网络）

总结几个思考：

• 通过二级索引加锁 = 二级索引加锁 + 主键索引加锁
  使用主键索引需要加一把锁，而使用二级索引需要在二级索引和主键索引上各加一把锁。注意，加锁锁定记录之后，如果需要update某个字段而该字段上又建有索引，那么还需要继续去获取该字段索引上相应位置的锁
  如https://www.jianshu.com/p/b87dd0fb70b7的最后一个案例，通过主键id锁定记录，要修改字段module_id（建有索引），还需要获取idx_module_id索引上相应位置的锁

• gap lock为什么设计成彼此兼容？
  从正面看，gap lock的目的只有一个，就是为了防止别的事务在gap内的新插入，因此gap lock只需要阻塞insert intention lock就可以
  从反面看，为了提高写性能。否则并发大量写操作的时候，gap重叠的概率很大，gap lock如果不彼此兼容，那并发写就退化成了串行写

• 为什么不能使用gap lock来代替insert intention lock？
  gap锁可以共存的，gap锁存在的唯一目的是防止有其他事务进行【插入】，避免造成幻读。假如利用gap锁来代替插入意向锁：
  一方面，两个事务可以同时对一个gap任意进行插入，而无视了插入位置相同的情况。
  另一方面，在update或delete时要加next-key lock（可能会有锁优化），因此在插入时需要一种新的gap锁来与更新和删除进行互斥，来保证update或delete时不能在被锁定的间隙进行insert，否则这边刚删了那边又insert回去，就会发生幻读
  插入意向锁支持当多个事务在同一区间（gap）插入位置不同的多条数据时，事务之间不需要互相等待，这是它区别于gap锁的特性：支持并发插入

• 在插入时是否可以不用插入意向锁，而仅使用记录锁？
  未插入数据前，根本不存在记录锁。由于数据在插入之前，对应的索引记录是不存在的，因此索引记录锁必须是插入后才能添加