一 幻读

   关于幻读，在网上可以搜到很多种解释。这里我们还是先看mysql官方文档中给出的定义：

The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row.

   幻读问题，是指在一个事务中，相同的查询在不同的时间得到了不同的（结果）集合。例如，如果一个SELECT查询语句执行两次，但第二次查询返回的一条记录不在第一次查询的返回中，这行就是一个“幻”行。

   假设有一张child表，id列上建立了索引，你想要查询并锁住所有id大于100的行，并试图在稍后更新其中的某一列，查询sql如下：

SELECT * FROM child WHERE id > 100 FOR UPDATE;

   这个查询会从符合id大于100的第一记录开始扫描索引。假设表包含id值为90和102的行，如果在扫描范围内的索引记录上设置的锁没有锁定在间隙中所做的插入（在本例中是90和102之间的间隙），则另一个会话可以在表中插入id为101的新行。如果要在同一事务中执行相同的SELECT，则在查询返回的结果集中会看到一个id为101的新行（“幻影”）。如果我们将一组行视为一个数据项，那么新的幻像child行将违反事务的隔离原则，即事务在运行时，它读取的数据在事务期间不会更改。

   为了避免幻读，InnoDB使用名为临键（next-key）锁的算法，融合了index-row锁 和 间隙（gap）锁。InnoDB执行行级锁定的方式是，当它搜索或扫描表索引时，它会对遇到的索引记录设置共享或排他锁。因此，行级锁事实上是索引记录所（index-record锁）。另外，索引记录上的next-key锁也会影响该索引记录之前的“间隙”。也就是说，一个next-key锁是索引记录锁加上索引记录前面的间隙上的间隙锁。当一个会话A有记录R索引上的共享锁或排它锁，另一个会话不能在紧靠索引顺序的R之前的间隙中插入新的索引记录（需要等会话A释放锁）。

   当InnoDB扫描索引时，可能也会锁住索引中最后一条记录之后的间隙。就发生在前面的例子中：为了防止在id大于100的表中插入任何内容，InnoDB设置的锁在id值102之后的间隙上包含一个锁。

   可以使用next-key锁在应用程序中实现唯一性检查：如果您在共享模式下读取数据，但没有看到要插入的行的重复项，则可以安全地插入行，并知道在读取期间在行的后续行上设置的next-key锁可防止任何人同时插入行的重复项。

   间隙锁可以被禁用。但这可能会导致幻读问题，因为其他会话可以在间隙锁被禁用的情况下，把新纪录插入间隙。

二 死锁

2.1 InnoDB死锁示例（mysql8.0版本）

   下面的示例阐述了，当一个锁请求导致死锁时会发生怎样的错误。示例包括两个客户端，A 和 B。

首先，客户端A创建一个质保函一行记录的表，然后开启一个事务。在这个事务内，A通过共享模式查询，获得这行记录的一个S锁（共享锁）。

mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;
Query OK, 0 rows affected (1.07 sec)
mysql> INSERT INTO t (i) VALUES(1);
Query OK, 1 row affected (0.09 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t WHERE i = 1 FOR SHARE;
+------+
| i    |
+------+
|    1 |
+------+

需要特别注意的是，select * from xx for share 是mysql8版本的语法，当在mysql低版本中执行这条语句时，会报语法错误，例如我在5.7.28-log：

mysql> SELECT * FROM t WHERE i = 1 FOR SHARE;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SHARE' at line 1
mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.7.28-log |
+------------+

如果要继续这个示例，需要改为：

SELECT * FROM t WHERE i = 1 lock in share mode;

  接下来，客户端B开启一个事务，并尝试从表中删除这行记录。

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> DELETE FROM t WHERE i = 1;

   删除操作需要一个X锁（排它锁）。这个锁不会被授予，因为它和客户端A持有的排它锁不兼容，所以请求进入行和客户机B块的锁请求队列。

   最后，客户端A还尝试从表中删除行：

mysql> DELETE FROM t WHERE i = 1;

   在这里由于客户端A需要一个X锁来删除这行记录，所以发生了死锁。但是无法授予该锁请求，因为客户机B已经请求了X锁，并且正在等待客户机a释放其S锁；也不能因为B事先请求X锁而将A持有的基础锁升级为X锁。结果，InnoDB为其中一个客户机生成一个错误并释放其锁。客户端返回此错误：

ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction

   此时，可以授予另一个客户端的锁请求，并从表中删除该行。

注：另外还有一种情况，上述示例中客户端A一直持有S锁，且不执行删除动作也不提交；客户端B会一直等待X锁，但不会无限制的等下去。当超过超时时间阈值时，会返回如下错误：

mysql>  delete from t where i=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

默认情况是50秒，这是InnoDB事务等待行锁的时长限制，在参数innodb_lock_wait_timeout中：

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+

2.2 死锁检测

   当开启死锁检测默认开启，InnoDB会自动检查事务中的死锁，并回滚一个或多个事务来打破死锁。InnoDB尝试选择“小”事务来进行回滚，事务的大小由插入，更新，或删除的行数来决定。

   如果innoDB_table_locks=1（默认值）和autocommit=0，那么InnoDB感知表锁，并且上面的MySQL层感知行级锁。否则，InnoDB无法检测死锁，其中涉及由MySQL lock TABLES语句设置的表锁或由InnoDB以外的存储引擎设置的锁。通过设置innodb_lock_wait_timeout这个系统变量可以解决这些问题。

   如果InnoDB Monitor输出的LATEST DETECTED DEADLOCK部分包含一条消息，说明TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION（在锁表WAITS-FOR图中搜索太深或太长，我们将回滚下面的事务），这表明wait-for列表上的事务数已经达到了200的限制。超过200个事务的等待列表将被视为死锁，并尝试检查等待列表的事务将回滚。如果锁定线程必须查看等待列表上事务拥有的超过1000000个锁，也可能发生相同的错误。

2.2.1 禁用死锁检测

   在高并发系统中，当多个线程等待同一把锁时，死锁检测可能导致减速。有时，禁用死锁检测并在死锁发生时依赖innodb_lock_wait_timeout设置事务回滚可能更有效。死锁检测可以使用innodb_deadlock_detect配置项禁用。

mysql> show variables like 'innodb_deadlock_detect';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_deadlock_detect | ON    |
+------------------------+-------+
1 row in set (0.01 sec)

2.3 如何最小化和处理死锁

   前面描述了死锁场景和检测工具，本章以上一章的死锁检测内容为基础，将描述怎样最小化和处理死锁。

   死锁在事务型数据库中是一个经典问题，但它们并不危险，除非它们非常频繁，以至于根本无法运行某些事务。通常，必须编写应用程序，以便在事务由于死锁而回滚时，它们随时准备重新发布事务。

   InnoDB使用自动行级锁，即使事务只是插入或删除一行，也可能会出现死锁。这是因为这些操作并不是真的“自动”；它们会自动对插入或删除的行的索引记录（可能有多个）设置锁。

   我们可以使用以下技术处理死锁并降低死锁发生的可能性：

• 任意时间，都可以通过show_engine_innodb_status命令来确认最近一次死锁的原因。这可以帮你调整应用来避免死锁。
  
• 如果频繁死锁报警引起关注，可以通过开启innodb_print_all_deadlocks配置选项来收集更多调试信息。不只是最近的一个，每条关于死锁的信息都被记录到了MySQL的 error log中。在调试完成后关闭这个选项。
  
• 如果事务因死锁而失败，请随时准备重新发出事务。死锁并不危险，只要重试就好。
  
• 使事务保持小而短的持续时间，以使它们不易发生冲突。
  
• 在进行一组相关更改后立即提交事务，以使它们不易发生冲突。特别是，不要让交互式mysql会话在未提交事务的情况下长时间处于打开状态。
  
• 如果使用锁定读(SELECT ... FOR UPDATE 或 SELECT ... FOR SHARE)，尝试使用较低的隔离级别，例如READ COMMITTED。
  
• 当修改一个事务中的多个表或同一个表中的不同行集时，每次都以一致的顺序执行这些操作。那么事务会形成定义良好的队列，不会死锁。例如，将数据库操作组织到应用程序中的函数中，或者调用存储过程，而不是在不同的位置编码多个类似的INSERT、UPDATE和DELETE语句序列
  
• 为表添加精心选择的索引。这样查询需要扫描更少的索引记录，从而设置更少的锁。使用EXPLAIN SELECT来确定MySQL服务器认为哪些索引最适合你的查询。
  
• 少使用锁。如果能够接受通过SELECT来返回旧的快照数据，就不要增加FOR UPDATE或FOR SHARE子句。在这里，使用READ COMMITTED隔离级别比较适合，因为同一事务中的每个一致读取都从自己的新快照中读取。
  
• 如果没有其他帮助，请用表级锁串行化事务。在事务表（例如InnoDB表）中使用LOCK TABLES的正确方式，是通过设置autocommit=0（而不是START TRANSACTION）开启事务，然后使用LOCK TABLES，并且不要在你完全提交事务之前调用UNLOCK TABLES。例如，如果你需要写表t1并且读表t2，你可以像下面方式：
  

SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;

• 串行化事务的其他方法，是创建一个辅助的“信号量”表，它只包含一行。让每个事务在访问其他表之前更新该行。这样，所有事务都以串行方式发生。注意，InnoDB即时死锁检测算法也适用于这种情况，因为序列化锁是行级锁。对于MySQL表级锁，必须使用timeout方法来解决死锁。
  

三 事务调度

   InnoDB使用 竞争感知事务调度（CATS）算法来对等待锁定的事务进行优先级排序。当多个事务等待同一对象上的锁时，CATS算法确定哪个事务首先接收锁。

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

注意：

   在MySQL 8.0.20之前，InnoDB也使用先入先出（FIFO）算法来调度事务，CATS算法只用在重锁竞争的场景。MySQL 8.0.20中的CATS算法增强使FIFO算法变得多余，允许删除它。从MySQL 8.0.20开始，以前由FIFO算法执行的事务调度由CATS算法执行。在某些情况下，此更改可能会影响事务被授予锁的顺序。