MySQL5.7官方文档翻译-innodb事务模型与锁
官方文档：
命令查询记录
查询事务的锁定（可以查看事务存在的意图锁，记录锁）
show engine innodb status
查询当前会话的事务级别
select @@transaction_isolation;
查询全局事务级别
select @@gloabal.transaction_isolation;
设置当前会话的事务级别
set session transaction isolation level repeatable read;
锁定读取（只有在禁用自动提交时才能set autocommit=0)
设置共享模式锁定（给select加读锁）
SELECT ... LOCK IN SHARE MODE 改要等待本事务提交。如果其他事务已经修改了要读取的行但是没有提交，则本事务等待其他事务结束，并获取最新的值
查询索引锁定（给select加写锁）
SELECT ... FOR UPDATE
搜索到的索引记录，锁定行/索引条目，阻止其他事务更新这些行，设置共享模式锁定，或者读取行
Shared and Exclusive Locks 共享锁和排他锁
事务T1在row行上有共享锁
1、事务T2申请共享锁可立即获得，T1, T2同时拥有共享锁
2、事务T2申请排他锁需要等待T1释放
事务T1在row行上有排他锁
1、事务T2申请共享锁 or 排他锁，均需等待T1释放
Intention Locks 意图锁（表级锁）
innodb支持多粒度锁：即支持同时存在行锁和标锁。为了支持这个机制，有了意图锁
目的：表名事务之后需要在一给表的某行中需要共享 or 排他锁
意图共享锁IS：事务打算在一张表的个别行申请共享锁（或正拥有）（select ... for share)
意图排他锁IX：事务打算再一张表的个别行申请排他锁（或正拥有）（select ... for update)
-
事务需要申请共享行锁之前，先要申请表的意图共享锁或意图排他锁
-
是无需要申请排他航所之前，先要申请表的意图排他锁
| X | IX | S | IS | |
|---|---|---|---|---|
| X | 互斥 | 互斥 | 互斥 | 互斥 |
| IX | 互斥 | 兼容 | 互斥 | 兼容 |
| S | 互斥 | 互斥 | 兼容 | 兼容 |
| IS | 互斥 | 兼容 | 兼容 | 兼容 |
| （意图锁之间互相兼容，其余遵循X,S互斥原则） |
意图锁不会阻止除了完整表请求之外的任何（如何lock tables ... write)
某个事务上的某个意图锁SHOW ENGINE INNODB STATUS：
TABLE LOCK table test.t trx id 10080 lock mode IX
Record Locks 索引记录锁
SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE 给c1=10的行上排他锁,如果查询用的是聚集索引，就会使用此排他记录锁
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 ;;
Gap Locks 间隙锁
SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;
给10~20之间的行上排他间隙锁，阻止其他事务更新
间隙锁在部分隔离级别下使用
在唯一索引上的查询（不包括多个col的唯一索引，搜索条件为某些列），不使用间隙锁。例：SELECT * FROM child WHERE id = 100;只有记录锁，没有间隙锁。如果id没有索引或者不是唯一索引，会在100之前的所有行上间隙锁
注意：不同的事务可以在一个间隙上拥有不同的间隙锁。例如：事务A在一个间隙上拥有共享间隙锁，同时事务B在同一间隙上拥有排他间隙锁（无论是间隙S锁还是间隙X锁）。这个机制被允许存在是因为，一个记录被删除时，会和并不同事务在这个记录上的间隙锁
间隙锁唯一的作用就是阻止其他事务在间隙上的更改，一个事务在一个记录上有间隙锁不阻止其他事务在此间隙上获得间隙锁
在读提交隔离级别上，间隙锁不被使用，除非在进行外键约束检查和重复值检查时。在读提交隔离级别上，找不到行时，记录锁被释放。
Next-Key Locks
这个锁是索引数据上的记录锁和索引数据之前的间隙锁的结合
InnoDB以这样的方式执行行级锁定：当它搜索或扫描表索引时，它会在遇到的索引记录上设置共享锁或排它锁。因此，行级锁实际上是索引记录锁。
在索引记录上的next-key lock依然影响在索引记录之前的间隙锁。所以，next-key lock是一个索引记录锁+在这个索引数据之前的间隙锁。如果一个会话有记录R索引上的共享/排他锁，其他会话不可以插入一个新的索引记录在这个R索引之前
假设10，11，13，20上有索引，可能的next-key lock如下，最后一个间隔，next-key lock锁定最大索引值之后的间隙
(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)
innodb在可重复度级别下，使用next-key lock用于查找和索引扫描，防止幻读
查看锁状态
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 ;;
插入意图锁
插入意图锁在执行插入行数据之前的设置的一种间隙锁
这个锁表示，在想要插入的时候，多个事务插入共同的索引间隙不需要相互等待，若果他们的行在间隙中位置不同
假设，47上有索引记录锁，独立的事务想要插入5和6，每个事务在得到插入行上的排他锁之前，使用插入意图锁锁住47这个间隙。但是每个事务不阻塞彼此
举例：客户端A创建一张表有2个索引数据（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查看
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锁是一个特殊的表级锁，当事务插入表并有自增列的时候持有。举例，如果一个事务在插入数据，其他事务需要等待自己的插入，第一个事务将得到连续的主键值
例子
客户端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;
