MySQL锁类型及作用

1. MySQL数据库有哪些锁？
2. MySQL数据库锁的应用场景有哪些？主要作用是什么？

• Shared and Exclusive Locks（共享锁与排他锁）

• Intention Locks（意向锁）

• Record Locks（行锁）

• Gap Locks（间隙锁）

• Next-Key Locks（Next-Key锁）

• Insert Intention Locks（插入意向锁）

• AUTO-INC Locks（自增锁）

• Predicate Locks for Spatial Indexes（空间索引锁）

共享锁与排他锁

如果事务T1持有数据行r共享锁(S)，此时事务T2可以立即获取数据行r共享锁，不能立即获取数据行r排他锁。如果事务T1持有数据行r排他锁，则事务T2请求数据行r排他锁需要等待事务T1释放数据行r排他锁。

意向锁

意向锁加锁规则：

• 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.
• 一个事务在获取某行记录的排它锁之前必须先获取表的意向排它锁。

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

意向锁一般情况下不会锁定任何行，除非请求表锁***LOCK TABLES … WRITE***。意向锁的作用是标识事务正在锁定某些行或者预计会锁定哪些行。

TABLE LOCK table `test`.`t` trx id 10080 lock mode IX

行锁

行锁是索引记录上的锁。例如：SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;阻止任何其他事务插入、更新或删除t.c1值为10的行。行锁总会锁定索引记录行，尽管一个可能没有显示创建索引的情况。比如，InnoDB创建了一个隐藏的聚集索引，并使用这个索引来锁定记录。（tips: MySQL 聚类簇索引和聚类簇索引）

执行SHOW ENGINE INNODB STATUS时，行锁状态详情：

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

间隙锁

间隙锁是指在索引记录之间的间隙上的锁，或者在第一个索引记录之前或最后一个索引记录之后的间隙上的一个锁。例如：SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;可以防止其他事务将值15插入列t.c1中，无论列中是否已经有这样的值，where条件范围内所有值之间的间隙已被锁定。一个间隙可以包含一个索引值、多个索引值、或者是空的（不包含任何索引）。
间隙锁是性能和并发性之间的平衡，可用于某些事务隔离级别，而不适用于其他事务隔离级别。
如果使用唯一键索引搜索特定的某一行，那么间隙锁不会生效。如果是多字段唯一索引的部分字段用于检索，此时间隙锁会生效。例如，SELECT * FROM child WHERE id = 100;只会使用索引行锁，不关心其他事务在id=100记录行前后的操作。如果id没有索引或有一个非惟一索引，语句会锁定记录行前面的间隙。
这里还值得注意的是，不同的事务可以在一个间隙上持有冲突的锁。例如，事务A可以在一个间隙上持有一个共享间隙锁(gap s-lock)，而事务B在同一个间隙上持有一个排他间隙锁(gap x-lock)。允许间隙锁冲突的原因是，如果从索引中清除了一条记录，则必须合并由不同事务持有的记录上的间隙锁。
InnoDB中的Gap锁是“纯抑制的”，这意味着它们的唯一目的是防止其他事务插入到Gap中。间隙锁可以共存。一个事务获得的间隙锁不能阻止另一个事务获得相同间隙上的间隙锁。共享和独占间隙锁之间没有区别。它们彼此不冲突，它们执行相同的功能。
间隙锁定可以显式禁用。如果将事务隔离级别更改为READ COMMITTED或启用innodb_locks_unsafe_for_binlog系统变量(现在已弃用)，就可以启用。在这种情况下，间隙锁定对于搜索和索引扫描是无效的，仅用于外键约束检查和重复键检查时有效。值得注意的是如果计算where条件后并未匹配到行，则会释放行锁。对于UPDATE语句，InnoDB执行一个“半一致(semi-consistent)”的读取，这样它会返回最新提交的版本给MySQL，以便MySQL可以确定这行是否匹配UPDATE的WHERE条件。

Next-Key锁

执行如下SQL语句时，Next-Key锁会展示这样的详细信息。

SHOW ENGINE INNODB STATUS 

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10080 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

