InnoDB的锁和事务模型

一、InnoDB 锁

InnoDB中的锁类型：

• 共享/排他锁
• 意向锁
• 纪录锁
• 间隙锁
• 下一值锁
• 插入意向锁
• 自增锁
• 空间索引谓词锁

1.共享/排他锁

​ 如果一个事务T1对某一行（r）持有共享锁（S），则其他事务T2申请共享锁会被立即授予，这样T1和T2都持有r行的共享锁，如果T2申请排他锁（X）,则不会被立即授予。

​ 如果一个事务T1对某一行（r）持有排它锁（X），则其他事务T2申请任何锁都不会被立即授予。T2会等T1释放锁后。

2.意向锁

​ 意向锁时表级锁，由存储引擎去维护， 它不跟行锁冲突，意向锁直接也不冲突因为引擎级别的特殊表级概念。

​ 例如，事务T1对表上某一行（r）存在写排它锁，事务T2需要申请表级的写排它锁，会先确认该表是否存在表排它锁，确认没有之后会遍历该表所有行，确认是否存在行排它锁，当遍历到r行时，存在行排它锁，T2会进入等待，这种情况下效率很低，需要遍历所有行，于是引进了意向锁。

​ 当一个事务T1准备对 用户表进行 行级锁定操作，InooDB 会先获取该数据行所在的表的对应意向锁，进行意向锁锁定操作。这样事务B过来尝试对 用户表做表锁锁定操作时，申请锁资源时，只需要看下 用户表是否存在 意向锁锁定 ，如果有，直接去等待资源就行，这样就不需要遍历了（以免遍了N久才知道有冲突，还是需要等待）。

意向共享锁（IS）和意向排它锁（IX）。

innodb引擎 在知道我们对某个表数据准备使用 S （共享读锁）行级锁定时，就会去对这些行 所在的表，做 意向共享锁 (IS) 锁定操作。同样，innodb引擎 在知道我们对某个表数据准备使用X （排他写锁）行级锁定时，就会去对这些行 所在的表，做 意向排他锁（IX）锁定操作。

所以一个记录存在行锁，应该也存在与之对应的意向锁。

`mysql> set autocommit=off;`
`Query OK, 0 rows affected (0.00 sec)`

`mysql> select * from t1 where id=1 for share;`
`+----+------+`
`| id | name |`
`+----+------+`
`|  1 | aaa  |`
`+----+------+`
`mysql> select object_schema,object_name,lock_mode from performance_schema.data_locks where object_name='t1';`
`+---------------+-------------+---------------+`
`| object_schema | object_name | lock_mode     |`
`+---------------+-------------+---------------+`
`| test          | t1          | IS            |`
`| test          | t1          | S,REC_NOT_GAP |`
```+---------------+-------------+---------------+````

``mysql> commit;`
`Query OK, 0 rows affected (0.00 sec)`

`mysql> select object_schema,object_name,lock_mode from performance_schema.data_locks where object_name='t1';`
`Empty set (0.00 sec)`

3.记录锁

记录锁通常锁定的是索引记录

4.间隙锁

间隙锁是在索引记录之间的间隙上的锁，或者在第一个索引记录之前或最后一个索引记录之后的间隙上的锁，可能包含一个值、多个值或0个值。

5.下一值锁

next-key锁是索引记录上的记录锁和索引记录之前的间隙上的间隙锁的组合。

mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
|  2 | bbb  |
|  3 | ccc  |
|  4 | ddd  |
|  5 | eee  |
|  6 | fff  |
|  7 | ggg  |
|  8 | hhh  |
|  9 | iii  |
| 11 | kkk  |
+----+------+
10 rows in set (0.00 sec) 
会话A:
Query OK, 0 rows affected (0.00 sec)

