这篇文章主要针对的是关系型数据库（MySQL以及InnoDB引擎）而去讨论的数据一致性，锁以及MVCC机制，和隔离等级等相关的话题。之前写过一篇文章针对基础概念（理解数据库的事务，ACID，CAP和一致性），而本篇主要是通过MySQL和InnoDB的具体实现来更加细致地阐释这些话题。
本篇不涉及分布式系统的一致性问题，而主要关注的是事务的一致性问题。
数据一致性问题
要理解数据的一致性，先要明白在哪些情况可能会导致数据不一致。只有当并发操作发生时，才会导致数据不一致的情况，而这包含以下几种情况：
| 不一致场景 | Transaction A | Transaction B |
|---|---|---|
| 幻读(跟记录条数有关) | select xx from table where x>3; 假设选取了10条记录 | |
| insert 1条记录 x = 100； | ||
| select xx from table where x>3; 选出了11条记录(同样条件) | ||
| 结果 | 同样条件，第一次读的时候是10条记录，第二次读的时候是11条记录 | |
| 不可重复读(跟多次读有关) | read x=10 | |
| update x=12 | ||
| read x=12 | ||
| 结果 | 读同一个数据第一次是10，第二次是12 | |
| 脏读(读取了脏数据) | update x = 20 | |
| read x=20 | ||
| rollback | ||
| 结果 | 读取了脏数据20，实际并没有成功更新到20 | |
| 修改丢失之提交覆盖(跟同时修改有关) | read x=10 | read x=10 |
| update x= x+1 (即x=11) | ||
| update x= x+2 (即x=12) | ||
| 结果 | 逻辑上应该是x=13，但实际是x=12，Transaction A的修改丢失 | |
| 修改丢失之回滚覆盖(跟回滚有关) | read x=10 | read x=10 |
| update x= x+1 (即x=11) | ||
| rollback | ||
| 结果 | 逻辑上应该是x=11，但实际是x=10，Transaction A的修改丢失 |
解决数据不一致的问题，数据库一般是通过锁或者MVCC机制来实现的。接下来我们先来了解InnoDB中各种类型的锁以及MVCC机制：
锁
共享锁和排他锁(Shared and Exclusive Locks)
S锁和X锁是用于为数据库表中特定的行（row）设定锁，即锁的对象是行数据。
| 锁 | 描述 |
|---|---|
| S锁(共享锁) | 当一个事务添加了S锁后，其它事务可以添加S锁，但是无法添加X锁 |
| X锁(排它锁) | 当某一事务添加了X锁后，其它事务既不能加X锁，也不能加S锁 |
当锁冲突的时候，必须等待前一个事务释放相冲突的锁。因此这种锁机制必然影响并发性能，因此当使用RC和RR隔离等级时，InnoDB采用了MVCC机制来提升并发性能。
锁的互斥情况
| 锁 | S锁 | X锁 |
|---|---|---|
| S锁 | 不冲突 | 冲突 |
| X锁 | 冲突 | 冲突 |
意图锁(intention lock)
- Intention Lock，即IS和IX锁，翻译为意图锁，实际是表明后续的意图，比如加了IS锁，表明后续有读操作的意图，后续要加S锁。意图锁是表级锁。
示例:# 加IS锁, 行级S锁 SELECT ... FOR SHARE # 加IX锁，行级X锁 SELECT ... FOR UPDATE
事实上意图锁不会和行级的S和X锁产生冲突，只会和表级的S和X锁产生冲突。因此，通常情况下，数据库查询，更新，插入，删除 record都不会因为意图锁而产生冲突(IS和IX互不冲突)，只会与表级共享锁或表级独占锁冲突。表级共享锁与独占锁如下方的示例。而对于表的更改删除操作实际使用的是MDL写锁，而不是普通的表级独占锁，这个要注意。
//表级别的共享锁，也就是读锁；
lock tables t_student read;
//表级别的独占锁，也就是写锁；
lock tables t_stuent write;
- Transaction中，如果程序要获取S锁来读数据，那么首先要获取IS锁或更强的IX锁
- Transaction中，如果程序要获取X锁来写数据，那么首先要获取IX锁
记录锁(record lock)
记录锁用于锁定索引记录，举例而言下面这条语句会锁定index上t=1的记录, 这样任何对t=1的update, insert和delete操作都无法执行（需要注意的是insert t=1的记录也无法执行）
```
# 本条语句对t=1这条记录加了X锁
select * from tablename where t=1 for update;
```
记录锁始终锁定索引记录，即使定义的表没有索引。在这种情况下，InnoDB 创建一个隐藏的聚簇索引并使用这个索引用于记录锁。
间隙锁(gap lock)
用于针对特定范围的索引进行加锁时，就需要用到间隙锁，即针对索引记录的间隙进行加锁，从而防止特定的范围内的插入操作，以防止幻读现象的发生。举个例子(注意：本例需在RR隔离等级)：
select * from tablename where t between 10 and 20 for update
这种情况下，在t index的10与20之间就加入了间隙锁，这时是无法插入t的值在10和20之间的记录的，例如无法插入t=16的记录。
# 这种情况是否加间隙锁要看t是否有index，且是unique类
# 型的index，如果是unique类型的index，则无需加gap lock，因为只需要index record lock即可
# 否则就会对index 10之前和之后的index间隙加gap lock，以防止insert一个t同样为10的记录
# 所谓间隙就是指两个index record之间的缝隙，比如此例中假设index 10 之前的record为5，
# 之后的record 为12， 那么5~10之间就会有一个间隙，10~12也是一个间隙，
# gap lock就会锁定这个index范围，从而无法插入诸如6,7,8,9,10或者11等值(注意5和12是否可以要视情况而定，见下面详解）。
select * from tablename where t=10 for update;
- 间隙锁是以右边界值为lock data的，意思是如间隙锁20，意味着其右边界值为20
- 间隙锁之间不会互斥，它的作用仅仅在于防止插入新的数据，比如当某个间隙已经加了gap S锁的情况下，仍然可以加gap X锁，只要不是插入新数据，就不会冲突。
- 间隙锁主要在RR隔离等级下会起作用，其主要是为了解决幻读现象，而在RC隔离等级下，间隙锁将只会用于外键约束检查和Duplicate Key的检查。
Next-key lock
什么是next-key lock? 以下引用自MySQL官网
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是一种组合，组合了inex record lock和index gap lock。next-key lock是在RR隔离等级下为解决幻读而存在的一种锁的组合。至于这个gap到底如何锁定则需要视具体情况而定。详细可以参考MySQL是怎么加锁的
-- 当where查询使用unique索引时，且查询条件限定特定单一record，如下
# field a 已经加了unique索引
select * from testnk where a=5 for update;
这时，next-key只对index a=5这条记录加锁(IX和X锁)，而没有间隙锁。这时只要插入的记录其a值不等于5，就可以插入
-- 当where查询使用unique索引时，且查询条件限定一个范围时，如下：
# field a 已经加了unique索引
select * from testnk where a<8 for update;
这时，next-key lock会对a < 8这个特定范围加锁，当插入a的值为6时，则无法插入，但是如果插入的a值为9，则没有问题。注意实际加锁情况还跟表中存在的索引记录有关，假如在表小于8的记录只有一个如a=5, 而大于8的最近的是a=9的记录，且不存在a=8的记录，那么就会针对index_a有两个锁，一个是(5, 9)的间隙锁，另外一个是(-∞, 5]的next-key锁。
-- 当where查询使用普通索引时(非unique), 且查询条件为特定单一record
# field b 为普通索引, int类型
# 假定已经有b=3，和b=8的记录
select * from testnk where b=5 for update
这时next-key lock会退化成间隙锁，理论上会对index_b的(3, 8)这个范围加锁。
这时会比较复杂，会分两种情况：
1.b=5不存在，那么只有两把锁，一把IX锁，另外一把是在index_b索引上的(3, 8)间隙锁
-
b=5存在，那么就会有4把锁，一把是IX锁，一把index_b索引上的(3, 5]next-key锁，一把index_b上的(5, 8)间隙锁，另外一把primary key索引上的对应b=5所对应的记录的记录锁。
另外，3和8两个边界值是否可以插入也分两种情况而定，如下图所示：
间隙锁边界的两种情况
- 如果记录落在在(3, 8)间隙锁范围里，则无法插入；比如图中记录<3, 6>，这里意为b=3, 而6是代表primary key id=6。由于二级索引的叶子节点排列时就是要按照<3,5><3,6>这样按顺序插入的，因此<3, 6>记录会插入到<3,5>后面，因此就落入了间隙锁的范围，因此是无法插入的，同理<8, 9>记录也无法插入。
- 如果记录落在间隙锁范围外则可以插入，如图中记录<3,4>或者<8,12>是可以插入的。
-- 当where查询使用普通索引时(非unique),且查询条件限定一个范围时，如下：
# 假定已经有b=3，和b=8的记录
select * from testnk where b < 5 for update;
这时会锁定b < 5这个特定范围， 实际会有两个锁：一个是(3, 8)的间隙锁；另外一个是(-∞, 3]这个间隙锁。
-- 当where查询的field没有索引的时候，不论查询条件怎样，都会lock整个表，这对于线上服务来说就是个灾难，导致并发性能会严重下降，所以务必注意查询条件的field是否加了索引。
插入意图锁(Insert Intention Lock)
An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion.
可以看出插入意图锁是一种间隙锁，用于表明插入新数据的意图。官方有个例子，假设我们数据库中已有数据4和7, 现在要在两个不同的事务中分别插入5和6，这时两个事务都会获取到4和7之间的插入意图锁，并在随后分别获得排他记录锁，分别锁定两条不同的行记录，且都能插入成功。
同其它意图一样，在插入数据时，插入意图锁并不会互相冲突，一般都会正常获取到该锁，但是由于insert需要X锁，这就需要看是否与其它锁冲突。
自增长锁(AUTO-INC Locks)
An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns. In the simplest case, if one transaction is inserting values into the table, any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values.
自增长锁主要用于带有AUTO_INCREMENT的列，这个锁主要用于保证自增长列的id的唯一性和增长性。MySQL 5.1中引入innodb_autoinc_lock_mode配置参数可以配置该锁的不同模式，有三种模式：
- innodb_autoinc_lock_mode=0，这时为传统模式，即会将整个表加锁，同一时间只能有一个事务插入数据，且在插入语句完成后解锁(而不是事务完成)
- innodb_autoinc_lock_mode=1, 默认模式，当能够预先知道插入的条数时，则为"simple insert"，这种情况下，只会在分配id的过程时有锁定，保证分配id的唯一性和自增长性，其它时候（包括插入的时候都不需要锁表）；而当无法预先知道要插入的条数时，则为"bulk insert"，这时还是会按照传统模式来锁表，直到insert语句执行完毕。这种模式会在相当程度上改善并发性。
- innodb_autoinc_lock_mode=2，轻量模式，任何情况下都不会加
AUTO_INC表级锁，并发性最好，但是并不安全。主要是在主备复制过程中，如果binlog是statement格式，可能会发生主备不一致的情况，因此，如果要使用此模式，最好将binlog的格式设为ROW格式
Predicate Locks for Spatial Indexes
用于支持SPATIAL Index的锁机制，具体参考官网，不再详述。
MDL锁
MDL锁中文是元数据锁，也分为MDL读锁和MDL写锁，主要用于以下场景：
- MDL读锁：对表进行CRUD操作时自动加MDL读锁
- MDL写锁：对表结构进行更改需要加MDL写锁
MDL 是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做了变更。也就是说MDL读锁和写锁是互斥的，当对表进行CRUD操作时自动加MDL读锁，这时如果要对表结构进行更改，就需要申请MDL写锁，但由于已经有了MDL读锁，所以就无法获取到MDL写锁，也就无法变更表结构。反之亦然。
需要注意的是，申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现 MDL 写锁等待，会阻塞后续该表的所有 CRUD 操作。所以当有长事务占用MDL读锁，而此时又有更改表结构的操作，那么这时由于要获取MDL写锁，所以就会被阻塞，阻塞后，后续的读操作也会由于获取MDL读锁（且排在MDL写锁的后边）而被阻塞，这就导致大量线程都被阻塞在这，从而导致数据库崩溃。所以，在更改表结构之前，一定要确定是否有长事务操作。
所以为了能安全的对表结构进行变更，在对表结构变更前，先要看看数据库中的长事务，是否有事务已经对表加上了 MDL 读锁，如果可以考虑 kill 掉这个长事务，然后再做表结构的变更。
多版本并发控制MVCC(Multi-Version Concurrent Control)
如果完全使用锁机制，则会导致读写操作无法并发执行，这在实际应用的时候会导致数据库的并发性能受到很大影响，因此InnoDB引入了MVCC机制，使得读写操作可以并发执行，从而大大提高了数据库性能。
其具体的实现是通过每行记录的三个隐藏的字段(分别是隐藏id，事务id，回滚指针)以及undo log来实现的。具体可以参考MySQL数据库事务各隔离级别加锁情况--read committed && MVCC。
简而言之，当事务要针对一条记录进行写操作时，会先用X锁锁定该行，然后将该记录copy到undo log当中，并通过回滚指针指向这条记录，用于回滚恢复时使用。这时，其它事务可以通过读取undo log中的备份数据来实现读操作，从而实现读写并发。
MVCC只在InnoDB的RC和RR隔离等级下才能够使用。因为在Read Uncommitted这种隔离等级下，读操作总是读取记录的最新数据，而不会去读undo log中的备份数据；而如果是串行模式，读操作不会发生冲突，因此也总是读取的最新的记录。
而在RC和RR模式下，MVCC也稍有不同。
首先，在MySQL中分为了两种读取方式：
- consistent non-locking reading
- locking read
Consistent Non-Locking Read
当我们通常使用select ... from ...where ...的时候，我们使用的就是consistent non-locking read，也称为快照读（snapshot read），即数据库会从快照中来获取数据。对于RR level来说，对于同一个transaction，其总是获取第一次查询时的快照（readview不变，具体细节请参考MVCC原理的相关文章），因此在整个事务期间其数据始终保持一致，从而避免了不可重复这种不一致问题。而在RC level下，每一次读数据（即每次select）都会获取当时最新的快照，因此其数据可能会变化。
Locking Read
当我们使用select ... for share 或者select ... for update的时候，我们就是在使用locking read，因为我们需要获取IS、S锁或者IX、X锁。这种情况我们不会去读取快照，而是直接读取最新的行数据，由于总是读取当前的行数据，因此也叫“当前读”。在使用locking read时，RC和RR也是不同的，比如RR支持Gap Lock和Next-Key Lock，而RC只会在外键约束检查和duplicate key约束检查时才会使用gap lock（详见gap lock和next-key lock）。
隔离等级
而要解决数据不一致的问题，就需要通过设定不同的隔离等级来进行规避。标准的隔离等级表如下（注意，InnoDB在实现时会有不同，具体见注解）：
| 隔离等级 | 原理描述 | 回滚覆盖 | 解决脏读 | 解决不可重复读 | 提交覆盖 | 解决幻读 |
|---|---|---|---|---|---|---|
| RU(Read Uncommitted) | 读的时候不加锁，写的瞬间加X锁，直到事务结束释放，因此我们可以读取到未提交的数据，因此叫Read Uncommitted | Yes | No | No | No | No |
| RC(Read Committed) | 在读的一瞬间加S锁，读完之后释放S锁；在写的一瞬间加X锁，直到事务结束，所以只能读到committed之后的数据，所以叫read committed | Yes | Yes | No | No | No |
| RR(Repeatable Read) | 在读的时候加持续S锁，在写的时候加持续X锁，因此在Transaction过程中保证读取的数据始终一致，也就避免不可重复读问题 | Yes | Yes | Yes | No | No |
| Serialization | 最高隔离等级，通过表级锁，使得每个数据库操作都串行化，无法并行操作数据库，这样完全避免了数据不一致的问题，但由于无法并行操作数据库，导致性能受到极大的限制，因此只能用于特定的场景 | Yes | Yes | Yes | Yes | Yes |
-
InnoDB在具体实现不同隔离等级的时候，会与标准稍有不同，需要加以区分。比如，在RR模式下，标准是要求读数据的时候加持续S锁，但是InnoDB具体实现的时候，要分不同的情况：
# 当数据没有被其它事务加锁时，当前读；当有被其它事务加锁时，直接快照读。可能出现提交覆盖 select * from tableA where x=5; # 加持续S锁，当前读。可能出现提交覆盖 select * from tableA where x=5 lock in share mode; # MySQL 5.7 select * form tableA where x=5 for share; # MySQL 8.0 # 加持续X锁，当前读。不会出现提交覆盖 select * from tableA where x=5 for update;这主要是由于InnoDB支持MVCC机制，普通的读取（Non-Locking Reads)是通过MVCC机制来读取特定的快照（snapshot，即undo log中的数据）从而使得可以在不使用锁的情况下获取到数据，且在事务过程中保持一致性。另外，当使用Locking Reads的时候（即使用select ... for share， select...for update), 就还是使用锁机制。
在标准的隔离等级中，RR隔离等级是不能解决幻读问题的，但是InnoDB却通过gap lock和next-key lock解决了幻读问题
标准的隔离等级中，是通过锁来实现RC和RR等级的，而在InnoDB中，不仅仅有锁机制，还有MVCC机制，是两种机制结合在一起来实现RC和RR等级的。同时，由于引入了MVCC机制，使得其并发性更好。(在使用select ... for share 或 select ... for update时依然会使用锁，而且都是在读的瞬间加锁，一直到事务结束后才会释放锁，这就是所谓的加锁读也叫当前读）
在RC模式下，当前读加锁时只加记录锁，不加间隙锁，而在RR模式下会使用next-key锁，因此会加间隙锁，从而解决了幻读现象。
MySQL实操
- 查看和设置隔离等级
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.01 sec)
这里要注意的是在 MySQL 中变量一般分为两类：用户变量 和 系统变量，用户变量的变量名格式为 @variable，而系统变量的格式为 @@variable，tx_isolation 是系统变量，所以变量名为 @@tx_isolation。其中，系统变量又可以分为 全局变量 和 会话变量，默认情况下使用 select @@variable 查询出来的是会话变量的值，也可以写作 select @@session.variable 或者 select @@local.variable，如果要查询全局变量的值，则使用 select @@global.variable。 -- <<解决死锁之路 - 学习事务与隔离级别>>
# 设置下一个事务的隔离等级
mysql> set transaction isolation level read committed;
Query OK, 0 rows affected (0.01 sec)
# 此时，去查当前session的隔离等级，仍然是repeatable-read
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)
# 要想修改session的隔离等级或者global的隔离等级，可以如下操作
mysql> set @@session.tx_isolation='READ-COMMITTED';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set, 1 warning (0.00 sec)
mysql> set @@global.tx_isolation='READ-COMMITTED';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-COMMITTED |
+-----------------------+
1 row in set, 1 warning (0.00 sec)
- 查看锁的状态
# 查看表锁的情况
mysql> show status like 'table_locks%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 90 |
| Table_locks_waited | 0 |
+-----------------------+-------+
2 rows in set (0.00 sec)
# 查看record lock的状态，都是0表明很健康
mysql> show status like 'InnoDB_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
+-------------------------------+-------+
5 rows in set (0.00 sec)
另外，我们可以通过show engine innodb status来查看锁的状态，但是在这之前我们需要先创建一个表，如下：
# 在任意db中创建该表，创建该表就会打开lock monitor
mysql> CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;
Query OK, 0 rows affected (0.16 sec)
另外，我们可以通过查询INFORMATION_SCHEMA中的innodb_locks, innodb_lock_waits和innodb_trx三张表来查询正在等待的锁的状态, 关于这三张表的说明如下所示：
# 引用自《mysql 查看死锁和去除死锁》
root@127.0.0.1 : information_schema 13:28:38> desc innodb_locks;
+————-+———————+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+————-+———————+——+—–+———+——-+
| lock_id | varchar(81) | NO | | | |#锁ID
| lock_trx_id | varchar(18) | NO | | | |#拥有锁的事务ID
| lock_mode | varchar(32) | NO | | | |#锁模式
| lock_type | varchar(32) | NO | | | |#锁类型
| lock_table | varchar(1024) | NO | | | |#被锁的表
| lock_index | varchar(1024) | YES | | NULL | |#被锁的索引
| lock_space | bigint(21) unsigned | YES | | NULL | |#被锁的表空间号
| lock_page | bigint(21) unsigned | YES | | NULL | |#被锁的页号
| lock_rec | bigint(21) unsigned | YES | | NULL | |#被锁的记录号
| lock_data | varchar(8192) | YES | | NULL | |#被锁的数据
+————-+———————+——+—–+———+——-+
rows in set (0.00 sec)
root@127.0.0.1 : information_schema 13:28:56> desc innodb_lock_waits;
+——————-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——————-+————-+——+—–+———+——-+
| requesting_trx_id | varchar(18) | NO | | | |#请求锁的事务ID
| requested_lock_id | varchar(81) | NO | | | |#请求锁的锁ID
| blocking_trx_id | varchar(18) | NO | | | |#当前拥有锁的事务ID
| blocking_lock_id | varchar(81) | NO | | | |#当前拥有锁的锁ID
+——————-+————-+——+—–+———+——-+
rows in set (0.00 sec)
root@127.0.0.1 : information_schema 13:29:05> desc innodb_trx ;
+—————————-+———————+——+—–+———————+——-+
| Field | Type | Null | Key | Default | Extra |
+—————————-+———————+——+—–+———————+——-+
| trx_id | varchar(18) | NO | | | |#事务ID
| trx_state | varchar(13) | NO | | | |#事务状态：
| trx_started | datetime | NO | | 0000-00-00 00:00:00 | |#事务开始时间；
| trx_requested_lock_id | varchar(81) | YES | | NULL | |#innodb_locks.lock_id
| trx_wait_started | datetime | YES | | NULL | |#事务开始等待的时间
| trx_weight | bigint(21) unsigned | NO | | 0 | |#
| trx_mysql_thread_id | bigint(21) unsigned | NO | | 0 | |#事务线程ID
| trx_query | varchar(1024) | YES | | NULL | |#具体SQL语句
| trx_operation_state | varchar(64) | YES | | NULL | |#事务当前操作状态
| trx_tables_in_use | bigint(21) unsigned | NO | | 0 | |#事务中有多少个表被使用
| trx_tables_locked | bigint(21) unsigned | NO | | 0 | |#事务拥有多少个锁
| trx_lock_structs | bigint(21) unsigned | NO | | 0 | |#
| trx_lock_memory_bytes | bigint(21) unsigned | NO | | 0 | |#事务锁住的内存大小（B）
| trx_rows_locked | bigint(21) unsigned | NO | | 0 | |#事务锁住的行数
| trx_rows_modified | bigint(21) unsigned | NO | | 0 | |#事务更改的行数
| trx_concurrency_tickets | bigint(21) unsigned | NO | | 0 | |#事务并发票数
| trx_isolation_level | varchar(16) | NO | | | |#事务隔离级别
| trx_unique_checks | int(1) | NO | | 0 | |#是否唯一性检查
| trx_foreign_key_checks | int(1) | NO | | 0 | |#是否外键检查
| trx_last_foreign_key_error | varchar(256) | YES | | NULL | |#最后的外键错误
| trx_adaptive_hash_latched | int(1) | NO | | 0 | |#
| trx_adaptive_hash_timeout | bigint(21) unsigned | NO | | 0 | |#
+—————————-+———————+——+—–+———————+——-+
rows in set (0.01 sec)
正常没有锁等待的情况下，查询这三张表是空的，如下：
mysql> select * from information_schema.innodb_locks;
Empty set, 1 warning (0.02 sec)
mysql> select * from information_schema.innodb_lock_waits;
Empty set, 1 warning (0.00 sec)
mysql> select * from information_schema.innodb_trx;
Empty set (0.00 sec)
下面我们做个试验，让两个事务交互，形成锁等待，如下：
# 事务1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
# 此条语句会获得X锁，且会获得next-key锁，锁定b>5的范围
mysql> select * from testnkb where b > 5 for update;
+---+
| b |
+---+
| 8 |
| 8 |
+---+
2 rows in set (0.01 sec)
然后事务2：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.01 sec)
# 此条语句会导致锁等待，等待事务1的锁释放
mysql> update testnkb set b=10 where b=8;
此时，我们再去通过information_schema中的三个表去查询
mysql> select * from information_schema.innodb_trx\G;
*************************** 1. row ***************************
trx_id: 7120 # transaction id
trx_state: LOCK WAIT
trx_started: 2019-07-26 20:56:43
trx_requested_lock_id: 7120:184:4:4 # 在等待的锁的id
trx_wait_started: 2019-07-26 20:56:43
trx_weight: 2
trx_mysql_thread_id: 4407
trx_query: update testnkb set b=10 where b=8 # 实际等待中的语句
trx_operation_state: starting index read
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 1
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
trx_id: 7119
trx_state: RUNNING
trx_started: 2019-07-26 20:55:45
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 3
trx_mysql_thread_id: 4364
trx_query: select * from information_schema.innodb_trx
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 3
trx_lock_memory_bytes: 1136
trx_rows_locked: 5
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)
ERROR:
No query specified
mysql> select * from information_schema.innodb_lock_waits\G;
*************************** 1. row ***************************
requesting_trx_id: 7120 # 正在等待的Transaction id
requested_lock_id: 7120:184:4:4
blocking_trx_id: 7119 # 正在占用锁的Transaction id
blocking_lock_id: 7119:184:4:4
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
mysql> select * from information_schema.innodb_locks\G;
*************************** 1. row ***************************
lock_id: 7120:184:4:4
lock_trx_id: 7120
lock_mode: X # Next-Key锁
lock_type: RECORD # 行级锁
lock_table: `test`.`testnkb`
lock_index: b
lock_space: 184
lock_page: 4
lock_rec: 4
lock_data: 8, 0x000000000206
*************************** 2. row ***************************
lock_id: 7119:184:4:4
lock_trx_id: 7119
lock_mode: X
lock_type: RECORD
lock_table: `test`.`testnkb`
lock_index: b
lock_space: 184
lock_page: 4
lock_rec: 4
lock_data: 8, 0x000000000206
2 rows in set, 1 warning (0.00 sec)
ERROR:
No query specified
图中 LOCK_TYPE 中的 RECORD 表示行级锁，而不是记录锁的意思：
如果 LOCK_MODE 为 X，说明是 next-key 锁；
如果 LOCK_MODE 为 X, REC_NOT_GAP，说明是记录锁；
如果 LOCK_MODE 为 X, GAP，说明是间隙锁；
另外，我们可以通过show engine innodb status看到更多信息，如下：
# 截取Transaction部分
------------
TRANSACTIONS
------------
Trx id counter 7121
Purge done for trx's n:o < 7066 undo n:o < 0 state: running but idle
History list length 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 7120, ACTIVE 80 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 4407, OS thread handle 123145393938432, query id 22951 localhost root Searching rows for update
update testnkb set b=10 where b=8
------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
# 这里表明Transaction 7120正在等待X锁
RECORD LOCKS space id 184 page no 4 n bits 72 index b of table `test`.`testnkb` trx id 7120 lock_mode X waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000008; asc ;;
1: len 6; hex 000000000206; asc ;;
------------------
---TRANSACTION 7119, ACTIVE 138 sec
# 3个锁，应该是IX锁，(5, 8]next-key锁，以及(8, +∞]next-key锁
3 lock struct(s), heap size 1136, 5 row lock(s)
# 被4364这个thread占用锁，通过show processlist也能看到4364这个线程在占用
MySQL thread id 4364, OS thread handle 123145395331072, query id 22952 localhost root starting
show engine innodb status
我们也可以通过show processlist;来查看运行中的事务
mysql> show processlist;
+------+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+-----------+------+---------+------+----------+------------------+
| 4364 | root | localhost | test | Sleep | 7 | | NULL |
| 4407 | root | localhost | test | Query | 0 | starting | show processlist |
+------+------+-----------+------+---------+------+----------+------------------+
2 rows in set (0.00 sec)
正常情况下会在info中显示SQL语句，但是由于我们在事务1中执行的select... for update;语句已经执行完毕，所以这里显示为null。从此也可以分析出某个事务一直没有提交，但是也没有执行SQL语句，可能是在执行事务过程中出现问题导致一直无法提交。
- 查看Auto-inc锁的模式
mysql> show variables like 'innodb_autoinc_lock_mode';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1 |
+--------------------------+-------+
1 row in set (0.00 sec)
