MySQL InnoDB Locking(官方文档)

如果事务T1持有r这行的独占(X)锁，则不能立即批准来自某个不同事务T2的请求，请求r行上任何一种类型的锁。相反，事务T2必须等待事务T1释放对行r的锁。接下来看下因为行锁的独占和共享导致的死锁，实验环境Mysql5.7：

CREATE TABLE yangzai  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(255) NULL DEFAULT NULL,
  `b` int(255) NULL DEFAULT NULL,
  `c` int(255) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `un_a`(`a`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 20 CHARACTER SET = utf8
INSERT into yangzai(a,b,c) VALUES (1,2,3);

语句的顺序就是SQL的执行顺序

会话A开启事务，执行INSERT插入操作，a是唯一索引，会话A在插入这条数据时，会先使用a索引当前读判断是否唯一索引冲突，yangzai表已经预先初始化(1,2,3)，会话A插入数据失败，但是会话A还是持有a索引的共享锁（锁的两阶段，只有在事务提交后锁才会释放），会话B开始更新a=1这行数据，更新前，执行器会调用InnoDB引擎接口获取a=1这行数据，并且在a=1索引上加独占锁，会话B在a=1索引加独占锁等待，因为a=1的共享锁被会话A持有还未释放

会话A开始执行更新语句，需要在a=1索引加上独占锁，会话A在a=1索引加独占锁等待，因为会话B也要在a=1索引加独占锁，导致会话B等待会话A在a=1索引的共享锁释放，事务 A 和事务 B 在互相等待对方的资源释放，就是进入了死锁状态。

当出现死锁以后，有两种策略：一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

二、意向锁

InnoDB支持多粒度锁，允许行锁和表锁共存。例如，LOCK TABLES ... WRITE之类的语句在指定的表上采用独占锁(X锁)。为了使多粒度级别的锁定切实可行，InnoDB使用意向锁。意向锁是表级锁，指示事务稍后需要对表中的行使用哪种类型的锁(共享或独占)。有两种类型的意向锁：

• 意向共享锁(IS)表示事务打算对表中的单个行上设置共享锁。
• 意向独占锁(IX)表示事务打算对表中的单个行设置独占锁。

例如，SELECT ... FOR SHARE设置一个IS锁，SELECT ... FOR UPDATE 设置一个IX锁. 意向锁的协议如下：

• 事务在获取表中某行上的共享锁之前，必须先获取表上的IS锁或更强的IS锁。
• 事务在获取表中某行上的独占锁之前，必须先获取该表的IX锁。

下表汇总了表级锁类型兼容性，即表锁和表级意向锁的兼容性。

如果请求事务与现有事务的锁兼容，则会将锁授予该事务，但如果它与现有锁冲突，则不会授予该事务锁。事务将一直等到冲突的现有锁被释放。如果锁请求与现有锁冲突，并且由于会导致死锁而无法授予，则会发生错误。

意向锁只会阻塞全表请求(比如LOCK TABLES ... WRITE/READ，如果要对一行设置共享锁，LOCK TABLES ... WRITE的操作就会阻塞，意向锁就是为了使行锁和表锁同时共存，已经有一个事务对表的一行已经加独占锁，防止其他事务在对表加独占锁)。意向锁的主要目的是显示某事务正在锁定一行，或者将要锁定表中的一行。

意向锁的事务数据类似于下面的SHOW ENGINE INNODB STATUS或者 InnoDB monitor 的输出

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

三、记录锁

记录锁是对索引记录的锁。例如，SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;防止其他事务插入、更新或删除t.c1的值为10的行。 记录锁总是锁定索引记录，即使表定义时没有索引。在这种情况下，InnoDB会创建一个隐藏的聚集索引(主键索引)，并使用这个索引来锁定记录。 记录锁的事务数据类似于下面的SHOW ENGINE INNODB STATUS或者 InnoDB monitor 的输出：

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列中，无论该列中是否存在任何此类值，因为该范围中所有现有值之间的间隙已被锁定。 间隙可能跨越单个索引值、多个索引值，甚至为空。 间隙锁是性能和并发性之间权衡的一部分，在某些事务隔离级别下而不是其他事务隔离级别中使用。间隙锁使用在RR(可重复读事务隔离级别中，间隙锁解决幻读)。 使用唯一索引来搜索唯一行的锁定行的语句不需要间隙锁锁定，因为使用唯一索引进行等值搜索不会出现幻读。（这不包括搜索条件包含多列唯一索引的情况；在这种情况下，会发生间隙锁定。）例如，如果id列具有唯一索引，则以下语句仅对id值为100的行使用索引记录锁，并不影响而其他会话是否插入：

SELECT * FROM child WHERE id = 100;

如果id没有索引或索引不是唯一索引，则语句会锁定前面的间隙。 这里还值得注意的是，不同的事务可能会在一个间隙中持有冲突的锁。例如，事务A可以再一个gap上持有一个共享的gap锁（gap S-lock），而事务B在同一个gap上持有一个独占的gap锁（gap X-lock）。允许冲突的间隙锁的原因是，如果从索引中清除记录，则必须合并由不同事务保留在记录上的间隙锁。 InnoDB中的Gap锁是“完全禁止的”，这意味着它们的唯一目的是防止其他事务插入到Gap中。间隙锁可以共存。一个事务执行的间隙锁不会阻止另一个事务对同一个间隙执行间隙锁。共享锁和独占锁之间没有区别。它们不会相互冲突，它们执行相同的功能。 可以显示禁用间隙锁定。如果将事务隔离级别更改为READ COMMITTED，则会发生这种情况。 在这些情况下，间隙锁定在搜索和索引扫描中被禁用，并且仅用于外键约束检查和重复键检查。 使用READ COMMITTED隔离级别还有其他效果。在MySQL评估WHERE条件之后，将释放非匹配行的记录锁。对于UPDATE语句，InnoDB执行“（semi-consistent）半一致”读取，这样它会将最新提交的版本返回给MySQL，以便MySQL可以确定行是否与更新的WHERE条件匹配。

五、Next-Key Locks

一个next-key锁是索引记录上的记录锁和索引记录之前的间隙锁的组合。 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)