mysql>  select id,name from t1 where id between 1 and 3 for update;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
|  2 | bbb  |
|  3 | ccc  |
+----+------+
3 rows in set (0.00 sec)
此时表t1上的锁：
mysql>  select object_schema,object_name,lock_data,lock_mode from performance_schema.data_locks where object_name='t1';
+---------------+-------------+-----------+---------------+
| object_schema | object_name | lock_data | lock_mode     |
+---------------+-------------+-----------+---------------+
| test          | t1          | NULL      | IX            |  
| test          | t1          | 1         | X,REC_NOT_GAP |
| test          | t1          | 2         | X             |
| test          | t1          | 3         | X             |
+---------------+-------------+-----------+---------------+
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql>  select id,name from t1 where id between 8 and 11 for update;
+----+------+
| id | name |
+----+------+
|  8 | hhh  |
|  9 | iii  |
| 11 | kkk  |
+----+------+
3 rows in set (0.00 sec)
mysql>  select object_schema,object_name,lock_data,lock_mode from performance_schema.data_locks where object_name='t1';
+---------------+-------------+-----------+---------------+
| object_schema | object_name | lock_data | lock_mode     |
+---------------+-------------+-----------+---------------+
| test          | t1          | NULL      | IX            |
| test          | t1          | 8         | X,REC_NOT_GAP |
| test          | t1          | 9         | X             |
| test          | t1          | 11        | X             |
+---------------+-------------+-----------+---------------+
4 rows in set (0.00 sec)

会话B:
mysql> insert into t1 values(10,'kkk');
此时插入10会被阻塞，超时后会报错
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

在超时报错前，查询锁情况：
mysql>  select ENGINE_TRANSACTION_ID,object_schema,object_name,lock_data,lock_mode from performance_schema.data_locks where object_name='t1';
+-----------------------+---------------+-------------+-----------+------------------------+
| ENGINE_TRANSACTION_ID | object_schema | object_name | lock_data | lock_mode              |
+-----------------------+---------------+-------------+-----------+------------------------+
|                441670 | test          | t1          | NULL      | IX                     |
|                441670 | test          | t1          | 11        | X,GAP,INSERT_INTENTION |
|                441667 | test          | t1          | NULL      | IX                     |
|                441667 | test          | t1          | 8         | X,REC_NOT_GAP          |
|                441667 | test          | t1          | 9         | X                      |
|                441667 | test          | t1          | 11        | X                      |
+-----------------------+---------------+-------------+-----------+------------------------+

6.插入意向锁

​ 插入意图锁是insert操作在行插入之前设置的一种间隙锁。这个锁以这样一种方式表示插入的意图，即插入到相同索引间隙中的多个事务如果不在间隙内的相同位置插入，则不需要彼此等待。假设存在值为4和7的索引记录。分别尝试插入值为5和6的事务，在获得插入行上的排他锁之前，每个事务都用插入意图锁锁住4和7之间的间隙，但不会相互阻塞，因为行不冲突。

7.自增锁

​ AUTO-INC锁是一种特殊的表级锁，用于在具有AUTO_INCREMENT列的表中插入事务。在最简单的情况下，如果一个事务正在向表中插入值，那么任何其他事务都必须等待对该表进行自己的插入，以便第一个事务插入的行接收连续的主键值。

8.空间索引的谓词锁

二、InnoDB事务模型

1.事务隔离级别

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

老生常谈，本文档只讨论READ COMMITTED和REPEATABLE READ。Innod默认为REPEATABLE READ。
我们可以在会话级别设置隔离级别

set session transaction isolation level repeatable read;

也可以在服务器级别设置，通过修改配置文件或环境变量

set global transaction isolation level repeatable read;

也可以在事务中修改隔离级别来灵活控制后续的语句的隔离级别

也可以在事务中修改隔离级别来灵活控制后续的语句的隔离级别

也可以在服务器级别设置，通过修改配置文件或环境变量
set global transaction isolation level repeatable read;
也可以在事务中修改隔离级别来灵活控制后续的语句的隔离级别