next-key lock是索引记录上的记录锁和索引记录之前间隙上的间隙锁的组合。InnoDB执行行级锁的方式是这样的:当它搜索或扫描一个表索引时，它会对遇到的索引记录设置共享或排他锁。因此，行级锁实际上是索引记录锁。索引记录上的next-key锁也会影响该索引记录之前的“间隙”。也就是说，next-key锁是一个索引记录锁加上一个间隙锁，该间隙位于前述索引记录之前。如果一个会话对一个索引中的记录R有一个共享或排他锁，那么另一个会话就不能在索引顺序R之前的间隙中插入一个新的索引记录。
假设索引包含值10、11、13和20。这个索引可能的next-key锁包括以下区间，其中圆括号表示不包含间隔端点，方括号表示包含端点:

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

默认情况下，InnoDB运行在REPEATABLE READ事务隔离级别。在这种情况下，InnoDB使用next-key锁进行搜索和索引扫描，从而防止出现幻读。

插入意向锁

插入意向锁是一种间隙锁，在插入行之前的insert操作设置。如果多个事务插入记录的位置不在其他事物插入意向gap内时不需要等待其他间隙锁的释放。假设有值为4和7的索引记录，现在有尝试分别插入值为5和6的单独事务，在获得插入行的排他锁之前，每个事务都用插入意图锁锁住4和7之间的间隙，但不会互相阻塞，因为行是不冲突的。
下面的示例演示了在获得插入记录上的排他锁之前使用插入意图锁的事务。这个例子涉及2个客户端，客户端A和客户端B。
客户端A创建一个包含两条索引记录(90和102)的表，然后启动一个事务，对ID大于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;
+-----+
| id  |
+-----+
| 102 |
+-----+

客户端B开始一个事务，将一条记录插入到间隙中。事务需要等待获得排他锁时已获取到一个插入意向锁。

mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);

执行SHOW ENGINE INNODB STATUS和INNODB monitor输出中，插入意向锁的事务数据类似如下:

RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000066; asc    f;;
 1: len 6; hex 000000002215; asc     " ;;
 2: len 7; hex 9000000172011c; asc     r  ;;...

AUTO-INC锁

AUTO-INC锁是一种特殊的表级锁，由插入到带有AUTO_INCREMENT列的表中的事务获得。有一个最简单的情况下，如果一个事务正在向表中插入值，那么任何其他事务都必须等待该事务的插入操作，以便该事务插入的行接收连续的主键值。innodb_autoinc_lock_mode变量可以控制自动增量锁定的算法。它允许用户选择如何在自动增量值的可预测序列和插入操作的最大并发性之间进行权衡。

空间索引的断言锁

MySQL支持在空间数据上设置空间索引。为了处理SPATIAL索引的操作涉及的锁定，next-key锁不能很好地支持REPEATABLE READ或SERIALIZABLE事务隔离级别。因为多维数据中没有绝对排序的概念，所以不清楚哪一个是“next”键。
为了支持设置有SPATIAL索引的表的事务隔离级别，InnoDB使用断言锁。SPATIAL索引包含最小边界矩形(MBR)值，因此InnoDB通过在用于查询的MBR值上设置断言锁来强制实现对索引进行一致性的读。其他事务不能插入或修改与查询条件匹配的行。

小结

锁类型	作用	应用场景
行级锁-共享锁(S)	持有锁事务可以进行读操作
行级锁-排他锁(X)	持有锁事务可以进行更新或者删除操作
意向锁(Intention Locks)	表级锁，可以持有多行记录共享锁或者排他锁	SELECT … LOCK IN SHARE MODE; SELECT … FOR UPDATE;
Record Locks	索引记录上的锁
Gap Locks	间隙锁
Next-Key Locks	索引记录上的记录锁和索引记录之前间隙上的间隙锁的组合	InnoDB使用next-key锁进行搜索和索引扫描，从而防止出现幻读
Insert Intention Locks	插入意向锁，一种间隙
AUTO-INC Locks	表级锁	保证事务获取有连续值的自增值(自增值的连续性和事务的并发性可以使用算法进行平衡)

提问区

1. 什么情况下间隙锁生效？

参考文献

1. InnoDB Locking