数据库锁

最近想要好好研究下数据的锁，所以又翻阅了mysql的英文手册，并在本篇文章做了翻译，并附上部分测试用例，如有错误，请大家指正。
英文原文来自：https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-insert-intention-locks

数据库的锁有以下几个分类：

• Shared and Exclusive Locks 共享锁 & 排他锁
• Intention Locks 意向锁
• Record Locks 记录锁
• Gap Locks 间隙锁
• Next-Key Locks
• Insert Intention Locks 插入意向锁
• AUTO-INC Locks
• Predicate Locks for Spatial Indexes

意向锁

InnoDB支持多种粒度的锁，允许行锁和表锁同时存在。例如， LOCK TABLES … WRITE语句取得了相应表上的一个排他锁。为了使多粒度锁可行，InnoDB使用意向锁。意向锁是一个表级锁，代表一个事务将要获取表里面一行的共享锁还是排他锁。意向锁有两种类型：
intention shared lock (IS)：意向共享锁，表明一个事务将要获取表里某些行上的共享锁；
intention exclusive lock (IX)： 意向排他锁，表明一个事务将要获取表里某些行上的排他锁；
例如：SELECT … LOCK IN SHARE MODE会设置一个IS锁（当然执行成功后也会有一个S锁），SELECT … FOR UPDATE会设置一个IX锁（当然，执行成功后会有一个X行锁）。
意向锁的规范如下：
一个事务获取行的S锁之前，必须要获取到一个表上的IS锁，或者一个更强的锁；
一个事务获取行的X锁之前，必须要获取到一个表上的IX锁。锁的兼容性如下：

一个事务申请锁时，只有申请的锁与已经存在的锁兼容时才能成功，否则事务只能等待有不兼容的锁释放掉。若有冲突时如果锁申请成功会造成死锁或者其他错误。意向锁只会阻塞全表的请求（例如： LOCK TABLES … WRITE）。意向锁的目的就是表明有事务已经锁行或者将要锁行。

记录锁

行锁是在一个索引行上的锁，例如SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;这个会阻止任何在该行上的插入/删除/更新操作。
行锁经常是在索引行上加锁，即使表内没有定义索引。在没有索引的情况下，InnoDB会创建clustered index，并使用这个索引。
间隙锁
间隙锁是指在两个索引行之间(select between and for update)，或者最小行之前(<)，或者最大行(>)在之后的的行上的锁。例如SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE，禁止插入一个t.c1=15的记录，不管该表中有没有一条t.c1=15的记录。所以，间隙锁可以在一行或者多行记录上。
当使用唯一索引的列作为查询条件时，不会使用间隙锁，只会使用行锁。但是，当唯一索引是在多列，查询条件只使用部分列时，间隙锁还是会存在的。
不同事务在同一个间隙可以拥有不同类型的锁，如事务A可以在gap上有GAP-S锁，事务B可以在gap上有GAP-X锁，这种情况被允许的原因是：如果从索引中清除一条记录，则必须合并不同事务持有的记录上的间隙锁。
gap lock的意义只在于阻止区间被插入，防止幻读，因此是可以共存的。一个事务获取的gap lock不会阻止另一个事务获取同一个gap的gap lock。共享和排他的gap lock是没有区别的。他们相互不冲突，且功能相同。
gap lock可以被显示禁止。当在数据库隔离级别在READ COMMITTED及下时，gap lock被禁止用于索引查找，而只用于外检约束检查和重复键检查。

Next-Key 锁

Next-Key 锁是一个行锁再加上该行之前的一个间隙锁(select where id=XX for update)。InnoDB行级别加锁实际是在搜索或者扫描一个表的索引时，遇到符合条件的索引时，给该索引行加上S锁或者X锁，因此行锁其实是“索引-行”锁。一个索引行上的Next-Key 锁同时也影响该行之前的一个gap，因此Next-Key 锁是一个行锁再加上该行之前的一个间隙锁。如果一个session有一个R行的S锁或者X锁，则另一个session不能立即在该行之前插入一条记录。
假设一个有索引记录 10，11，13，20。则可能的Next-Key 锁包含：(negative infinity, 10] (10, 11] (11, 13] (13, 20] (20, positive infinity)。
通常情况下，InnoDB的隔离级别是REPEATABLE READ ，在这种隔离级别下，就是通过Next-Key 锁来防止幻读。
例子：https://cloud.tencent.com/developer/article/1447138