1）REPEATABLE READ
在一个事务内，任何时间点查询某一表的某一数据都是一致的，因为他读取的都是同一个快照，即事务开始前的快照。

DML的读取锁定取决于查询条件列是否是唯一索引列。

如果是唯一索引列，则值锁定唯一索引记录
如果不是唯一索引列，或者是范围查询，则使用间隙锁（gap lock）和next-key lock来锁定

2）READ COMMITTED
一个事务内，不同时间点查询某一表的某一数据可能是不一致的，因为他读取的都是最新的快照。

对于锁定读取，InnoDB只锁定索引记录，不锁定他们之间的间隙，因此可能出现幻象行，
read committed 仅支持基于行的二进制记录。

示例 1：
会话A：
 select * from t1 where id=1;
+------+------+
| id   | name |
+------+------+
|    1 | AAA |
+------+------+
select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
会话B：
 select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
会话C：
 select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+

会话A、B、C：
START TRANSACTION;
select * from t1 where id=1;
+------+------+
| id   | name |
+------+------+
|    1 | AAA  |
+------+------+
此时三个会话查询一致

会话A:
update t1 set name='aaa' where id=1;
commit;
select * from t1 where id=1;
+------+------+
| id   | name |
+------+------+
|    1 | aaa  |
+------+------+

会话B:
select * from t1 where id=1;
+------+------+
| id   | name |
+------+------+
|    1 | AAA  |
+------+------+

REPEATABLE-READ 隔离级别，读取的是会话开始前的快照。

会话C:
 select * from t1 where id=1;
+------+------+
| id   | name |
+------+------+
|    1 | aaa  |
+------+------+
READ-COMMITTED 隔离级别，读取的是最新的快照。

会话B:
 commit;
 select * from t1 where id=1;
+------+------+
| id   | name |
+------+------+
|    1 | aaa  |
+------+------+
commit使上个事务结束，读取最新的快照

示例2：
 select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
会话A:
CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB; 
INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2); 
COMMIT;
START TRANSACTION;
UPDATE t SET b = 5 WHERE b = 3;


会话B ：
 select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
UPDATE t SET b = 4 WHERE b = 2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction


会话A、B
set session transaction isolation level read committed;

会话A：
drop table t;
CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB; 
INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2); 
COMMIT;

会话A、B：

START TRANSACTION;


会话A:
UPDATE t SET b = 5 WHERE b = 3;

会话B：
UPDATE t SET b = 4 WHERE b = 2;

--此时会话B不会被阻塞

会话A:
 select * from t;
+---+------+
| a | b    |
+---+------+
| 1 |    2 |
| 2 |    5 |
| 3 |    2 |
| 4 |    5 |
| 5 |    2 |
+---+------+
会话B:

 select * from t;
+---+------+
| a | b    |
+---+------+
| 1 |    4 |
| 2 |    3 |
| 3 |    4 |
| 4 |    3 |
| 5 |    4 |
+---+------+

--这就是所谓的“半一致”读取（“semi-consistent” read）

示例3：
会话A:
CREATE TABLE t (a INT NOT NULL, b INT, c INT, INDEX (b)) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2,3),(2,2,4);
COMMIT;

START TRANSACTION;
UPDATE t SET b = 3 WHERE b = 2 AND c = 3;

会话B：
UPDATE t SET b = 4 WHERE b = 2 AND c = 4;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

--尽管两个会话查询的不是一行，但是由于访问共同的index记录，因此也会被阻塞

2.autocommit、rollback、commit

autocommit启用，（1或ON）,一个sql就是一个事务，如果该语句没有返回错误，则这个SQL执行完就提交。如果这个SQL返回错误，提交还是回滚取决于报错的性质。多语句的事务可以使用START TRANSACTION 或 BEGIN表示开始，以commit或rollback表示结束。

autocommit禁用，（0或OFF）。则会话始终是打开一个事务，commit和rollback表示上个事务的结束和新的事务的开始。如果在没有commit和rollback下关闭会话，则事务会回滚。