对于最后一个间隔，下一个next-key锁将间隔锁定在索引中最大值之上，并且“supremum”伪记录的值高于索引中的任何实际值。上确界不是真正的索引记录，因此，实际上，下一个键锁只锁定最大索引值后面的间隙。 默认情况下，InnoDB在可重复读事务隔离级别运行。在这种情况下，InnoDB使用next-key锁进行搜索和索引扫描，从而防止出现幻读行。 next-key锁的事务数据显示类似于下面的SHOW ENGINE INNODB STATUS或者 InnoDB monitor 的输出：

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        ;;

六、插入意向锁

插入意向锁是在行插入之前由插入操作设置的一种间隙锁。此锁表示插入的意图，这样，如果插入到同一索引间隙中的多个事务没有在间隙中的同一位置插入，那么它们就不必彼此等待。假设存在值为4和7的索引记录。尝试分别插入值5和6的单独事务，在获取插入行的独占锁之前，每个事务都使用插入意向锁锁定4和7之间的间隙，但是不阻塞对方因为插入行不冲突。 下面的示例演示在获取插入记录的独占锁之前获取插入意图锁的事务。这个例子涉及两个客户端，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_INCREMENT列的表中获得。在最简单的情况下，如果一个事务正在向表中插入值，则任何其他事务都必须等待自己向该表中插入，以便第一个事务插入的行接收连续的主键值。 innodb_autoinc_lock_mode配置选项控制用于自动增量锁定的算法。它允许您选择如何在可预测的自动增量值序列和插入操作的最大并发性之间进行权衡。

八、Predicate Locks for Spatial Indexes

InnoDB支持对包含空间列的列进行空间索引。 为了能处理涉及空间索引的操作的锁定，next-key锁不能很好地支持REPEATABLE READ 或 SERIALIZABLE的事务隔离级别。多维数据没有绝对排序的概念，所以不清楚哪一个是"下一个"键。 为了支持具有空间索引的表的隔离级别，InnoDB使用predicate锁。空间索引包含最小绑定矩形（MBR）值，因此InnoDB通过对用于查询的MBR值设置predicate锁来强制对索引进行一致读取。其他事务处理无法插入或修改与查询条件匹配的行。

九、全局锁

• 如果在主库上备份，在备份期间都不能执行更新操作，业务上只能查询，基本上停摆
• 如果在从库上备份，在备份期间从库不能执行主库同步过来的binlog，会导致主从延迟，如果大部分查询都是走从库，会导致查询出来的数据不一致。

官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。一致性读的前提是引擎要支持这个隔离级别。MyISAM这种不支持事务的引擎，如果备份过程有更新数据，只能取到最新数据，就破坏备份的一致性。就只能用全局锁进行保证备份的一致性。 set global readonly=true也可以使全库只读，readonly可以让全库只读，但是备份一致性还是建议使用全局锁:

• readonly常用来判断一个库是主库还是备库。
• 客户端异常处理机制上。全局锁客户端异常断开，Mysql会自动释放这个全局锁，整库回到可以正常的更新。将整个库设置为 readonly 之后，如果客户端发生异常，数据库也会一直保持 readonly 状态，会导致整个库长时间处于不可写状态。

十、表级锁

mysql表级别的锁有两种：一种是表锁，一种是元数据锁。 表锁的语法是lock tables … read/write， 和全局锁类似，可以主动unlock tables释放锁，客户端异常断开也会自动释放。MyISAM不支持行锁，只支持最小粒度的表级锁。lock tables除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。举个例子, 如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表。 元数据表级锁，无需显示加上，在访问一个表的时候会被自动加上。防止DDL和DML在不同线程同时进行。如果一个查询正在遍历一个表中数据，而执行期间有其他线程对这个表结构做变更，删除一列，查询线程获取到的数据结果跟表结构对不上，绝对不允许。元数据表级锁有两种类型，元数据表级读锁，元数据表级写锁。

• 元数据表级读锁不互斥，允许多个线程同时对同一张表进行增删改查。
  
• 元数据表级读锁写锁、写锁写锁互斥，保证表结构变更。如果存在多个线程同时给表加字段，要等待其他线程执行完才能开始执行。

使用show processlist;查看连接信息