Insert Intention Locks

插入意向锁是一种gap锁，这种锁是INSERT语句会设置的，并且这种锁优先级高于行插入。这种锁允许不同的事务在同一个间隙同时插入不同位置的数据。假设有索引行4和7，那么不用的事务分别想插入5和6，两个锁定4和7之间gap的插入意向锁优先级高于获取锁插入行的排他锁，因此不会互相阻塞。
下面的例子说明了一个事务获取插入意向锁优先级会高于获取插入行的排他锁。例子涉及两个客户端A 和B。A创建了一个表，包含两个索引行90和102，然后开启一个事务，多大于100的行加上排他锁，这个锁是100-102的一个间隙锁。
mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);
mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
B开启另一个事务向这个gap中插入一条数据，这个语句获取了一个插入意向锁，同时等待一个排他锁。
mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);

AUTO-INC Locks

自增锁是一个表级锁，表设置了AUTO_INCREMENT列，向表中插入数据时使用该锁。如果一个事务向表中插入数据，其他事务必须等待，这样的话第一个事务才能获取到连续的自增值。

总结：

注：间隙锁防止幻读的。

测试用例:

建测试表

CREATE TABLE test_key (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  no int(11) DEFAULT NULL,
  PRIMARY KEY (id),
  KEY idx_no (no)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

初始化3条数据：

insert into test_key(no) vaules(8),(10),(15),(20)

假设两事务A,B

• 测试GAP锁

（1）A:

	set autocommit = 0;
	set session transaction isolation level REPEATABLE READ;
	SELECT * FROM test_key WHERE no<15 for update;  //该语句使用gap锁,对小于15的范围加上了锁

（2.1）B:

set autocommit = 0;
set session transaction isolation level REPEATABLE READ;
INSERT into test_key(no) VALUES(14); //因为A对对小于15的范围加上了锁，所以A事务不提交时，该语句阻塞

（2.2）B:

set autocommit = 0;
set session transaction isolation level REPEATABLE READ;
INSERT into test_key(no) VALUES(16);//因为A对对小于15的范围加上了锁，所以此语句正常执行
between 和>同理。

• 测试Next-Key Locks

（1）A:

set autocommit = 0;
set session transaction isolation level REPEATABLE READ;
SELECT * FROM test_key WHERE no=15 for update;  //因为no不是唯一索引，所以该语句使用Next-Key锁在（10，15]的范围加上了锁，即一个15上的记录锁，一个（10，15）之间的间隙锁

（2）B：

set autocommit = 0;
set session transaction isolation level REPEATABLE READ;
INSERT into test_key(no) VALUES (8);因为A对（10,15]范围加上了锁，所以此语句正常执行,又插入一条no=8的记录，所以此时对B来说?️两个8，并且在no=8两行行上都加上了一个记录锁
INSERT into test_key(no) VALUES (14);因为A对（10,15]范围加上了锁，所以A事务不提交时此语阻塞。

（3）A:

SELECT * FROM test_key WHERE no<10 for update; 因为B对no=8加上了记录锁，所以此处B不提交时是查不出原来有的no=88这个数据的。

• 测试Intention Locks

例1:

（1）A:

set autocommit = 0;
set session transaction isolation level REPEATABLE READ;
SELECT * FROM test_key WHERE no=15  LOCK in SHARE mode; //获取到了一个IS和一个S锁

（2）B：

set autocommit = 0;
set session transaction isolation level REPEATABLE READ;
lock tables test_key read; //申请表的S锁，可以获取到
lock tables test_key write;//申请表的写锁，阻塞

例2:
（1）A:

set autocommit = 0;
set session transaction isolation level REPEATABLE READ;
SELECT * FROM test_key WHERE no=15 for update; //获取到了一个IX和一个X锁

（2）B：

set autocommit = 0;
set session transaction isolation level REPEATABLE READ;
lock tables test_key read; //申请表的S锁，阻塞，同理lock tables test_key write也会阻塞