生产环境建议关闭autocommit。

3.一致的非锁定读取

一致性读取：InnoDB使用多版本，确保在某一时间点查询一个快照，这个查询只可以看到快照之前所有的已提交的事务更改，和同一事务中之前做的更改。

一致性读取对Select语句有效，但是对DML语句可能不起作用，

示例4：
会话A:
truncate table t1;
START TRANSACTION;
select count(*) from t1;
会话B：
insert into t1 values(1,'aaa');
commit;
会话A:
select count(*) from t1;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
delete from t1 where id=1;
Query OK, 1 row affected (0.00 sec)
--一致性读看不到其他会话插入的行，但是DML语句可以删除其他会话插入的行。

我们可以通过执行commit然后在进行查询来看到其他会话的更改，或者 START TRANSACTION WITH CONSISTENT SNAPSHOT。
这个叫做InnoDB的多版本并发控制

如果想看数据库的“最新”状态，可以使用read committed隔离级别或者锁定读取。

一致性读取对DDL无效
一致的读对DROP TABLE无效，因为MySQL不能使用已被删除 InnoDB 的表。
一致性读对ALTER TABLE ，因为alter table会构建原始表的临时副本，并删除原始表，然后临时副本称为更改后的新表。新表不存在之前的事务快照中，索引查询会返回如下错误：
ER_TABLE_DEF_CHANGED, “Table definition has changed, please retry transaction”.

4.锁定读取

SELECT … FOR SHARE
SELECT … FOR UPDATE

如果您希望在请求的行被锁定时立即返回查询，或者从结果集中排除锁定的行是可接受的，则无需等待释放行锁定。

NOWAIT

使用 NOWAIT 永不等待获取行锁定的 锁定读取 。 查询立即执行，如果请求的行被锁定则失败并显示错误。

SKIP LOCKED

使用 SKIP LOCKED 永不等待获取行锁定的 锁定读取 。 查询立即执行，从结果集中删除锁定的行。

示例5：
会话A
CREATE TABLE t (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;
INSERT INTO t (i) VALUES(1),(2),(3);
commit;
START TRANSACTION;
mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE;
+---+
| i |
+---+
| 2 |
+---+

会话B
START TRANSACTION;
mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE NOWAIT;
ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.

会话C:
START TRANSACTION;
mysql> SELECT * FROM t FOR UPDATE SKIP LOCKED;
+---+
| i |
+---+
| 1 |
| 3 |
+---+

三、InnoDB中不同语句的锁

• SELECT…FROM不会设置锁，除非隔离级别是SERIALIZABLE，如果是SERIALIZABLE,SELECT会设置共享的Next-key锁，对于使用唯一索引锁定行以搜索唯一行的语句，只需要索引记录锁定。
• SELECT … FOR UPDATE 和 SELECT … FOR SHARE 使用唯一索引的语句获取扫描行的锁，并释放不符合包含在结果集中的行的锁（例如，如果它们不符合 WHERE 子句中 给出的条件 ）。 但是，在某些情况下，行可能不会立即解锁，因为在查询执行期间结果行与其原始源之间的关系会丢失。 例如，在 UNION 从表中扫描（和锁定）的行可能会在评估它们是否符合结果集之前插入临时表中。 在这种情况下，临时表中的行与原始表中的行的关系将丢失，并且在查询执行结束之前不会解锁后面的行。
• 对于锁定读取的update和delete语句，所采用的锁取决于语句是使用唯一搜索条件的唯一索引还是范围类型搜索条件。对于具有唯一搜索条件的唯一索引，只锁定索引记录；对于其他搜索条件以及非唯一索引，使用间隙锁或Next-Key锁。
• Update…where 在查询遇到的每条记录上设置独占的Next-Key锁定，但是对于使用唯一索引锁定的行，只需要锁定索引记录。
• 当UPDATE修改一个聚集索引记录时，隐式锁会在受影响的二级索引记录上执行。在插入新的辅助索引记录之前执行重复检查扫描，以及在插入新的辅助索引记录时，UPDATE操作还在受影响的辅助索引记录上使用共享锁。
  delete…from…where……在搜索遇到的每条记录上设置独占的下一个键锁。但是，对于使用唯一索引锁定行以搜索唯一行的语句，只需要索引记录锁。
• insert在插入的行上设置排他锁。这个锁是索引记录锁，而不是下一个键锁(也就是说，没有间隙锁)，并且不阻止其他会话插入到插入行之前的间隙中。

在插入行之前，设置了一种称为插入意图间隙锁的间隙锁。该锁表示要以这样一种方式插入，即插入到同一索引间隙的多个事务如果不在间隙内的同一位置插入，则不需要相互等待。假设有值为4和7的索引记录。尝试插入值5和6的独立事务在获得插入行上的排它锁之前，每个事务都用插入意图锁锁定4和7之间的间隙，但不会阻塞彼此，因为行是不冲突的。

示例：

会话A：
CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;
START TRANSACTION;
INSERT INTO t1 VALUES(1);

会话B：
START TRANSACTION;  --此时会话B和C会被阻塞
INSERT INTO t1 VALUES(1);

会话C:
START TRANSACTION;
INSERT INTO t1 VALUES(1);

会话A:
rollback;

会话B:
mysql> INSERT INTO t1 VALUES(1);
Query OK, 1 row affected (17.91 sec)

会话C：
mysql> INSERT INTO t1 VALUES(1);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

四、幻读行

所谓的幻影问题(phantom problem)发生在同一个事务中，即同一个查询在不同时间产生不同的行集。例如，如果一个SELECT执行了两次，但是第二次返回的行第一次没有返回，那么这一行就是“幻影”行。

 示例1：
 select * from t1 where id=1;
 会话A和会话B:
 mysql>  select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

会话A:
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 where id=1;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
+----+------+
1 row in set (0.00 sec)
会话B:
mysql> update t1  set name='AAA' where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 where id=1;
+----+------+
| id | name |
+----+------+
|  1 | AAA  |
+----+------+
1 row in set (0.01 sec)

会话A:
mysql> select * from t1 where id=1;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
+----+------+
1 row in set (0.00 sec)
--没有产生幻读
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 where id=1;
+----+------+
| id | name |
+----+------+
|  1 | AAA  |
+----+------+
1 row in set (0.00 sec)
--提交后看到更新后数据

示例2：
会话A和B：
mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+
1 row in set (0.00 sec)

会话A：
mysql>  START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 where id=2;
+----+------+
| id | name |
+----+------+
|  2 | bbb  |
+----+------+
1 row in set (0.00 sec)
会话B：
mysql> select * from t1 where id=2;
+----+------+
| id | name |
+----+------+
|  2 | bbb  |
+----+------+
1 row in set (0.00 sec)

mysql> update t1 set name='BBB' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 where id=2;
+----+------+
| id | name |
+----+------+
|  2 | BBB  |
+----+------+
1 row in set (0.00 sec)

会话A：
mysql>  select * from t1 where id=2;
+----+------+
| id | name |
+----+------+
|  2 | bbb  |
+----+------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql>  select * from t1 where id=2;
+----+------+
| id | name |
+----+------+
|  2 | BBB  |
+----+------+
1 row in set (0.00 sec)

示例3：
会话A和B：
mysql> set session transaction isolation level READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED        |
+-------------------------+
1 row in set (0.00 sec)

会话A：
mysql>  START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql>  select * from t1 where id=3;
+----+------+
| id | name |
+----+------+
|  3 | ccc  |
+----+------+
1 row in set (0.00 sec)
会话B：
mysql>  START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> update t1 set name='CCC' where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t1 where id=3;
+----+------+
| id | name |
+----+------+
|  3 | CCC  |
+----+------+
1 row in set (0.00 sec)

会话A：
mysql> select * from t1 where id=3;
+----+------+
| id | name |
+----+------+
|  3 | ccc  |
+----+------+
1 row in set (0.00 sec)
--在会话B提交前，会话A看到name值仍为小写
会话B：
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

会话A：
mysql> select * from t1 where id=3;
+----+------+
| id | name |
+----+------+
|  3 | CCC  |
+----+------+
1 row in set (0.00 sec)
--会话B提交后，会话A看到了会话B提交后的值，会话A在同一事务内看到了两个不同的值，产生了幻读

总结：在 READ COMMITTED和REPEATABLE READ隔离级别，均不会产生幻读，READ COMMITTED 读的是最新快照，可能会产生幻读，innodb通过next-key锁来避免幻读；REPEATABLE READ读取的是事务开始前的快照，不会产生幻读；READ UNCOMMITTED会产生幻读。

五、死锁

示例：

会话A：

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 where id=4 for share;
+----+------+
| id | name |
+----+------+
|  4 | ddd  |
+----+------+
1 row in set (0.00 sec)

会话B：

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from t1 where id=4;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>  

这里发生死锁，因为客户端A需要一个X锁来删除行。但是，这个锁请求不能被授予，因为客户端B已经有一个对X锁的请求，并且正在等待客户端a释放它的S锁。A持有的S锁也不能因为B之前申请了X锁而升级为X锁。结果，InnoDB为其中一个客户端生成了一个错误并释放了锁。客户端返回此错误。

当启用死锁检测(默认)时，InnoDB自动检测事务死锁，并回滚一个或多个事务以打破死锁。InnoDB尝试选择小事务进行回滚，其中事务的大小由插入、更新或删除的行数决定。

如果innodb_table_locks = 1(默认值)和autocommit = 0, InnoDB会感知表锁，并且它上面的MySQL层知道行级锁。否则，当涉及到MySQL lock TABLES语句设置的表锁或者涉及到InnoDB以外的存储引擎设置的锁时，InnoDB无法检测到死锁。通过设置nnodb_lock_wait_timeout系统变量的值来解决这些情况

如果InnoDB监控器输出的最新检测到的死锁部分包含一条消息，“在LOCK TABLE waiting -for GRAPH中搜索太深或太长，我们将在事务之后回滚”，这表明等待列表上的事务数量已经达到限制200。超过200个事务的等待列表被视为死锁，试图检查等待列表的事务被回滚。如果锁线程必须查看等待链表上事务拥有的超过1,000,000个锁，也会发生相同的错误。

在高并发系统上，当多个线程等待同一个锁时，死锁检测可能会导致速度减慢。有时，禁用死锁检测并依赖Innodb_lock_wait_timeout死锁发生时事务回滚的超时时间。死锁检测可以通过innodb_deadlock_detect配置选项来禁用。

六、事务调度

InnoDB使用CATS (Contention-Aware Transaction Scheduling)算法来确定等待锁的事务的优先级。当多个事务等待同一个对象上的锁时，CATS算法确定哪个事务首先接收到锁。

CATS算法通过分配一个调度权重来确定等待事务的优先级，该权重是根据一个事务阻塞的事务数量计算的。例如，如果两个事务在等待同一个对象上的锁，那么阻塞最多事务的事务会被分配更大的调度权重。如果权重相等，则优先考虑等待时间最长的事务

七、Mysql8.0 查看锁

information_schema.innodb_trx

存储了当前正在执行的事务信息

字段解释：

trx_id：事务ID。

trx_state：事务状态，有以下几种状态：RUNNING、LOCK WAIT、ROLLING BACK 和 COMMITTING。

trx_started：事务开始时间。

trx_requested_lock_id：事务当前正在等待锁的标识，可以和 INNODB_LOCKS 表 JOIN 以得到更多详细信息。

trx_wait_started：事务开始等待的时间。

trx_mysql_thread_id：事务线程 ID，可以和 PROCESSLIST 表 JOIN。

trx_tables_locked：表示该事务目前加了多少个表级锁。

trx_lock_structs：表示该事务生成了多少个内存中的锁结构。

trx_lock_memory_bytes：事务锁住的内存大小，单位为 BYTES。

trx_rows_locked：表示该事务目前加了多少个行级锁。

performance_schema.data_locks

（8.0前的innodb_locks ）:记录锁信息

ENGINE
ENGINE_LOCK_ID
ENGINE_TRANSACTION_ID
THREAD_ID
EVENT_ID
OBJECT_SCHEMA
OBJECT_NAME
PARTITION_NAME
SUBPARTITION_NAME
INDEX_NAME
OBJECT_INSTANCE_BEGIN
LOCK_TYPE
LOCK_MODE
LOCK_STATUS
LOCK_DATA

performance_schema.data_lock_waits

(8.0前的infomation_schema.innodb_lock_waits)

ENGINE
REQUESTING_ENGINE_LOCK_ID
REQUESTING_ENGINE_TRANSACTION_ID
REQUESTING_THREAD_ID
REQUESTING_EVENT_ID
REQUESTING_OBJECT_INSTANCE_BEGIN
BLOCKING_ENGINE_LOCK_ID
BLOCKING_ENGINE_TRANSACTION_ID
BLOCKING_THREAD_ID
BLOCKING_EVENT_ID
BLOCKING_OBJECT_INSTANCE_BEGIN

processlist

id:标识ID。这与在SHOW PROCESSLIST语句的Id列、Performance Schema threads表的PROCESSLIST_ID列中显示的值类型相同，并由CONNECTION_ID()函数返回

user:发出该语句的mysql用户。

host：发出该语句的客户机的主机名(系统用户除外，没有主机)。

db：默认数据库。

command：线程正在执行的命令的类型。

time：线程处于当前状态的时间(以秒为单位)。

state：指示线程正在执行的操作、事件或状态。

info：线程正在执行的语句，如果没有执行任何语句，则为NULL。

相关语句：

1）查看锁等待相关的（阻塞线程、被阻塞线程信息及相关用户、IP、PORT、locked_type锁类型）
SELECT locked_table,
locked_index,
locked_type,
blocking_pid,
concat(T2.USER,‘@’,T2.HOST) AS “blocking(user@ip:port)”,
blocking_lock_mode,
blocking_trx_rows_modified,
waiting_pid,
concat(T3.USER,‘@’,T3.HOST) AS “waiting(user@ip:port)”,
waiting_lock_mode,
waiting_trx_rows_modified,
wait_age_secs,
waiting_query
FROM sys.x$innodb_lock_waits T1
LEFT JOIN INFORMATION_SCHEMA.processlist T2 ON T1.blocking_pid=T2.ID
LEFT JOIN INFORMATION_SCHEMA.processlist T3 ON T3.ID=T1.waiting_pid;

2）等待的持续时间（单位秒>20s）
SELECT trx_mysql_thread_id AS PROCESSLIST_ID,
NOW(),
TRX_STARTED,
TO_SECONDS(now())-TO_SECONDS(trx_started) AS TRX_LAST_TIME ,
USER,
HOST,
DB,
TRX_QUERY
FROM INFORMATION_SCHEMA.INNODB_TRX trx
JOIN sys.innodb_lock_waits lw ON trx.trx_mysql_thread_id=lw.waiting_pid
JOIN INFORMATION_SCHEMA.processlist pcl ON trx.trx_mysql_thread_id=pcl.id
WHERE trx_mysql_thread_id != connection_id()
AND TO_SECONDS(now())-TO_SECONDS(trx_started) >= 20 ;

参考：

https://www.modb.pro/db/163574

MySQL 8.0 Reference Manual -15.7 InnoDB Locking and Transaction Model