MySQL 并发教程（三）
十、索引和外键
在前一章中，你学习了索引和外键如何影响锁定。这是一个值得深入探讨的话题，因为理解这些影响很重要。
本章的第一部分研究了主索引、次索引、升序索引、降序索引和唯一索引如何影响锁定。第二部分介绍外键以及它们如何影响 DML 和 DDL 语句的锁定。
索引
简而言之，索引提供了访问给定记录的捷径，从而减少了检查的记录数量。这对锁的数量有积极的影响，因为只有被访问的行会被锁定。这是您在上一章中看到的，当时一个索引被添加到了表world.city的Name列中，用于在Name列上过滤的查询。当连接表时，索引变得特别重要，因为没有索引，被访问的行数是连接表中行数的乘积。
Note
由于 MySQL 8 中对散列连接的支持，您可能认为索引不那么重要了。虽然这在一定程度上适用于非锁定语句，但对于使用锁的语句来说，情况就不那么如此了，因为过度锁定会导致锁等待和死锁。正如在第 11 章中所讨论的，锁也会消耗缓冲池中的内存，所以更多的锁意味着用于缓存数据的内存更少。类似地，减少被访问的行数也会减少缓冲池中的页面周转，从而提高缓冲池命中率。
本节首先讨论主索引和辅助索引的使用，然后讨论升序索引和降序索引，最后讨论唯一索引。
主索引与辅助索引
访问行最有效的方法是通过它的主键，因为这样可以确保只访问受语句影响的行。例如，考虑清单 10-1 ，它在world.city的Name列上添加了一个二级索引，并更新了城市名称悉尼的人口。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 713 1171 6
-- 2 714 1172 6
-- Connection 1
Connection 1> ALTER TABLE world.city
ADD INDEX (Name);
Query OK, 0 rows affected (1.3916 sec)
Records: 0 Duplicates: 0 Warnings: 0
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0002 sec)
Connection 1> UPDATE world.city
SET Population = 5000000
WHERE Name = 'Sydney';
Query OK, 1 row affected (0.0007 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- Connection 2
Connection 2> SELECT index_name, lock_type,
lock_mode, lock_data
FROM performance_schema.data_locks
WHERE object_schema = 'world'
AND object_name = 'city'
AND thread_id = 1171\G
*************************** 1\. row ***************************
index_name: NULL
lock_type: TABLE
lock_mode: IX
lock_data: NULL
*************************** 2\. row ***************************
index_name: Name
lock_type: RECORD
lock_mode: X
lock_data: 'Sydney ', 130
*************************** 3\. row ***************************
index_name: PRIMARY
lock_type: RECORD
lock_mode: X,REC_NOT_GAP
lock_data: 130
*************************** 4\. row ***************************
index_name: Name
lock_type: RECORD
lock_mode: X,GAP
lock_data: 'Syktyvkar ', 3660
4 rows in set (0.0006 sec)
-- Connection 1
Connection 1> ROLLBACK;
Query OK, 0 rows affected (0.0625 sec)
Connection 1> ALTER TABLE world.city
DROP INDEX Name;
Query OK, 0 rows affected (0.4090 sec)
Records: 0 Duplicates: 0 Warnings: 0)
Listing 10-1Updating row by non-unique secondary index
尽管更新只影响一行，但是有三个排他的记录级锁，一个是记录，另一个是索引Name上的间隙锁，还有一个是使用主键的行记录锁。
另一方面，如果您执行相同的更新，但是使用主键访问行，那么只需要主键上的记录锁，如清单 10-2 所示。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 719 1180 6
-- 2 720 1181 6
-- Connection 1
Connection 1> ALTER TABLE world.city
ADD INDEX (Name);
Query OK, 0 rows affected (1.1499 sec)
Records: 0 Duplicates: 0 Warnings: 0
Connection 1> SELECT ID
FROM world.city
WHERE Name = 'Sydney';
+-----+
| ID |
+-----+
| 130 |
+-----+
1 row in set (0.0004 sec)
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0002 sec)
Connection 1> UPDATE world.city
SET Population = 5000000
WHERE ID = 130;
Query OK, 1 row affected (0.0027 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- Connection 2
Connection 2> SELECT index_name, lock_type,
lock_mode, lock_data
FROM performance_schema.data_locks
WHERE object_schema = 'world'
AND object_name = 'city'
AND thread_id = 1180\G
*************************** 1\. row ***************************
index_name: NULL
lock_type: TABLE
lock_mode: IX
lock_data: NULL
*************************** 2\. row ***************************
index_name: PRIMARY
lock_type: RECORD
lock_mode: X,REC_NOT_GAP
lock_data: 130
2 rows in set (0.0007 sec)
-- Connection 1
Connection 1> ROLLBACK;
Query OK, 0 rows affected (0.0410 sec)
Connection 1> ALTER TABLE world.city
DROP INDEX Name;
Query OK, 0 rows affected (0.3257 sec)
Records: 0 Duplicates: 0 Warnings: 0
Listing 10-2Updating row by the primary index
在这种情况下，主键值(ID列)首先在更新行的事务之外获得，然后主键值用于UPDATE语句的WHERE子句中。结果是持有的唯一记录级锁是值为 130 的主键上的独占锁。
Caution
在事务之外确定主键值确实有可能在获取主键值和执行更新之间改变数据。因此，您应该仅将此作为一个示例。
除非需要更改表中的所有行，否则应该使用索引来访问这些行。即使对于全表更新，如果不要求自动应用更改，那么对于大型表，在由主键上的范围定义的相对较小的批次中应用更改是有利的。
Tip
如果按函数过滤，减少检查行数的一个好方法是添加一个函数索引(在 MySQL 8.0.13 和更高版本中可用)。或者，在 MySQL 5.7 和更高版本中，您可以添加一个带有索引的生成列。
除了访问的行数之外，还有更多关于索引和锁定的内容。降序索引和随后的惟一索引也可以减少锁的数量。
升序与降序索引
MySQL 8 支持降序索引，这可以提高按降序访问行的性能。您可以使用升序索引来按降序访问行，因此在这种情况下使用降序索引的主要好处是不会在页面中来回跳转。这是否意味着在锁定时选择索引的顺序没有好处？
当您以与存储索引记录相反的顺序使用索引时，您将付出很小的代价，因为您需要在搜索开始时锁定间隙。清单 10-3 显示了在现有人口在 100 万到 200 万之间的三个人口最多的城市增加 10%的人口时，使用升序索引时持有的锁。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 836 1363 6
-- Connection 1
Connection 1> ALTER TABLE world.city
ADD INDEX (Population);
Query OK, 0 rows affected (1.1838 sec)
Records: 0 Duplicates: 0 Warnings: 0
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0005 sec)
Connection 1> UPDATE world.city
SET Population = Population * 1.10
WHERE Population BETWEEN 1000000 AND 2000000
ORDER BY Population DESC
LIMIT 3;
Query OK, 3 rows affected (0.0014 sec)
Rows matched: 3 Changed: 3 Warnings: 0
-- Investigation #1
-- Connection 2
Connection 2> SELECT index_name, lock_type,
lock_mode, lock_data
FROM performance_schema.data_locks
WHERE object_schema = 'world'
AND object_name = 'city'
AND lock_type = 'RECORD'
AND thread_id = 1363
ORDER BY index_name, lock_data DESC;
+------------+-----------+---------------+---------------+
| index_name | lock_type | lock_mode | lock_data |
+------------+-----------+---------------+---------------+
| Population | RECORD | X,GAP | 2016131, 3018 |
| Population | RECORD | X | 1987996, 936 |
| Population | RECORD | X | 1977246, 2824 |
| Population | RECORD | X | 1975294, 3539 |
| PRIMARY | RECORD | X,REC_NOT_GAP | 936 |
| PRIMARY | RECORD | X,REC_NOT_GAP | 3539 |
| PRIMARY | RECORD | X,REC_NOT_GAP | 2824 |
+------------+-----------+---------------+---------------+
7 rows in set (0.0008 sec)
Listing 10-3Updating rows in descending order by ascending index
正如所料，主键和Population索引上有三个锁，每个更新的行有一个锁。这是最理想的了。使用升序索引的锁的代价是，在人口为 2016131 且主键设置为 3018 的索引记录上还有一个间隙锁。
Tip
InnoDB 总是将主键附加到非唯一的二级索引的末尾，因此很容易从索引记录转到该行。这样做的原因是，InnoDB 根据聚集索引以及聚集索引使用的显式主键来组织行。
关于这个例子中的锁，还有两件事需要注意。首先，如果您从索引的末尾(人口最多的城市)进行更新，那么您将看到上确界伪记录上的记录锁，而不是间隙锁和区间的高人口末尾。这是因为上确界伪记录不是真正的记录，所以对它的记录锁定实际上只是对它之前的间隙的锁定。其次，所涉及的确切锁类型取决于WHERE子句，因此如果您更改或删除WHERE子句，您可能会在二级索引上看到额外的间隙锁。这些额外的间隙锁也将出现在使用降序索引的同一示例中。
如果使用降序索引，除了间隙锁之外，锁的列表是相同的。清单 10-4 展示了一个这样的例子。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 843 1374 6
-- Connection 1
Connection 1> ALTER TABLE world.city
ADD INDEX (Population DESC);
Query OK, 0 rows affected (0.8885 sec)
Records: 0 Duplicates: 0 Warnings: 0
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0006 sec)
Connection 1> UPDATE world.city
SET Population = Population * 1.10
WHERE Population BETWEEN 1000000 AND 2000000
ORDER BY Population DESC
LIMIT 3;
Query OK, 3 rows affected (0.0021 sec)
Rows matched: 3 Changed: 3 Warnings: 0
-- Investigation #1
-- Connection 2
Connection 2> SELECT index_name, lock_type,
lock_mode, lock_data
FROM performance_schema.data_locks
WHERE object_schema = 'world'
AND object_name = 'city'
AND lock_type = 'RECORD'
AND thread_id = 1374
ORDER BY index_name, lock_data DESC;
+------------+-----------+---------------+---------------+
| index_name | lock_type | lock_mode | lock_data |
+------------+-----------+---------------+---------------+
| Population | RECORD | X | 1987996, 936 |
| Population | RECORD | X | 1977246, 2824 |
| Population | RECORD | X | 1975294, 3539 |
| PRIMARY | RECORD | X,REC_NOT_GAP | 936 |
| PRIMARY | RECORD | X,REC_NOT_GAP | 3539 |
| PRIMARY | RECORD | X,REC_NOT_GAP | 2824 |
+------------+-----------+---------------+---------------+
6 rows in set (0.0008 sec)
Listing 10-4Updating rows in descending order by descending index
结论是，对数据进行降序访问时，降序索引的主要好处是更有序的数据访问的性能提高，而不是减少锁定。然而，也就是说，如果在更新或删除数据时有许多递减范围的扫描，那么您也将受益于更少的间隙锁。
另一种减少锁定的索引类型是唯一索引。
唯一索引
与等效的非唯一索引相比，唯一索引的主要目的是添加每个值只允许出现一次的约束。因此，从表面上看，除了已经提到的以外，唯一索引似乎与锁的讨论没有什么关系。但是，InnoDB 知道对于给定的权益条件，最多只能存在一条记录(除了值为NULL的记录),因此可以利用这一点来减少所需的锁定数量。
例如，考虑两个表，_tmp_city1和_tmp_city2，包含来自world.city表的相同行子集。_tmp_city1表在Name列上有一个非唯一索引，而_tmp_city2表在该列上有一个唯一索引。然后使用Name列上的条件更新一行。清单 10-5 显示了这一点。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 736 1209 6
-- 2 737 1210 6
-- Connection 1
Connection 1> DROP TABLE IF EXISTS world._tmp_city1;
Query OK, 0 rows affected (0.0643 sec)
Note (code 1051): Unknown table 'world._tmp_city1'
Connection 1> CREATE TABLE world._tmp_city1
SELECT *
FROM world.city
WHERE CountryCode = 'AUS';
Query OK, 14 rows affected (1.3112 sec)
Records: 14 Duplicates: 0 Warnings: 0
Connection 1> ALTER TABLE world._tmp_city1
ADD PRIMARY KEY (ID),
ADD INDEX (Name);
Query OK, 0 rows affected (2.5572 sec)
Records: 0 Duplicates: 0 Warnings: 0
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0002 sec)
Connection 1> UPDATE world._tmp_city1
SET Population = 5000000
WHERE Name = 'Sydney';
Query OK, 1 row affected (0.0007 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- Connection 2
Connection 2> DROP TABLE IF EXISTS world._tmp_city2;
Query OK, 0 rows affected (0.1361 sec)
Note (code 1051): Unknown table 'world._tmp_city2'
Connection 2> CREATE TABLE world._tmp_city2
SELECT *
FROM world.city
WHERE CountryCode = 'AUS';
Query OK, 14 rows affected (0.8276 sec)
Records: 14 Duplicates: 0 Warnings: 0
Connection 2> ALTER TABLE world._tmp_city2
ADD PRIMARY KEY (ID),
ADD UNIQUE INDEX (Name);
Query OK, 0 rows affected (2.4895 sec)
Records: 0 Duplicates: 0 Warnings: 0
Connection 2> START TRANSACTION;
Query OK, 0 rows affected (0.0003 sec)
Connection 2> UPDATE world._tmp_city2
SET Population = 5000000
WHERE Name = 'Sydney';
Query OK, 1 row affected (0.0005 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Listing 10-5The difference between non-unique and unique secondary indexes
当事务仍在进行时，您可以检查每个连接持有的锁。对于连接 1，清单 10-6 中显示了记录锁，这是清单 10-5 中执行的工作负载的调查号 1。
-- Investigation #1
-- Connection 3
Connection 3> SELECT index_name, lock_mode, lock_data
b FROM performance_schema.data_locks
WHERE object_schema = 'world'
AND lock_type = 'RECORD'
AND thread_id = 1209\G
*************************** 1\. row ***************************
index_name: Name
lock_mode: X
lock_data: 'Sydney ', 130
*************************** 2\. row ***************************
index_name: PRIMARY
lock_mode: X,REC_NOT_GAP
lock_data: 130
*************************** 3\. row ***************************
index_name: Name
lock_mode: X,GAP
lock_data: 'Townsville ', 142
3 rows in set (0.0094 sec)
Listing 10-6The record locks for Connection 1 (investigation number 1)
根据前面示例的经验，主键上的记录锁以及索引上的记录和间隙锁都是这种情况。对于连接 2，只有两个锁存在，如清单 10-5 中调查编号 2 的清单 10-7 中的输出所示。
-- Investigation #2
Connection 3> SELECT index_name, lock_mode, lock_data
FROM performance_schema.data_locks
WHERE object_schema = 'world'
AND lock_type = 'RECORD'
AND thread_id = 1210\G
*************************** 1\. row ***************************
index_name: Name
lock_mode: X,REC_NOT_GAP
lock_data: 'Sydney ', 130
*************************** 2\. row ***************************
index_name: PRIMARY
lock_mode: X,REC_NOT_GAP
lock_data: 130
2 rows in set (0.0006 sec)
Listing 10-7The record locks for Connection 2 (investigation number 2)
这里只需要二级索引和主键上的两个记录锁。
Tip
关于 InnoDB 中有无唯一索引的各种语句所使用的锁的完整列表，请参见 https://dev.mysql.com/doc/refman/en/innodb-locks-set.html 。
最后，回滚并删除测试表:
-- Connection 1
Connection 1> ROLLBACK;
Query OK, 0 rows affected (0.0714 sec)
Connection 1> DROP TABLE world._tmp_city1;
Query OK, 0 rows affected (0.7642 sec)
-- Connection 2
Connection 2> ROLLBACK;
Query OK, 0 rows affected (0.1038 sec)
Connection 2> DROP TABLE world._tmp_city2;
Query OK, 0 rows affected (1.4438 sec)
与可用于减少锁定的唯一键不同，外键将添加锁，这将在下面讨论。
外键
外键是确保数据库中各表之间数据一致性的强大工具。然而，它们有一个缺点，即为了提供这种安全性，需要在父表和子表上附加锁。
所需的额外锁取决于外键关系的父表还是子表被更改，以及哪些列被更改。对于元数据锁，共享锁在大多数情况下是在外键关系所涉及的表上获得的。向外键关系的父表中插入时是一个例外；在这种情况下，不会对子表使用共享元数据锁。
在 InnoDB 级别，如果包含在外键中的列被修改，则在与外键列的新值的关系的另一端的表上设置一个共享记录级锁，并为该表设置一个意向共享锁。无论是否违反了外键，都会发生这种情况。如果没有外键列被更改，即使该列用于过滤，InnoDB 也不会获取任何额外的锁。
为了理解这对你的影响，有必要考虑几个例子。它们都使用sakila.inventory表，该表有两个外键指向film和store表。同时，它还是来自film_rental表的外键的父表。如图 10-1 所示。
图 10-1
sakila.inventory表及其外键关系
在该图中，只包括主键和外键中涉及的列。首先讨论一个更新库存表中的行的例子，然后讨论一个 DDL 语句。
DML 语句
作为由 DML 语句的外键引起的锁的一个例子，考虑一个将电影从一个商店移动到另一个商店的UPDATE语句。更新是通过主键进行的，清单 10-8 中显示了一个这样的例子。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 814 1329 6
-- Connection 1
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0002 sec)
Connection 1> UPDATE sakila.inventory
SET store_id = 1
WHERE inventory_id = 4090;
Query OK, 1 row affected (0.0008 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Listing 10-8Updating a row in a table with foreign keys relationships
由于外键的原因，这个简单的单个表和行更新需要跨越sakila数据库中大量表的许多锁。清单 10-9 显示了由语句引起的 InnoDB 锁(这是调查编号 1)。
-- Investigation #1
-- Connection 2
Connection 2> SELECT object_schema, object_name, lock_type,
index_name, lock_mode, lock_data
FROM performance_schema.data_locks
WHERE thread_id = 1329\G
*************************** 1\. row ***************************
object_schema: sakila
object_name: inventory
lock_type: TABLE
index_name: NULL
lock_mode: IX
lock_data: NULL
*************************** 2\. row ***************************
object_schema: sakila
object_name: inventory
lock_type: RECORD
index_name: PRIMARY
lock_mode: X,REC_NOT_GAP
lock_data: 4090
*************************** 3\. row ***************************
object_schema: sakila
object_name: store
lock_type: TABLE
index_name: NULL
lock_mode: IS
lock_data: NULL
*************************** 4\. row ***************************
object_schema: sakila
object_name: store
lock_type: RECORD
index_name: PRIMARY
lock_mode: S,REC_NOT_GAP
lock_data: 1
4 rows in set (0.0102 sec)
Listing 10-9The InnoDB data locks caused by the UPDATE statement
在这种情况下，InnoDB 在store表上获取一个意向共享锁，并在主键设置为 1 的记录上获取一个共享锁。因为UPDATE语句不会改变film_id列的值，所以film表上没有锁。
对于元数据锁，从清单 10-10 (调查编号 2)中可以看出，它更加复杂。
-- Investigation #2
Connection 2> SELECT object_type, object_schema, object_name,
column_name, lock_type, lock_duration
FROM performance_schema.metadata_locks
WHERE owner_thread_id = 1329
ORDER BY object_type, object_schema, object_name,
column_name, lock_type\G
*************************** 1\. row ***************************
object_type: SCHEMA
object_schema: sakila
object_name: NULL
column_name: NULL
lock_type: INTENTION_EXCLUSIVE
lock_duration: TRANSACTION
*************************** 2\. row ***************************
object_type: TABLE
object_schema: sakila
object_name: customer
column_name: NULL
lock_type: SHARED_READ
lock_duration: TRANSACTION
*************************** 3\. row ***************************
object_type: TABLE
object_schema: sakila
object_name: film
column_name: NULL
lock_type: SHARED_READ
lock_duration: TRANSACTION
*************************** 4\. row ***************************
object_type: TABLE
object_schema: sakila
object_name: inventory
column_name: NULL
lock_type: SHARED_WRITE
lock_duration: TRANSACTION
*************************** 5\. row ***************************
object_type: TABLE
object_schema: sakila
object_name: payment
column_name: NULL
lock_type: SHARED_WRITE
lock_duration: TRANSACTION
*************************** 6\. row ***************************
object_type: TABLE
object_schema: sakila
object_name: rental
column_name: NULL
lock_type: SHARED_WRITE
lock_duration: TRANSACTION
*************************** 7\. row ***************************
object_type: TABLE
object_schema: sakila
object_name: staff
column_name: NULL
lock_type: SHARED_READ
lock_duration: TRANSACTION
*************************** 8\. row ***************************
object_type: TABLE
object_schema: sakila
object_name: store
column_name: NULL
lock_type: SHARED_READ
lock_duration: TRANSACTION
8 rows in set (0.0007 sec)
Listing 10-10The metadata locks caused by the UPDATE statement
您不会总是在sakila模式上看到INTENTION_EXCLUSIVE锁，所以您的结果可能只包括七个表级元数据锁。
这表明在film和store表上有一个SHARED_READ锁，在rental表上有一个SHARED_WRITE,这是讨论到目前为止所预期的。但是，还有几个元数据锁。额外的锁是因为rental表的库存外键是ON UPDATE CASCADE。这使得元数据锁级联到rental表的外键关系。这个例子告诉我们，对于外键，尤其是级联关系，需要注意元数据锁的数量会迅速增加。
最后，回滚事务:
-- Connection 1
Connection 1> ROLLBACK;
Query OK, 0 rows affected (0.1104 sec)
DDL 语句
当您对一个带有外键的表执行 DDL 语句时，会为修改后的表的每个父表和子表获取一个SHARED_UPGRADABLE元数据锁。清单 10-11 中显示了一个这样的例子。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 820 1340 6
-- 2 821 1341 6
-- Connection 1
Connection 1> OPTIMIZE TABLE sakila.inventory;
-- Connection 2
Connection 2> SELECT object_name, lock_type, lock_duration
FROM performance_schema.metadata_locks
WHERE owner_thread_id = 1340
AND object_type = 'TABLE';
+---------------+-------------------+---------------+
| object_name | lock_type | lock_duration |
+---------------+-------------------+---------------+
| inventory | SHARED_NO_WRITE | TRANSACTION |
| film | SHARED_UPGRADABLE | STATEMENT |
| rental | SHARED_UPGRADABLE | STATEMENT |
| store | SHARED_UPGRADABLE | STATEMENT |
| #sql-8490_334 | EXCLUSIVE | STATEMENT |
+---------------+-------------------+---------------+
5 rows in set (0.0014 sec)
Listing 10-11Performing DDL on a table with foreign key relations
在这种情况下，rental表上的级联外键不会导致进一步的元数据锁定，因为没有要级联的更新。#sql-8490_334表是OPTIMIZE TABLE语句的构建表，其名称取决于mysqld进程的 id 和执行该语句的连接的进程列表 id。
结论是，虽然外键对于确保数据一致性非常重要，但在高并发性工作负载中，由于额外的锁定(以及在约束验证上花费的时间)，外键可能会成为瓶颈。但是，不要在默认情况下忽略外键，因为这会危及数据的完整性，而且它们对于记录表之间的关系也很有用；本章前面的图是 MySQL Workbench 根据外键自动生成的。
Caution
不要因为额外的锁定而忽略外键，因为它们是确保数据一致性所必需的。如果它们对您的工作负载来说过于昂贵，您将需要确保您的应用中的数据一致性，这绝不是一项微不足道的任务。如果没有足够好的数据完整性约束，最终可能会向用户返回无效数据。
摘要
本章深入探讨了索引和外键对锁定的影响。索引有助于减少锁定，而外键增加了锁定。
使用的索引越有选择性，访问的行数就越少，也就是说锁定越少。因此，主键访问是最佳的，其次是唯一索引。使用与访问行顺序相同的索引进行访问也会有所帮助。
对于外键，它们需要额外的锁来维护数据完整性。当修改外键中包含的列时，InnoDB 会在外键另一端的行上添加一个共享锁，并为表设置一个意向共享锁。此外，在大多数情况下，外键关系中涉及的表上都采用共享元数据锁。对于级联外键，元数据锁也会级联。
讨论到此结束，主要焦点是锁。不过，硬币还有另外一面，事务。事务不仅与并发直接相关，而且与锁相关。下一章从一般的事务开始。
十一、事务
事务是报表的老大哥。它们将多个更改组合在一起，无论是在单个语句中还是在几个语句中，因此它们作为一个单元被应用或放弃。大多数情况下，事务只是事后的想法，只是在需要将几个语句一起应用时才考虑。这不是考虑事务的好方法。它们对于确保数据完整性非常重要，如果使用不当，会导致严重的性能问题。
本章首先讨论什么是事务和 ACID 概念，然后通过回顾事务对锁和性能的影响，继续讨论为什么需要从性能的角度认真对待事务。最后，讨论了组提交特性如何提高高并发系统的性能。
事务和 ACID
从最简单的角度来看，事务是一个包含一个或多个语句的容器。然而，这是一种过于简单化的观点，因为事务也有其自身的属性。最重要的是，它是实现原子性、一致性、隔离性和持久性 ( 酸)的主要工具。本节将详细介绍这四个属性。
原子数
原子性的概念意味着事务中的所有更改要么被提交，要么被回滚。这就是容器的概念发挥作用的地方，因此所有语句都被视为一个工作单元——或者换句话说，一个事务是原子的。
原子性为什么重要的经典例子是两个银行账户之间的金融事务。从付款人的账户中提取一笔金额，并将其插入收款人的账户。如果没有原子性，你可能最终会把钱取出来，但永远不会插入，从而让双方中的一方赔钱。事务是原子保证，如果钱被提取，接收者也收到他们账户上的钱。
一致性
数据库满足一致性属性意味着有适当的检查来确保如果事务成功提交，那么数据是一致的。一致性的含义很大程度上是由业务逻辑定义的。例如，您不能为不存在的实体创建银行帐户。在数据库内部，约束(包括外键)的工作是确保数据的一致性。通过使用事务，由于其原子行为，即使约束在第二步或后面的步骤中失败，也可以恢复(回滚)整个事务，并且数据库保持其原始和一致的状态。
在某些数据库中，约束检查可以推迟到提交事务时进行。如果您考虑这样一个例子，不允许您拥有一个不存在的实体的银行帐户，延迟约束允许您首先创建帐户，然后在同一事务中注册拥有该帐户的实体。延迟约束主要用于循环关系，如添加必须具有默认组成员的组，但由于成员必须属于某个组，因此在添加组及其初始成员时，有必要暂时放松约束。
Note
延迟约束的使用是一个有争议的话题，可以认为它违反了关系数据库理论的原则。例如，C.J. Date 认为约束必须在语句边界得到满足； https://www.brcommunity.com/articles.php?id=b065b见。
InnoDB 不支持延迟约束，但支持通过显式禁用foreign_key_checks变量来禁用外键检查，该变量可在全局和会话范围内更改。
Tip
在已经确保数据一致的情况下执行大容量装载时，禁用外键检查会很有用。
InnoDB 不能禁用唯一键约束检查，因为unique_checks选项仅指示不需要检查；InnoDB 在某些情况下仍然会这样做。(顺便说一下，NDBCluster确实将一些约束检查推迟到提交时。)
隔离
隔离属性是连接事务和锁定的纽带。两个事务是独立的，这意味着它们不会干扰彼此的数据视图。隔离是在数据内容级别；两个并发事务在性能和锁定方面仍然可能相互干扰。与大多数数据库系统一样，MySQL 使用锁来实现隔离，InnoDB 有事务隔离级别的概念来定义隔离的含义。下一章将更详细地讨论事务隔离级别。
持久性
数据是持久的意味着数据更改不会丢失。在 MySQL 中，这仅适用于提交的数据或准备阶段事务的 XA 事务。InnoDB 通过重做日志和二进制日志(如果启用，这是 MySQL 8 中的默认设置)在本地级别实现持久性，并使用内部 XA 事务来确保两个日志之间的一致性。
Note
XA 事务是一个允许分布式事务的特性，例如，跨两个系统或在 MySQL 内部对 InnoDB 和二进制日志一起提交或回滚进行更改。它通过一个事务管理器和一个或多个资源管理器(例如，一个数据库)来工作。有关 MySQL 和 XA 事务的更多信息，请参见 https://dev.mysql.com/doc/refman/en/xa.html 。
只有当innodb_flush_log_at_trx_commit和sync_binlog都设置为 1(默认值)时，提交才保证是持久的。为了确保本地系统崩溃时的持久性，您还必须确保二进制日志事件已经复制到至少一个副本。MySQL 组复制或 MySQL InnoDB 集群是实现这一点的最佳方式。
Tip
复制超出了本书的范围。关于 MySQL 组复制和 InnoDB 集群的介绍，参见，例如，查尔斯·贝尔(Apress)的介绍 InnoDB 集群(https://www.apress.com/gp/book/9781484238844)。
事务的影响
如果您将事务视为用于分组查询的容器，那么事务可能看起来是一个简单的概念。然而，重要的是要理解，因为事务为查询组提供原子性，所以事务活动的时间越长，与查询相关联的资源被占用的时间就越长，并且事务中完成的工作越多，需要的资源就越多。提交事务之前一直在使用的查询使用了哪些资源？主要的两个是锁和撤销日志。
Tip
InnoDB 支持比读写事务开销更低的只读事务。对于自动提交的单语句事务，InnoDB 将尝试自动确定该语句是否是只读的。对于多语句事务，可以在启动时明确指定它是只读事务:START TRANSACTION READ ONLY;
锁
当查询执行时，它获取锁，并且当您使用默认的事务隔离级别–REPEATABLE READ时，所有锁都被保留，直到事务被提交或回滚。当您使用READ COMMITTED事务隔离级别时，一些锁可能会被释放，但至少那些涉及已更改记录的锁会被保留。锁本身就是一种资源，但是它也需要内存来存储关于锁的信息。对于正常的工作负载来说，您可能不认为这有什么了不起，但是巨大的事务最终会使用如此多的内存，以至于事务失败，并出现ER_LOCK_TABLE_FULL错误:
ERROR: 1206: The total number of locks exceeds the lock table size
从错误日志中记录的警告消息可以看出(更简短地说)，锁所需的内存来自缓冲池。因此，持有的锁越多、时间越长，可用于缓存数据和索引的内存就越少。
Caution
因为使用了所有的锁内存而中止一个事务是四重打击。首先，更新足够多的行以使用足够多的锁内存来触发错误需要一些时间。那项工作被浪费了。第二，由于所需更改的数量，回滚事务可能需要很长时间。第三，当锁内存被使用时，InnoDB 实际上处于只读模式(一些小的事务是可能的)，并且直到回滚完成后锁内存才被释放。第四，缓冲池中几乎没有空间来缓存数据和索引。
该错误之前，错误日志中有一条警告，指出超过 67%的缓冲池用于锁或自适应哈希索引:
2020-06-08T10:47:11.415127Z 10 [Warning] [MY-011958] [InnoDB] Over 67 percent of the buffer pool is occupied by lock heaps or the adaptive hash index! Check that your transactions do not set too many row locks. Your buffer pool size is 7 MB. Maybe you should make the buffer pool bigger? Starting the InnoDB Monitor to print diagnostics, including lock heap and hash index sizes.
该警告之后是 InnoDB monitor 的定期重复输出，因此您可以确定哪些事务是罪魁祸首。
一种在事务中经常被忽略的锁类型是元数据锁。当一个语句查询一个表时，会获取一个共享的元数据锁，并且该元数据锁会一直保持到事务结束。当一个表上有一个元数据锁时，任何连接都不能对该表执行任何 DDL 语句——包括OPTIMIZE TABLE。如果一个 DDL 语句被一个长时间运行的事务阻塞，它将依次阻塞所有使用该表的新查询。第 14 章将展示一个调查此类问题的例子。
当事务处于活动状态时，锁被持有。但是，即使事务已经通过撤消日志完成，它仍然会产生影响。
撤消日志
如果您选择回滚事务，则还必须根据需要存储事务期间所做的更改。这很容易理解。更令人惊讶的是，即使一个事务没有进行任何更改，也会使来自其他事务的撤销信息保留下来。当事务需要读视图(一致快照)时会发生这种情况，当使用REPEATABLE READ事务隔离级别时，在事务持续期间就是这种情况。读取视图意味着无论其他事务是否更改数据，事务都将返回与事务开始时间相对应的行数据。为了能够实现这一点，有必要保留在事务生命周期中发生变化的行的旧值。具有读视图的长时间运行的事务是导致大量撤销日志的最常见原因，在 MySQL 5.7 和更早的版本中，这可能意味着 ibdata1 文件变得很大。(在 MySQL 8 中，撤消日志总是存储在单独的可以被截断的撤消表空间中。)
Tip
READ COMMITTED事务隔离级别不太容易出现大的撤销日志，因为读取视图只在语句持续期间维护。
撤消日志的活动部分的大小在历史列表长度中测量。历史列表长度是尚未清除撤消日志的已提交事务的数量。这意味着您不能使用历史列表长度来衡量行更改的总量。它告诉您的是在执行查询时必须考虑的变更链表中有多少个旧行单元(每个事务一个单元)。这个链表越长，找到每一行的正确版本的代价就越大。最后，如果您有一个很大的历史列表，它会严重影响所有查询的性能。
Note
历史列表长度的问题是使用逻辑备份工具创建大型数据库备份的最大问题之一，例如使用单个事务获得一致备份的mysqlpump和mysqldump。如果在备份过程中提交了许多事务，备份可能会导致历史列表变得非常长。
什么构成了一个大的历史列表长度？这方面没有严格的规则，只是越小越好。通常，当列表有几千到一百万个事务时，性能问题就开始出现了，但是当历史列表很长时，它成为瓶颈的点取决于撤消日志中提交的事务和工作负载。
当不再需要最旧的部件时，InnoDB 会在后台自动清除历史列表。有两个选项可以控制清洗，也有两个选项可以影响清洗无法进行时会发生什么。这些选项包括
-
innodb_purge_batch_size: 每批清除的撤消日志页数。该批次在清除线程之间划分。该选项不应在生产系统上更改。默认值为 300，有效值介于 1 和 5000 之间。 -
innodb_purge_threads: 并行使用的清除线程数。如果数据更改跨越多个表，那么更高的并行度会很有用。另一方面，如果所有更改都集中在少数几个表上，则首选低值。更改清除线程的数量需要重启 MySQL。默认值为 4，有效值介于 1 和 32 之间。 -
innodb_max_purge_lag: 当历史列表长度大于innodb_max_purge_lag的值时，会给更改数据的操作增加一个延迟，以降低历史列表的增长速度，但代价是语句延迟增加。默认值为 0，这意味着永远不会添加延迟。有效值为 0–4294967295。 -
innodb_max_purge_lag_delay: 当历史列表长度大于innodb_max_purge_lag时，可以添加到 DML 查询的最大延迟。
通常没有必要更改这些设置；但是，在特殊情况下，它可能是有用的。如果清除线程跟不上，您可以尝试根据被修改的表的数量来更改清除线程的数量；修改的表越多，清除线程就越有用。当您更改清除线程的数量时，从更改前的基线开始监控效果非常重要，这样您就可以看到更改是否带来了改进。
最大清除延迟选项可用于降低修改数据的 DML 语句的速度。当写入仅限于特定的连接，并且延迟不会导致创建额外的写入线程以保持相同的吞吐量时，此功能非常有用。
群组提交
请记住，为了使 InnoDB 中的 D in ACID(耐久性)为真，您需要将innodb_flush_log_at_trx_commit和sync_binlog设置保持为默认值 1，以便事务所做的更改作为提交的一部分被同步到磁盘。虽然这对于确保您在崩溃时不会丢失已确认提交的更改非常有用，但从成本效益的角度来看，这确实是有代价的，因为磁盘的刷新性能常常会成为瓶颈。
存在组提交功能是为了减少这种性能影响，方法是稍微延迟提交，并对延迟期间发生的所有提交(即名称)进行分组，同时将它们刷新到磁盘。本质上，组提交牺牲了一点延迟来获得更高的吞吐量。在数据更改具有高并发性的系统中，当使用sync_binlog = 1时，组提交可以极大地提高性能。
使用两个配置选项来控制组提交功能:
-
binlog_group_commit_sync_delay: 等待更多事务提交的延迟时间(毫秒)。允许的值为 0–1000000，默认值为 0。值越大，一起提交的事务就越多，因此fsync()调用就越少。 -
binlog_group_commit_sync_no_delay_count: 在完成组提交之前，组提交队列中允许的最大事务数。如果该选项设置为大于 0 的值，提交的次数可能会比binlog_group_commit_sync_delay设置的次数更多。值为 0 意味着可以对无限数量的提交进行排队。允许的值为 0–1000000，默认值为 0。
如果您可以接受提交事务时的小延迟，建议增加binlog_group_commit_sync_delay以降低刷新速率。原则上，该值越大，吞吐量就越大，但是您当然应该考虑您的工作负载的最大可接受的提交延迟增加量。您可以使用binlog_group_commit_sync_no_delay_count来避免每个组提交中的事务数量变得过大。
如果您启用了复制，那么增加binlog_group_commit_sync_delay也会对副本产生积极的性能影响，因为一起提交的事务越多，用于并行复制的LOGICAL_CLOCK算法(slave_parallel_type选项)就变得越有效。(如果有binlog_transaction_dependency_tracking = WRITESET，效果会小一些，因为事务可以跨组提交并行化。)您必须在复制源上设置binlog_group_commit_sync_delay，以提高副本上的并行复制性能。
摘要
事务是数据库中的一个重要概念。它们有助于确保您可以将更改作为一个单元应用到几行，并且可以选择是应用更改还是回滚更改。
本章开始讨论什么是事务和 ACID 概念。ACID 代表原子性、一致性、隔离性和持久性，事务直接参与确保前三个属性，部分代表持久性。在并发环境中，隔离很有意思，因为它可以确保您可以安全地并发执行多个事务。在 MySQL 中，隔离是通过锁定实现的。
下一节将讨论为什么了解事务是如何被使用的很重要。虽然它们本身可以被认为是更改的容器，但锁会一直保持到事务被提交或回滚，并且它们可以阻止撤消日志被清除。锁和大量撤消日志都会影响查询的性能，即使它们不是在导致大量锁或大量撤消日志的事务中执行的。锁使用来自缓冲池的内存，因此可用于缓存数据和索引的内存较少。根据历史列表长度来衡量，大量的撤销日志意味着在 InnoDB 执行语句时必须考虑更多的行版本。
最后，讨论了组提交的概念。当在每次提交时将更改刷新到磁盘时，可以使用组提交特性通过一起完成几次提交来减少fsync()调用的数量。组复制的一个好的副作用是它可以提高并行复制的LOGICAL_CLOCK算法的性能。
在本章中，我们多次提到了事务隔离级别的概念。下一章将更详细地介绍四个受支持的事务隔离级别是如何工作的，以及每个级别如何影响锁定。
十二、事务隔离级别
在前一章中，你学习了隔离是事务的一个重要属性。事实证明，要回答两个事务是否被隔离并不那么简单，因为答案取决于所需的隔离程度。隔离程度是通过事务隔离级别定义的。
InnoDB 支持 SQL:1992 标准 1 定义的四个事务隔离级别，它们的隔离程度依次为:SERIALIZABLE、REPEATABLE READ、READ COMMITTED和READ UNCOMMITTED。可重复读取事务隔离级别是默认的。本章将逐一介绍这些隔离级别，并讨论它们是如何工作的以及它们对锁定的影响。
为了比较在不同的事务隔离级别中更新行时使用的锁，将使用一个更新斯洛伐克布拉迪斯拉发区城市的示例。在斯洛伐克的world.city表格中有三个城市:
mysql> SELECT ID, Name, District
FROM world.city
WHERE CountryCode = 'SVK';
+------+------------+--------------------+
| ID | Name | District |
+------+------------+--------------------+
| 3209 | Bratislava | Bratislava |
| 3210 | Košice | Východné Slovensko |
| 3211 | Prešov | Východné Slovensko |
+------+------------+--------------------+
3 rows in set (0.0032 sec)
UPDATE语句可以使用CountryCode上的索引将搜索范围缩小到三个城市，然后使用District上的非索引过滤器来查找与该地区匹配的城市。这将有助于暴露SERIALIZABLE、REPEATABLE READ和READ COMMITTED事务隔离级别的不同数量的锁。另外，对于SERIALIZABLE和REPEATBLE READ，将使用使用SELECT语句的测试。
在每个示例之后，您需要回滚事务以将数据库返回到其原始状态:
-- Connection 1
Connection 1> ROLLBACK;
Query OK, 0 rows affected (0.3022 sec)
也就是说，您已经准备好探索四个事务隔离级别了。
可序列化
SERIALIZABLE隔离级别是最严格的。除了启用了autocommit的SELECT语句(并且没有启动显式事务)之外，所有语句都使用锁。对于SELECT报表，这相当于添加了FOR SHARE。这确保了事务的所有方面都是可重复的，但也意味着事务隔离级别占用了大多数锁。清单 12-1 展示了一个由SELECT语句获取的锁的例子。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 967 1560 6
-- 2 968 1561 6
-- Connection 1
Connection 1> SET transaction_isolation = 'SERIALIZABLE';
Query OK, 0 rows affected (0.0007 sec)
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0003 sec)
Connection 1> SELECT ID, Name, Population
FROM world.city
WHERE CountryCode = 'SVK'
AND District = 'Bratislava';
+------+------------+------------+
| ID | Name | Population |
+------+------------+------------+
| 3209 | Bratislava | 448292 |
+------+------------+------------+
1 row in set (0.0006 sec)
-- Connection 2
Connection 2> SELECT index_name, lock_type,
lock_mode, lock_data
FROM performance_schema.data_locks
WHERE object_schema = 'world'
AND object_name = 'city'
AND lock_type = 'RECORD'
AND thread_id = 1560
ORDER BY index_name, lock_data DESC;
+-------------+-----------+---------------+-------------+
| index_name | lock_type | lock_mode | lock_data |
+-------------+-----------+---------------+-------------+
| CountryCode | RECORD | S,GAP | 'SVN', 3212 |
| CountryCode | RECORD | S | 'SVK', 3211 |
| CountryCode | RECORD | S | 'SVK', 3210 |
| CountryCode | RECORD | S | 'SVK', 3209 |
| PRIMARY | RECORD | S,REC_NOT_GAP | 3211 |
| PRIMARY | RECORD | S,REC_NOT_GAP | 3210 |
| PRIMARY | RECORD | S,REC_NOT_GAP | 3209 |
+-------------+-----------+---------------+-------------+
7 rows in set (0.0009 sec)
-- Connection 1
Connection 1> ROLLBACK;
Query OK, 0 rows affected (0.0003 sec)
Listing 12-1Read locking in the SERIALIZABLE transaction isolation level
该查询使用辅助索引，并最终锁定所有被读取记录的主键和CountryCode记录。此外，在斯洛伐克的最后一个索引记录之后有一个缺口锁定。所有的锁都是共享锁。
清单 12-2 考虑使用一个UPDATE语句来更新被检查行的子集。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 971 1567 6
-- 2 972 1568 6
-- Connection 1
Connection 1> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Query OK, 0 rows affected (0.0004 sec)
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0002 sec)
Connection 1> UPDATE world.city
SET Population = Population * 1.10
WHERE CountryCode = 'SVK'
AND District = 'Bratislava';
Query OK, 1 row affected (0.0006 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- Connection 2
Connection 2> SELECT index_name, lock_type,
lock_mode, lock_data
FROM performance_schema.data_locks
WHERE object_schema = 'world'
AND object_name = 'city'
AND lock_type = 'RECORD'
AND thread_id = 1567
ORDER BY index_name, lock_data DESC;
+-------------+-----------+---------------+-------------+
| index_name | lock_type | lock_mode | lock_data |
+-------------+-----------+---------------+-------------+
| CountryCode | RECORD | X,GAP | 'SVN', 3212 |
| CountryCode | RECORD | X | 'SVK', 3211 |
| CountryCode | RECORD | X | 'SVK', 3210 |
| CountryCode | RECORD | X | 'SVK', 3209 |
| PRIMARY | RECORD | X,REC_NOT_GAP | 3211 |
| PRIMARY | RECORD | X,REC_NOT_GAP | 3210 |
| PRIMARY | RECORD | X,REC_NOT_GAP | 3209 |
+-------------+-----------+---------------+-------------+
7 rows in set (0.0007 sec)
-- Connection 1
Connection 1> ROLLBACK;
Query OK, 0 rows affected (0.0576 sec)
Listing 12-2Locking in the SERIALIZABLE transaction isolation level
该语句更新一个城市(用ID = 3209)，但是在主键和CountryCode索引上持有所有三个斯洛伐克城市的锁，并且在最后一个索引记录之后持有一个间隙锁。
还要注意这里如何使用SET TRANSACTION语句来设置事务隔离级别，而不是设置transaction_isolation变量。这两种方法可以互换，尽管SET SESION TRANSACTION也支持设置它是只读还是读写事务。如果您使用既没有GLOBAL也没有SESSION作用域的SET TRANSACTION语句，那么它只适用于下一个事务。
这个事务隔离级别不经常使用，但是在研究锁定问题或处理 XA 事务时会很有用。除了使用锁的SELECT语句之外，隔离级别与接下来讨论的REPEATABLE READ相同。
可重复读
REPETABLE READ隔离级别是 InnoDB 中的默认设置。顾名思义，它确保了如果您重复一个 read 语句，那么将返回相同的结果。这也称为一致读取，它是通过称为快照的读取视图实现的。快照是在事务中执行第一条语句时建立的，或者如果用START TRANSACTION给定了WITH CONSISTENT SNAPSHOT修饰符，则在事务开始时建立。
一致快照的一个重要副作用是，可以进行非锁定读取，同时仍然可以重复检索相同的数据。这扩展到包括所有 InnoDB 表，因此对不同的表执行多个语句会返回对应于同一时间点的数据。具有一致快照的REPEATABLE READ事务隔离级别也允许使用mysqlpump和mysqldump等工具创建在线一致逻辑备份。
虽然REPEATABLE READ有一些很好的隔离属性，但不像SERIALIZABLE那样具有侵入性，仍然有很高的锁定级别。清单 12-3 显示了在斯洛伐克布拉迪斯拉发地区选择城市的例子。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 973 1571 6
-- 2 974 1572 6
-- Connection 1
Connection 1> SET transaction_isolation = 'REPEATABLE-READ';
Query OK, 0 rows affected (0.0004 sec)
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0003 sec)
Connection 1> SELECT ID, Name, Population
FROM world.city
WHERE CountryCode = 'SVK'
AND District = 'Bratislava';
+------+------------+------------+
| ID | Name | Population |
+------+------------+------------+
| 3209 | Bratislava | 448292 |
+------+------------+------------+
1 row in set (0.0006 sec)
-- Connection 2
Connection 2> SELECT index_name, lock_type,
lock_mode, lock_data
FROM performance_schema.data_locks
WHERE object_schema = 'world'
AND object_name = 'city'
AND lock_type = 'RECORD'
AND thread_id = 1571
ORDER BY index_name, lock_data DESC;
0 rows in set (0.0006 sec)
-- Connection 1
Connection 1> ROLLBACK;
Query OK, 0 rows affected (0.0004 sec)
Listing 12-3Read locking in the REPEATABLE READ transaction isolation level
在这种情况下，不持有锁，这是SERIALIZABLE和REPEATABLE READ事务隔离级别之间的重要区别。清单 12-4 展示了它如何寻找UPDATE语句。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 975 1574 6
-- 2 976 1575 6
-- Connection 1
Connection 1> SET transaction_isolation = 'REPEATABLE-READ';
Query OK, 0 rows affected (0.0004 sec)
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0002 sec)
Connection 1> UPDATE world.city
SET Population = Population * 1.10
WHERE CountryCode = 'SVK'
AND District = 'Bratislava';
Query OK, 1 row affected (0.0007 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- Connection 2
Connection 2> SELECT index_name, lock_type,
lock_mode, lock_data
FROM performance_schema.data_locks
WHERE object_schema = 'world'
AND object_name = 'city'
AND lock_type = 'RECORD'
AND thread_id = 1574
ORDER BY index_name, lock_data DESC;
+-------------+-----------+---------------+-------------+
| index_name | lock_type | lock_mode | lock_data |
+-------------+-----------+---------------+-------------+
| CountryCode | RECORD | X,GAP | 'SVN', 3212 |
| CountryCode | RECORD | X | 'SVK', 3211 |
| CountryCode | RECORD | X | 'SVK', 3210 |
| CountryCode | RECORD | X | 'SVK', 3209 |
| PRIMARY | RECORD | X,REC_NOT_GAP | 3211 |
| PRIMARY | RECORD | X,REC_NOT_GAP | 3210 |
| PRIMARY | RECORD | X,REC_NOT_GAP | 3209 |
+-------------+-----------+---------------+-------------+
7 rows in set (0.0010 sec)
-- Connection 1
Connection 1> ROLLBACK;
Query OK, 0 rows affected (0.3036 sec)
Listing 12-4Locking in the REPEATABLE READ transaction isolation level
这七把锁与SERIALIZABLE中的锁是一样的。
对于一致快照，您需要注意的一个重要警告是，它们仅适用于读取。这意味着，如果您从表中读取数据，然后另一个事务提交对行的更改，以便它们与第一个事务中使用的过滤器相匹配，那么第一个事务将能够修改这些行，然后，它们将被包括在快照中。清单 12-5 显示了一个这样的例子。如果您想查看持有的锁，可以进行两种调查。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 977 1578 6
-- 2 978 1579 6
-- Connection 1
Connection 1> SET transaction_isolation = 'REPEATABLE-READ';
Query OK, 0 rows affected (0.0005 sec)
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0004 sec)
Connection 1> SELECT ID, Name, Population
FROM world.city
WHERE CountryCOde = 'BHS';
+-----+--------+------------+
| ID | Name | Population |
+-----+--------+------------+
| 148 | Nassau | 172000 |
+-----+--------+------------+
1 row in set (0.0014 sec)
-- Connection 2
Connection 2> START TRANSACTION;
Query OK, 0 rows affected (0.0004 sec)
Connection 2> INSERT INTO world.city
VALUES (4080, 'Freeport', 'BHS',
'Grand Bahama', 50000);
Query OK, 1 row affected (0.0022 sec)
Connection 2> COMMIT;
Query OK, 0 rows affected (0.0983 sec)
-- Connection 1
Connection 1> SELECT ID, Name, Population
FROM world.city
WHERE CountryCOde = 'BHS';
+-----+--------+------------+
| ID | Name | Population |
+-----+--------+------------+
| 148 | Nassau | 172000 |
+-----+--------+------------+
1 row in set (0.0006 sec)
Connection 1> UPDATE world.city
SET Population = Population * 1.10
WHERE CountryCOde = 'BHS';
Query OK, 2 rows affected (0.0012 sec)
Rows matched: 2 Changed: 2 Warnings: 0
Connection 1> SELECT ID, Name, Population
FROM world.city
WHERE CountryCOde = 'BHS';
+------+----------+------------+
| ID | Name | Population |
+------+----------+------------+
| 148 | Nassau | 189200 |
| 4080 | Freeport | 55000 |
+------+----------+------------+
2 rows in set (0.0006 sec)
Listing 12-5Consistent reads mixed with DML
当 Connection 1 第一次查询巴哈马的所有城市(CountryCode = 'BHS')时，只返回拿骚市。然后，连接 2 为自由港市插入一行，并提交其事务。当 Connection 1 重复它的SELECT语句时，仍然只返回 Nassau。目前为止一切顺利。这是对一致读取功能的期望。然而，当 Connection 1 将巴哈马所有城市的人口增加 10%时，有两行被更新，随后的SELECT显示 Freeport 现在是 Connection 1 的 read 视图的一部分。
这种行为不是 bug！这是因为在REPEATABLE READ事务隔离级别中读取是非锁定的。如果想要避免这种行为，要么需要使用FOR SHARE子句显式请求 Connection 1 中的共享锁，要么需要更改为SERIALIZABLE事务隔离级别。
一致读取的代价是相对大量的锁，并且 InnoDB 必须维护数据的多个版本。请记住，从对撤消日志的讨论中可以看出，只要在给定的事务之前还有一个已启动的读视图，那么就必须保留事务的撤消日志，并且跟踪相同行的版本的成本很高。
如果您不需要一致的读取，那么READ COMMITTED事务隔离级别是一个不错的选择。
已提交读取
如果您习惯于 Oracle DB 或 PostgreSQL 等其他关系数据库系统，那么您可能一直在使用READ COMMITTED事务隔离级别。MySQL 中的NDBCluster存储引擎也使用了READ COMMITTED。这种隔离级别很受欢迎，因为它为许多工作负载提供了足够强的隔离，并且与REPEATABLE READ和SERIALIZABLE隔离级别相比，它减少了锁定。
从REPEATABLE READ到READ COMMITTED的主要区别是
-
READ COMMITTED不支持一致读取(尽管单个语句仍然返回一致的结果)。因为读取视图的生命周期只有语句的生命周期，所以 InnoDB 可以更快地清除旧的撤销日志。这一优势对于长时间运行的事务最为显著。 -
一旦评估了
WHERE子句，DML 语句对已检查但未修改的记录所采取的锁就会被释放。 -
READ COMMITTED将仅在检查外键和唯一键约束以及分页时使用间隙锁。当 InnoDB 页面接近满，必须在页面中间插入一条记录，或者现有记录增长，因此页面中不再有空间时，就会发生页面分割。 -
对于使用非索引列解析的
WHERE子句，半一致读取功能允许事务使用行的最后提交值来匹配过滤器，即使该行被锁定。
缺少间隙锁意味着可能会出现所谓的幻像行。当同一个语句在同一个事务中执行两次时，会出现幻像行，即使对于像SELECT ... FOR SHARE这样的锁定语句，也会返回不同的行。
The Illusive Gap Lock
在 MySQL 5.7 和 pre-GA MySQL 8 中，出现了一个更加困难的 MySQL bug。在复制设置中使用 XA 事务时，副本上会随机出现仅由复制流量引起的锁定等待超时和死锁。当复制源上没有任何锁定问题时，这怎么可能呢？
这期杂志由几部分组成。首先，在 MySQL 5.7 和更高版本中，XA 事务在准备时被写入二进制日志，并且它们可能不会按照准备时的顺序提交，这意味着即使在单线程复制中，副本也可能同时打开多个写事务。
其次，这个问题主要是通过实施基于行的复制和始终使用READ COMMITTED事务隔离级别来解决的。然而，非常令人困惑的是，偶尔——看似随机——副本上仍然会有锁冲突。最终证明是页面分割导致的间隙锁才是罪魁祸首。源和副本上不会同时发生页拆分，因此副本上可能会有额外的锁。在 5.7.22 和 8.0.4 版本中，通过在 XA 事务到达准备阶段时释放复制线程获取的间隙锁，最终解决了这个问题。
如果您尝试在READ COMMITTED事务隔离级别中使用循环的UPDATE语句示例，您将会看到它如何比以前使用更少的锁。如清单 12-6 所示。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 980 1582 6
-- 2 981 1583 6
-- Connection 1
Connection 1> SET transaction_isolation = 'READ-COMMITTED';
Query OK, 0 rows affected (0.0002 sec)
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0002 sec)
Connection 1> UPDATE world.city
SET Population = Population * 1.10
WHERE CountryCode = 'SVK'
AND District = 'Bratislava';
Query OK, 1 row affected (0.0007 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- Connection 2
Connection 2> SELECT index_name, lock_type,
lock_mode, lock_data
FROM performance_schema.data_locks
WHERE object_schema = 'world'
AND object_name = 'city'
AND lock_type = 'RECORD'
AND thread_id = 1582
ORDER BY index_name, lock_data DESC;
+-------------+-----------+---------------+-------------+
| index_name | lock_type | lock_mode | lock_data |
+-------------+-----------+---------------+-------------+
| CountryCode | RECORD | X,REC_NOT_GAP | 'SVK', 3209 |
| PRIMARY | RECORD | X,REC_NOT_GAP | 3209 |
+-------------+-----------+---------------+-------------+
2 rows in set (0.0008 sec)
-- Connection 1
Connection 1> ROLLBACK;
Query OK, 0 rows affected (0.0754 sec)
Listing 12-6Locking in the READ COMMITTED transaction isolation level
鉴于SERIALIZABLE和REPEATABLE READ隔离级别持有七个记录和间隙锁，READ COMMITTED只持有一个CountryCode索引锁和一个主键锁——虽然有一段时间，被检查的其他索引和行记录都持有锁，但它们在输出时又被释放了。这大大降低了锁等待和死锁的可能性。
READ COMMITTED隔离级别的一个不太为人所知的特性是半一致读取，它允许语句使用列的最后提交值来与它的WHERE子句进行比较。如果确定该行不受该语句的影响，则即使另一个事务持有锁，也不会发生锁冲突。如果行将被更新，条件将被重新评估，并且一个锁防止冲突的改变。清单 12-7 显示了一个这样的例子。如果您想查看持有的锁，可以进行两种调查。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 986 1592 6
-- 2 987 1593 6
-- Connection 1
Connection 1> SET transaction_isolation = 'READ-COMMITTED';
Query OK, 0 rows affected (0.0004 sec)
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0004 sec)
Connection 1> UPDATE world.city
SET Population = Population * 1.10
WHERE Name = 'San Jose'
AND District = 'Southern Tagalog';
Query OK, 1 row affected (0.0106 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- Connection 2
Connection 2> SET transaction_isolation = 'READ-COMMITTED';
Query OK, 0 rows affected (0.0004 sec)
Connection 2> START TRANSACTION;
Query OK, 0 rows affected (0.0065 sec)
Connection 2> UPDATE world.city
SET Population = Population * 1.10
WHERE Name = 'San Jose'
AND District = 'Central Luzon';
Query OK, 1 row affected (0.0060 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Listing 12-7READ COMMITTED Semi-consistent reads
两个事务都更新名为 San Jose 的城市，但是在不同的地区。无论是Name还是District列都没有被索引。即使第二个事务使用Name = 'San Jose' AND District = 'Southern Tagalog'检查了行，也没有锁冲突，因为基于地区的事务决定它不会更新行。但是，如果将索引添加到Name列，那么将会出现锁冲突，因此该特性目前对于大型表的价值有限。
从这次讨论中，你可能会得到这样的印象:READ COMMITTED总是比REPEATABLE READ表现得更好。这是最合乎逻辑的结论，然而事情往往比这更复杂。需要注意的是，为了启动一个读视图，需要使用trx_sys互斥体(wait/synch/mutex/innodb/trx_sys_mutex性能模式工具),并且由于READ COMMITTED为每条语句启动了一个新的读视图，它将比REPEATABLE READ更频繁地获取trx_sys互斥体。最后，如果你有很多快速的事务和语句，你可能会在READ COMMITTED中结束在互斥体上的大量活动，这将成为一个严重的瓶颈，你最好使用REPEATABLE READ。对于长期运行的事务和报表，天平向READ COMMITTED倾斜。
Tip:
如果你有兴趣看一些显示trx_sys互斥体如何影响性能的基准测试，请看 http://dimitrik.free.fr/blog/archives/2015/02/mysql-performance-impact-of-innodb-transaction-isolation-modes-in-mysql-57.html 。
未提交读取
最后一个事务隔离级别是READ UNCOMMITTED。顾名思义，使用此隔离级别的事务被允许读取尚未提交的数据；这也叫做脏读。这听起来很危险，而且在大多数情况下，这是绝对不可行的。然而，在一些特殊情况下，它可能是有用的。除了脏读之外，行为与READ COMMITTED相同。READ UNCOMMITTED相对于READ COMMITTED的主要优势是 InnoDB 永远不需要保存多个版本的数据来完成查询。
READ UNCOMMITTED的主要用途是在只需要近似值的情况下，用于批量插入，以及用于您想看一眼另一个事务做了什么更改的调查。只需要近似值的一个例子是 InnoDB 使用READ UNCOMMITTED计算索引统计。对于批量插入，MySQL Shell 的并行表数据导入特性(JavaScript 中的util.importTable()或 Python 中的util.import_table())会在批量加载期间切换到READ UNCOMMITTED。
摘要
本章研究了 InnoDB 支持的四个事务隔离级别。最严格的隔离级别是SERIALIZABLE，它为除自动提交单语句SELECT事务之外的所有语句加锁。REPEATABLE READ隔离级别支持非锁定读取，但支持一致读取。有一个问题是，如果事务更新了在 read 视图建立后提交的行，那么更新的行将被添加到视图中。
下一个级别是READ COMMITTED，它放弃一致读取，并且总是包括所有提交的行。它确实为幻像行打开了大门，但是另一方面，READ COMMITTED需要更少的锁，并且持有它们的时间更短，这样就大大降低了锁冲突的可能性。最后一个隔离级别是READ UNCOMMITTED，其行为类似于READ COMMITTED，但允许脏读，即读取尚未提交的更改。
这就结束了本书的理论部分。剩下的几章通过六个案例研究，第一章分析了一个涉及冲水锁的问题。
Footnotes [1](#Fn1_source)http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt——如果你有兴趣购买副本，参见例如 https://modern-sql.com/standard 获取链接。
十三、案例研究：清空锁
锁定问题是性能问题的常见原因之一，其影响可能非常严重。在最坏的情况下，查询可能会失败，连接会堆积起来，因此无法建立新的连接。因此，了解如何调查锁定问题并修复这些问题非常重要。
本章和后面的章节将讨论六类锁问题:
-
清空锁
-
元数据和模式锁
-
记录级锁，包括间隙锁
-
僵局
-
外键
-
旗语
除了外键案例研究之外，每一类锁都使用不同的技术来确定锁争用的原因。当您阅读示例时，您应该记住，可以使用类似的技术来调查与示例不完全匹配的锁问题。对于前四个案例研究(第 13 至 16 章)，讨论分为六个部分:
-
**症状:**这些症状使您能够识别所遇到的锁问题的种类。
-
**设置:**如果你想亲自尝试，这包括设置锁定问题的步骤。因为锁争用需要多个连接，所以提示符，例如
Connection 1>，用于告诉哪个连接应该用于哪个语句。如果您希望在调查过程中获得的信息不会比在真实案例中获得的更多，那么您可以跳过这一部分，在完成调查后再回头查看。 -
**解决方案:**如何解决即时锁定问题，从而最大限度地减少由此导致的停机。
-
**预防:**讨论如何减少遇到问题的机会。这与第 9 章中关于减少锁定问题的讨论密切相关。
外键和信号量的最后两个案例研究遵循类似的模式。
说够了，首先要讨论的锁类别是刷新锁，这是最难研究的锁问题之一。
症状
flush lock 问题的主要症状是数据库陷入停滞，所有使用部分或全部表的新查询都要等待 flush lock。要寻找的迹象包括:
-
新查询的查询状态是“等待表刷新”这可能发生在所有新查询中，也可能只发生在访问特定表的查询中。
-
越来越多的连接被创建。
-
最终，由于 MySQL 失去连接，新的连接会失败。新连接收到的错误为
ER_CON_COUNT_ERROR:ERROR 1040 (HY000): Too many connections。(在 8.0.19 或更早版本中使用 X 协议时，错误为MySQL Error 5011: Could not open session。) -
至少有一个查询的运行时间晚于最早的刷新锁请求。
-
进程列表中可能会有一个
FLUSH TABLES语句，但并不总是这样。 -
当
FLUSH TABLES语句等待lock_wait_timeout时，出现ER_LOCK_WAIT_TIMEOUT错误:ERROR: 1205: Lock wait timeout exceeded; try restarting transaction。因为lock_wait_timeout的默认值是 365 天，所以只有在超时时间减少的情况下，这种情况才有可能发生。 -
如果您使用默认模式集连接到
mysql命令行客户端，那么在您到达提示符之前，连接可能会挂起。如果在连接打开的情况下更改默认模式，也会发生同样的情况。提示如果您使用
-A选项启动客户端，禁用收集自动完成信息，则不会出现mysql命令行客户端阻塞的问题。更好的解决方案是使用 MySQL Shell，它以一种不会因刷新锁而阻塞的方式获取自动完成信息。
如果您看到这些症状，是时候了解是什么导致了锁定问题。
原因
当一个连接请求刷新一个表时，它要求关闭对该表的所有引用，这意味着没有活动查询可以使用该表。因此，当刷新请求到达时，它必须等待所有使用要刷新的表的查询完成。请注意，除非您明确指定要刷新哪些表，否则必须完成的只是查询，而不是整个事务。显然，所有表都被刷新的情况是最严重的，例如由于FLUSH TABLES WITH READ LOCK，因为这意味着所有活动查询必须在 flush 语句可以继续之前完成。
当等待刷新锁成为一个问题时，这意味着有一个或多个查询阻止了FLUSH TABLES语句获得刷新锁。由于FLUSH TABLES语句需要一个排他锁，因此它会阻止后续查询获取它们需要的共享锁。
在备份过程需要刷新所有表并获得读锁以创建一致备份的情况下，此问题经常出现。
当FLUSH TABLES语句超时或被终止，但后续查询没有继续进行时，可能会出现一种特殊情况。出现这种情况是因为低级表定义缓存(TDC)版本锁没有被释放。这种情况可能会引起混淆，因为后续查询仍在等待表刷新的原因并不明显。当一个ANALYZE TABLE语句触发被分析的一个或多个表的隐式刷新时，也会发生类似的情况。
设置
将要调查的锁定情况涉及三个连接(不包括用于调查的连接)。第一个连接执行慢速查询，第二个连接使用读锁刷新所有表，最后一个连接执行快速查询。这些语句如清单 13-1 所示。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 668 1106 6
-- 2 669 1107 6
-- 3 670 1108 6
-- Connection 1
Connection 1> SELECT city.*, SLEEP(3600) FROM world.city WHERE ID = 130;
-- Connection 2
Connection 2> FLUSH TABLES WITH READ LOCK;
-- Connection 3
Connection 3> SELECT * FROM world.city WHERE ID = 3805;
Listing 13-1Triggering flush lock contention
在第一个查询中使用SLEEP(3600)意味着您有一个小时(3600 秒)来执行另外两个查询并执行调查。如果您想停止锁定情况，您可以终止查询:
-- Investigation #6
-- Connection 4
Connection 4> KILL 668;
Query OK, 0 rows affected (0.0004 sec)
你现在可以开始调查了。
调查
对刷新锁的调查要求您查看实例上运行的查询列表。与其他锁争用不同，没有性能模式表或 InnoDB monitor 报告可用于直接查询阻塞查询。
清单 13-2 显示了使用sys.session视图的输出示例。使用获取查询列表的替代方法将产生类似的结果。线程和连接 id 以及语句延迟会有所不同。
-- Investigation #1
-- Connection 4
Connection 4> SELECT thd_id, conn_id, state,
current_statement,
statement_latency
FROM sys.session
WHERE command = 'Query'\G
*************************** 1\. row ***************************
thd_id: 1106
conn_id: 668
state: User sleep
current_statement: SELECT city.*, SLEEP(3600) FROM world.city WHERE ID = 130
statement_latency: 1.48 min
*************************** 2\. row ***************************
thd_id: 1107
conn_id: 669
state: Waiting for table flush
current_statement: FLUSH TABLES WITH READ LOCK
statement_latency: 1.44 min
*************************** 3\. row ***************************
thd_id: 1108
conn_id: 670
state: Waiting for table flush
current_statement: SELECT * FROM world.city WHERE ID = 3805
statement_latency: 1.41 min
*************************** 4\. row ***************************
thd_id: 1105
conn_id: 667
state: NULL
current_statement: SELECT thd_id, conn_id, state, ... on WHERE command = 'Query'
statement_latency: 40.63 ms
4 rows in set (0.0419 sec)
Listing 13-2Investigating flush lock contention using sys.session
输出中有四个查询。默认情况下，sys.session和sys.processlist视图根据执行时间以降序对查询进行排序。这使得调查类似围绕刷新锁的争用这样的问题变得容易，在查找原因时，查询时间是要考虑的主要因素。
您开始寻找FLUSH TABLES语句(稍后将讨论没有FLUSH TABLES语句的情况)。在这种情况下，那就是thd_id = 1107(第二排)。注意，FLUSH语句的状态是“等待表刷新”然后查找已经运行了较长时间的查询。在这种情况下，只有一个查询:带有thd_id = 1106的查询。这是阻止FLUSH TABLES WITH READ LOCK完成的查询。通常，可能有不止一个查询。
剩下的两个查询是被FLUSH TABLES WITH READ LOCK阻塞的查询和获取输出的查询。前三个查询一起构成了一个长时间运行的查询阻塞一个FLUSH TABLES语句的典型例子，该语句又阻塞了其他查询。
您还可以从 MySQL Workbench 获取进程列表，在某些情况下，还可以从您的监控解决方案中获取(MySQL Enterprise Monitor 就是一个例子)。在 MySQL Workbench 中，您可以通过选择导航器中的管理选项卡来使用客户端连接报告，如图 13-1 所示。
图 13-1
导航到客户端连接报告
您可以通过在管理部分选择客户端连接来打开报告。
该报告从performance_schema.threads表中获取进程信息，用performance_schema.session_connect_attrs表上的LEFT JOIN获取程序名。您可以选择是否过滤掉后台线程以及休眠连接，MySQL Workbench 允许您更改排序，而无需重新执行生成报告的语句。或者，您也可以刷新报告。图 13-2 显示了本案例研究的一个例子。
图 13-2
显示 MySQL Workbench 中的客户端连接
您不能选择要包括哪些列，并且为了使文本可读，屏幕截图中只包括报告的一部分。 Id 列对应sys.session输出中的conn_id，线程(靠近中间)对应thd_id。完整的截图作为figure_13_2_workbench_flush_lock.png收录在本书的 GitHub 知识库中。
类似 MySQL Workbench 和 MySQL Enterprise Monitor 中的报告的一个优点是，它们使用现有的连接来创建报告。在锁问题导致所有连接都被使用的情况下，使用监控解决方案获得查询列表是非常宝贵的。
如前所述，FLUSH TABLES语句可能并不总是出现在查询列表中。仍然有查询等待刷新表的原因是低级 TDC 版本锁。调查的原则保持不变，但它似乎令人困惑。清单 13-3 显示了这样一个例子，使用相同的设置，但是在调查之前终止了执行 flush 语句的连接(如果您交互地执行它，Ctrl+C 可以在 MySQL Shell 中的连接执行FLUSH TABLES WITH READ LOCK中使用)。
-- Investigation #7
Connection 4> KILL 669;
Query OK, 0 rows affected (0.0004 sec)
-- Investigation #1
Connection 4> SELECT thd_id, conn_id, state,
current_statement,
statement_latency
FROM sys.session
WHERE command = 'Query'\G
*************************** 1\. row ***************************
thd_id: 1106
conn_id: 668
state: User sleep
current_statement: SELECT city.*, SLEEP(3600) FROM world.city WHERE ID = 130
statement_latency: 3.88 min
*************************** 2\. row ***************************
thd_id: 1108
conn_id: 670
state: Waiting for table flush
current_statement: SELECT * FROM world.city WHERE ID = 3805
statement_latency: 3.81 min
*************************** 3\. row ***************************
thd_id: 1105
conn_id: 667
state: NULL
current_statement: SELECT thd_id, conn_id, state, ... on WHERE command = 'Query'
statement_latency: 39.53 ms
3 rows in set (0.0406 sec)
Listing 13-3Flush lock contention without a FLUSH TABLES statement
这种情况与前一种情况相同，只是没有了FLUSH TABLES语句。在这种情况下，查找等待时间最长且状态为“等待表刷新”的查询运行时间超过该查询等待时间的查询会阻止 TDC 版本锁被释放。在这种情况下，这意味着thd_id = 668是阻塞查询。
一旦您确定了问题和涉及的主要查询，您需要决定如何处理该问题。
解决方案
解决这个问题有两个层次。首先，您需要解决查询不执行的直接问题。其次，你需要努力避免将来出现这种问题。本小节将讨论即时解决方案，下一小节将考虑如何减少问题发生的几率。
要解决眼前的问题，您可以选择等待查询完成或开始终止查询。如果您可以在刷新锁争用正在进行时重定向应用以使用另一个实例，那么通过让长时间运行的查询完成，您也许能够让这种情况自行解决。如果在那些正在运行或等待的查询中有数据更改查询，在这种情况下，您确实需要考虑在所有查询完成后，它是否会使系统保持一致的状态。一种选择是以只读模式继续在不同的实例上执行读取查询。
Tip
如果长时间运行的查询是一个缺少 join 子句的流氓查询，它可能需要很长时间才能完成。这本书的作者经历了一个运行了几个月的查询。当决定是否等待时，您希望尝试估计查询将花费多长时间。一个好的选择是使用EXPLAIN FOR CONNECTION <processlist id>命令来检查长时间运行的查询的查询计划。
如果您决定终止查询，您可以尝试终止FLUSH TABLES语句。如果这行得通，这是最简单的解决方案。然而，正如所讨论的那样，这并不总是有帮助的，在这种情况下，唯一的解决方案是终止那些阻止FLUSH TABLES语句完成的查询。如果长时间运行的查询看起来像失控的查询，并且执行它们的应用/客户端不再等待它们，那么您可能想要杀死它们，而不是试图首先杀死FLUSH TABLES语句。
在终止查询时，一个重要的考虑因素是有多少数据被更改。对于一个纯粹的SELECT查询(不涉及存储的例程)，那总是没什么，从所做工作的角度来看，杀死它是安全的。然而，对于INSERT、UPDATE、DELETE和类似的查询，如果查询被终止，则更改的数据必须回滚。回滚更改通常比一开始就进行更改需要更长的时间，所以如果有很多更改，请准备好等待很长时间才能回滚。您可以使用information_schema.INNODB_TRX视图，通过查看trx_rows_modified列来估计完成的工作量。如果有大量工作要回滚，通常最好让查询完成。
Caution
当 DML 语句被终止时，它所做的工作必须回滚。回滚通常比创建变更花费更长的时间，有时甚至更长。如果你考虑终止一个长时间运行的 DML 语句，你需要考虑到这一点。
当然，最理想的情况是完全防止问题发生。
预防
刷新锁争用的发生是因为长时间运行的查询和一个FLUSH TABLES语句的组合。因此，为了防止这个问题，你需要看看你能做些什么来避免这两种情况同时出现。
查找、分析和处理长时间运行的查询超出了本书的范围。然而，一个特别有趣的选项是使用max_execution_time系统变量和MAX_EXECUTION_TIME(N)优化器提示为SELECT语句支持的查询设置超时，这是防止查询失控的一个好方法。一些连接器还支持超时查询。
Tip
为了避免长时间运行的SELECT查询，您可以配置max_execution_time选项或者设置MAX_EXECUTION_TIME(N)优化器提示。这将使SELECT语句在指定的时间段后超时，并有助于防止类似刷新锁等待的问题。
无法阻止某些长时间运行的查询。这可能是一项报告作业、构建缓存表或其他必须访问大量数据的任务。在这种情况下，您能做的最好的事情就是尽量避免它们运行，同时也有必要刷新表。一种选择是将长时间运行的查询安排在不同于需要刷新表的时间运行。另一种选择是让长时间运行的查询在不同于需要刷新表的作业的实例上运行。
需要刷新表的一个常见任务是进行备份。在 MySQL 8 中，可以通过使用备份和日志锁来避免这个问题。例如，MySQL Enterprise Backup (MEB)在版本 8.0.16 和更高版本中执行此操作，因此 InnoDB 表永远不会被刷新。或者，您可以在使用率较低的时段执行备份，这样潜在的冲突会更低，或者您甚至可以在系统处于只读模式时执行备份，从而完全避免FLUSH TABLES WITH READ LOCK。
摘要
本章研究了一种情况，一个长时间运行的查询阻止了一个FLUSH TABLES WITH READ LOCK语句获取刷新锁，从而阻止了以后开始执行的查询。像这样的情况是最难调查的，因为从性能模式中的锁表得不到任何帮助。相反，您需要查看进程列表，查找比FLUSH TABLES语句更早的查询，或者，如果不存在，查找等待刷新锁时间最长的连接。
在大多数情况下，您可以选择等待长时间运行的查询完成，或者终止它以解决问题。终止查询是否可接受取决于查询的目的以及事务进行了多少更改。为了防止这个问题，您可以尝试分离任务，以便长时间运行的查询和FLUSH TABLE语句不会同时执行，或者它们在不同的 MySQL 实例上执行。对于SELECT语句，您还可以使用max_execution_time选项或MAX_EXECUTION_TIME(N)优化器开关来自动终止长时间运行的查询。
另一种经常引起混淆的锁类型是元数据锁。一个涉及元数据锁定的案例研究将在下一章讨论。
十四、.案例研究：元数据和模式锁
在 MySQL 5.7 和更早的版本中，元数据锁经常是混淆的来源。问题是谁持有元数据锁并不明显。在 MySQL 5.7 中，元数据锁的检测被添加到性能模式中，而在 MySQL 8.0 中，它是默认启用的。启用该工具后，就可以很容易地确定是谁阻塞了试图获取锁的连接。本章将通过一个例子来说明元数据锁定的情况，并对其进行分析。首先讨论症状。
症状
元数据锁争用的症状类似于刷新锁争用的症状。在典型的情况下，会有一个长时间运行的查询或事务、一个等待元数据锁的 DDL 语句，并且可能会有查询堆积起来。要注意的症状如下:
-
DDL 语句和可能的其他查询都停留在“等待表元数据锁定”状态。
-
查询可能会堆积如山。等待中的查询都使用同一个表。(如果有多个表的 DDL 语句在等待元数据锁，则可能有不止一组查询在等待。)
-
当 DDL 语句已经等待
lock_wait_timeout时，出现一个ER_LOCK_WAIT_TIMEOUT错误:ERROR: 1205: Lock wait timeout exceeded; try restarting transaction。由于lock_wait_timeout的默认值是 365 天，只有在超时时间减少的情况下，这种情况才有可能发生。 -
有一个长时间运行的查询或长时间运行的事务。在后一种情况下，事务可能处于空闲状态，或者正在执行一个不使用 DDL 语句所作用的表的查询。
使这种情况变得潜在混乱的是最后一点:可能没有任何长时间运行的查询是导致锁问题的明确候选。那么，元数据锁争用的原因是什么呢？
原因
请记住，元数据锁的存在是为了保护模式定义(以及与显式锁一起使用)。只要事务处于活动状态，模式保护就会一直存在，因此当事务查询表时，元数据锁定将持续到事务结束。因此，您可能看不到任何长时间运行的查询。事实上，持有元数据锁的事务可能根本不做任何事情。
简而言之，元数据锁的存在是因为一个或多个连接可能依赖于给定表的模式不变，或者它们已经使用LOCK TABLES或FLUSH TABLES WITH READ LOCK语句显式锁定了该表。
设置
元数据锁定的示例调查使用了三个连接，就像上一章中的示例一样。第一个连接正在进行事务处理，第二个连接尝试向事务处理使用的表添加索引，第三个连接尝试对同一个表执行查询。这些查询如清单 14-1 所示。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 713 1181 6
-- 2 714 1182 6
-- 3 715 1183 6
-- Connection 1
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0003 sec)
Connection 1> SELECT * FROM world.city WHERE ID = 3805\G
*************************** 1\. row ***************************
ID: 3805
Name: San Francisco
CountryCode: USA
District: California
Population: 776733
1 row in set (0.0007 sec)
Connection 1> SELECT Code, Name FROM world.country WHERE Code = 'USA'\G
*************************** 1\. row ***************************
Code: USA
Name: United States
1 row in set (0.0005 sec)
-- Connection 2
Connection 2> ALTER TABLE world.city ADD INDEX (Name);
-- Connection 3
Connection 3> SELECT * FROM world.city WHERE ID = 130;
Listing 14-1Triggering metadata lock contention
此时，可以开始调查了。这种情况不会自行解决(除非你的lock_wait_timeout值很低，或者你准备等一年)，所以你有足够的时间。当您想要解决阻塞时，您可以开始终止连接 2 中的ALTER TABLE语句，以避免修改世界。城市餐桌。然后提交或回滚连接 1 中的事务。
调查
如果启用了wait/lock/metadata/sql/mdl性能模式工具(MySQL 8 中的默认设置)，那么调查元数据锁定问题就很简单了。您可以使用性能模式中的metadata_locks表来列出授予的和挂起的锁。然而，获得锁情况摘要的一个更简单的方法是使用 sys 模式中的schema_table_lock_waits视图。
作为一个例子，考虑在清单 14-2 中可以看到的元数据锁定等待问题，其中涉及三个连接。选择了WHERE子句，以便只包含该调查感兴趣的行。
-- Investigation #1
-- Connection 4
Connection 4> SELECT thd_id, conn_id, state,
current_statement,
statement_latency
FROM sys.session
WHERE command = 'Query' OR trx_state = 'ACTIVE'\G
*************************** 1\. row ***************************
thd_id: 1181
conn_id: 713
state: NULL
current_statement: SELECT Code, Name FROM world.country WHERE Code = 'USA'
statement_latency: NULL
*************************** 2\. row ***************************
thd_id: 1182
conn_id: 714
state: Waiting for table metadata lock
current_statement: ALTER TABLE world.city ADD INDEX (Name)
statement_latency: 26.68 s
*************************** 3\. row ***************************
thd_id: 1183
conn_id: 715
state: Waiting for table metadata lock
current_statement: SELECT * FROM world.city WHERE ID = 130
statement_latency: 24.68 s
*************************** 4\. row ***************************
thd_id: 1180
conn_id: 712
state: NULL
current_statement: SET @sys.statement_truncate_le ... ('statement_truncate_len', 64)
statement_latency: 50.42 ms
4 rows in set (0.0530 sec)
Listing 14-2A metadata lock wait issue
两个连接正在等待元数据锁定(在world.city表上)。还包括第三个连接(conn_id = 713)，它是空闲的，可以从语句延迟的NULL中看到(在 8.0.18 之前和 8.0.21 之后的一些版本中，您可能还会看到当前语句是NULL)。在这种情况下，查询列表仅限于具有活动查询或活动事务的查询，但通常您会从完整的进程列表开始。然而，为了便于关注重要的部分，输出被过滤。
一旦您知道存在元数据锁定问题，您可以使用sys.schema_table_lock_waits视图来获取关于锁定争用的信息。清单 14-3 显示了与刚才讨论的过程列表相对应的输出示例。
-- Investigation #3
Connection 4> SELECT *
FROM sys.schema_table_lock_waits\G
*************************** 1\. row ***************************
object_schema: world
object_name: city
waiting_thread_id: 1182
waiting_pid: 714
waiting_account: root@localhost
waiting_lock_type: EXCLUSIVE
waiting_lock_duration: TRANSACTION
waiting_query: ALTER TABLE world.city ADD INDEX (Name)
waiting_query_secs: 128
waiting_query_rows_affected: 0
waiting_query_rows_examined: 0
blocking_thread_id: 1181
blocking_pid: 713
blocking_account: root@localhost
blocking_lock_type: SHARED_READ
blocking_lock_duration: TRANSACTION
sql_kill_blocking_query: KILL QUERY 713
sql_kill_blocking_connection: KILL 713
*************************** 2\. row ***************************
object_schema: world
object_name: city
waiting_thread_id: 1183
waiting_pid: 715
waiting_account: root@localhost
waiting_lock_type: SHARED_READ
waiting_lock_duration: TRANSACTION
waiting_query: SELECT * FROM world.city WHERE ID = 130
waiting_query_secs: 126
waiting_query_rows_affected: 0
waiting_query_rows_examined: 0
blocking_thread_id: 1181
blocking_pid: 713
blocking_account: root@localhost
blocking_lock_type: SHARED_READ
blocking_lock_duration: TRANSACTION
sql_kill_blocking_query: KILL QUERY 713
sql_kill_blocking_connection: KILL 713
*************************** 3\. row ***************************
object_schema: world
object_name: city
waiting_thread_id: 1182
waiting_pid: 714
waiting_account: root@localhost
waiting_lock_type: EXCLUSIVE
waiting_lock_duration: TRANSACTION
waiting_query: ALTER TABLE world.city ADD INDEX (Name)
waiting_query_secs: 128
waiting_query_rows_affected: 0
waiting_query_rows_examined: 0
blocking_thread_id: 1182
blocking_pid: 714
blocking_account: root@localhost
blocking_lock_type: SHARED_UPGRADABLE
blocking_lock_duration: TRANSACTION
sql_kill_blocking_query: KILL QUERY 714
sql_kill_blocking_connection: KILL 714
*************************** 4\. row ***************************
object_schema: world
object_name: city
waiting_thread_id: 1183
waiting_pid: 715
waiting_account: root@localhost
waiting_lock_type: SHARED_READ
waiting_lock_duration: TRANSACTION
waiting_query: SELECT * FROM world.city WHERE ID = 130
waiting_query_secs: 126
waiting_query_rows_affected: 0
waiting_query_rows_examined: 0
blocking_thread_id: 1182
blocking_pid: 714
blocking_account: root@localhost
blocking_lock_type: SHARED_UPGRADABLE
blocking_lock_duration: TRANSACTION
sql_kill_blocking_query: KILL QUERY 714
sql_kill_blocking_connection: KILL 714
4 rows in set (0.0041 sec)
Listing 14-3Finding metadata lock contention
输出显示有四种查询等待和阻塞的情况。这可能令人惊讶，但它确实发生了，因为涉及到几个锁，并且有一系列等待。每一行都是一对等待和阻塞连接。输出使用“pid”作为进程列表 id，这与早期输出中使用的连接 id 相同。这些信息包括锁是什么、等待连接的详细信息、阻塞连接的详细信息以及可用于终止阻塞查询或连接的两个查询。
第三行显示了等待自身的进程列表 id 714。这听起来像是一个僵局，但事实并非如此。原因是ALTER TABLE首先获取了一个可以升级的共享锁，然后试图获取正在等待的独占锁。因为没有关于哪个现有锁实际上阻塞了新锁的明确信息，所以该信息最终被包括在内。
第四行显示SELECT语句正在等待进程列表 id 714，即ALTER TABLE。这就是当 DDL 语句需要一个独占锁时，连接会开始堆积的原因，所以它会阻塞对共享锁的请求。
第一行和第二行揭示了锁争用的潜在问题。进程列表 id 713 阻塞了其他两个连接，这表明这是阻塞 DDL 语句的罪魁祸首。因此，当您调查类似这样的问题时，请查找正在等待被另一个连接阻塞的独占元数据锁的连接。如果输出中有大量的行，您还可以查找导致最多阻塞的连接，并以此为起点。清单 14-4 展示了一个如何做到这一点的例子。
-- Investigation #4
Connection 4> SELECT *
FROM sys.schema_table_lock_waits
WHERE waiting_lock_type = 'EXCLUSIVE'
AND waiting_pid <> blocking_pid\G
*************************** 1\. row ***************************
object_schema: world
object_name: city
waiting_thread_id: 1182
waiting_pid: 714
waiting_account: root@localhost
waiting_lock_type: EXCLUSIVE
waiting_lock_duration: TRANSACTION
waiting_query: ALTER TABLE world.city ADD INDEX (Name)
waiting_query_secs: 678
waiting_query_rows_affected: 0
waiting_query_rows_examined: 0
blocking_thread_id: 1181
blocking_pid: 713
blocking_account: root@localhost
blocking_lock_type: SHARED_READ
blocking_lock_duration: TRANSACTION
sql_kill_blocking_query: KILL QUERY 713
sql_kill_blocking_connection: KILL 713
1 row in set (0.0025 sec)
-- Investigation #5
Connection 4> SELECT blocking_pid, COUNT(*)
FROM sys.schema_table_lock_waits
WHERE waiting_pid <> blocking_pid
GROUP BY blocking_pid
ORDER BY COUNT(*) DESC;
+--------------+----------+
| blocking_pid | COUNT(*) |
+--------------+----------+
| 713 | 2 |
| 714 | 1 |
+--------------+----------+
2 rows in set (0.0023 sec)
Listing 14-4Looking for the connection causing the metadata lock block
第一个查询寻找对独占元数据锁的等待，其中阻塞进程列表 id 不是它本身。在这种情况下，这会立即导致主块争用。第二个查询确定每个进程列表 id 触发的阻塞查询的数量。这可能不像这个例子中显示的那么简单，但是使用这里显示的查询将有助于缩小锁争用的范围。
一旦确定了锁争用的来源，就需要确定事务正在做什么。在这种情况下，锁争用的根源是进程列表 id 为 713 的连接。回到进程列表输出，您可以看到在这种情况下它没有做任何事情:
*************************** 1\. row ***************************
thd_id: 1181
conn_id: 713
state: NULL
current_statement: SELECT Code, Name FROM world.country WHERE Code = 'USA'
statement_latency: NULL
这个连接做了什么来获取元数据锁？没有涉及world.city表的当前语句这一事实表明该连接有一个活动的事务打开。在这种情况下，事务是空闲的(如statement_latency = NULL所示)，但也可能有一个与world.city表上的元数据锁无关的查询正在执行。无论哪种情况，您都需要确定事务在当前状态之前正在做什么。为此，您可以使用性能模式和信息模式。清单 14-5 展示了一个调查事务状态和最近历史的例子。
-- Investigation #6
Connection 4> SELECT *
FROM information_schema.INNODB_TRX
WHERE trx_mysql_thread_id = 713\G
*************************** 1\. row ***************************
trx_id: 284186648310752
trx_state: RUNNING
trx_started: 2020-08-06 19:57:33
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 0
trx_mysql_thread_id: 713
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 0
trx_lock_memory_bytes: 1136
trx_rows_locked: 0
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
trx_schedule_weight: NULL
1 row in set (0.0010 sec)
-- Investigation #7
Connection 4> SELECT *
FROM performance_schema.events_transactions_current
WHERE thread_id = 1181\G
*************************** 1\. row ***************************
THREAD_ID: 1181
EVENT_ID: 8
END_EVENT_ID: NULL
EVENT_NAME: transaction
STATE: ACTIVE
TRX_ID: NULL
GTID: AUTOMATIC
XID_FORMAT_ID: NULL
XID_GTRID: NULL
XID_BQUAL: NULL
XA_STATE: NULL
SOURCE: transaction.cc:209
TIMER_START: 456761974401600000
TIMER_END: 457816781775400000
TIMER_WAIT: 1054807373800000
ACCESS_MODE: READ WRITE
ISOLATION_LEVEL: REPEATABLE READ
AUTOCOMMIT: NO
NUMBER_OF_SAVEPOINTS: 0
NUMBER_OF_ROLLBACK_TO_SAVEPOINT: 0
NUMBER_OF_RELEASE_SAVEPOINT: 0
OBJECT_INSTANCE_BEGIN: NULL
NESTING_EVENT_ID: 7
NESTING_EVENT_TYPE: STATEMENT
1 row in set (0.0010 sec)
-- Investigation #8
Connection 4> SELECT event_id, current_schema, sql_text
FROM performance_schema.events_statements_history
WHERE thread_id = 1181
AND nesting_event_id = 8
AND nesting_event_type = 'TRANSACTION'\G
*************************** 1\. row ***************************
event_id: 9
current_schema: NULL
sql_text: SELECT * FROM world.city WHERE ID = 3805
*************************** 2\. row ***************************
event_id: 10
current_schema: NULL
sql_text: SELECT Code, Name FROM world.country WHERE Code = 'USA'
2 rows in set (0.0010 sec)
-- Investigation #9
Connection 4> SELECT attr_name, attr_value
FROM performance_schema.session_connect_attrs
WHERE processlist_id = 713
ORDER BY attr_name;
+-----------------+-----------------+
| attr_name | attr_value |
+-----------------+-----------------+
| _client_license | GPL |
| _client_name | libmysqlxclient |
| _client_version | 8.0.21 |
| _os | Win64 |
| _pid | 27832 |
| _platform | x86_64 |
| _thread | 31396 |
| program_name | mysqlsh |
+-----------------+-----------------+
8 rows in set (0.0007 sec)
Listing 14-5Investigating a transaction
第一个查询使用信息模式中的INNODB_TRX视图。例如，它显示事务开始的时间，因此您可以确定它已经活动了多长时间。如果决定回滚事务，那么trx_rows_modified列对于了解事务更改了多少数据也很有用。注意，InnoDB 所谓的 MySQL 线程 id(trx_mysql_thread_id列)实际上是连接 id。
第二个查询使用性能模式中的events_transactions_current表来获取更多的事务信息。您可以使用timer_wait列来确定事务的年龄。该值以皮秒为单位，因此使用FORMAT_PICO_TIME()函数可以更容易地理解该值:
mysql> SELECT FORMAT_PICO_TIME(1054807373800000) AS Age;
+-----------+
| Age |
+-----------+
| 17.58 min |
+-----------+
1 row in set (0.0006 sec)
如果您使用的是 MySQL 8.0.15 或更早版本，请使用sys.format_time()函数。
第三个查询使用events_statements_history表来查找之前在事务中执行的查询。nesting_event_id列被设置为来自events_transactions_current表输出的event_id的值，而nesting_event_type列被设置为匹配一个事务。这确保了只返回正在进行的事务的子事件。结果由event_id(语句的)排序，按照执行的顺序得到语句。默认情况下，events_statements_history表将包含最多十个最新的连接查询。
在这个例子中，调查显示事务执行了两个查询:一个从world.city表中选择，另一个从world.country表中选择。这是导致元数据锁争用的第一个查询。
第四个查询使用session_connect_attrs表来查找连接提交的属性。并非所有客户端和连接器都提交属性，或者它们可能被禁用，因此这些信息并不总是可用。当属性可用时，它们有助于找出违规事务是从哪里执行的。在这个例子中，您可以看到连接来自 MySQL Shell ( mysqlsh)。
调查完问题后，您可以回滚进程列表 id 713 的事务。这将导致执行ALTER TABLE,因此如果您想让模式保持在本例之前的状态，还应该再次删除Name索引:
-- Connection 1
Connection 1> ROLLBACK;
Query OK, 0 rows affected (0.0006 sec)
-- Connection 2
Query OK, 0 rows affected (35 min 34.2938 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- Connection 3
+-----+--------+-------------+-----------------+------------+
| ID | Name | CountryCode | District | Population |
+-----+--------+-------------+-----------------+------------+
| 130 | Sydney | AUS | New South Wales | 3276207 |
+-----+--------+-------------+-----------------+------------+
1 row in set (35 min 31.1277 sec)
-- Connection 2
Connection 2> ALTER TABLE world.city DROP INDEX Name;
Query OK, 0 rows affected (0.1890 sec)
Records: 0 Duplicates: 0 Warnings: 0
解决方案
对于元数据锁争用，您基本上有两种选择来解决问题:完成阻塞事务或终止 DDL 语句。要完成阻塞事务，您需要提交或回滚它。如果您终止连接，将触发事务回滚，因此您需要考虑需要回滚多少工作。为了提交事务，您必须找到执行连接的位置，并以这种方式提交它。您不能提交由不同连接拥有的事务。
终止 DDL 语句将允许其他查询继续进行，但从长远来看，如果锁被一个已放弃但仍处于活动状态的事务持有，这并不能解决问题。对于持有元数据锁的被放弃的事务，可以选择终止 DDL 语句和与被放弃的事务的连接。这样，您可以避免 DDL 语句在事务回滚时继续阻塞后续查询。回滚完成后，您可以重试 DDL 语句。
预防
避免元数据锁争用的关键是避免长时间运行的事务，因为您需要为事务使用的表执行 DDL 语句。例如，当您知道没有长时间运行的事务时，可以执行 DDL 语句。您还可以将lock_wait_timeout选项设置为一个较低的值，这将使 DDL 语句在lock_wait_timeout秒后放弃。虽然这不能避免锁问题，但它通过避免 DDL 语句停止其他查询的执行来缓解这个问题。然后，您可以找到根本原因，而不必担心大部分应用无法工作。
您还可以致力于缩短事务的活动时间。如果不要求所有操作都作为一个原子单元来执行，一种选择是将一个大的事务分成几个较小的事务。您还应该确保在事务处于活动状态时，您没有进行交互工作、文件 I/O、向最终用户传输数据等，从而确保事务不会保持不必要的长时间打开。
长时间运行事务的一个常见原因是应用或客户端根本不提交或回滚事务。禁用autocommit选项时，这种情况尤其容易发生。当autocommit被禁用时，任何查询——即使是普通的只读SELECT语句——都会在没有活动事务的情况下启动一个新事务。这意味着一个看似无辜的查询可能会启动一个事务，如果开发者不知道autocommit被禁用，那么开发者可能不会考虑显式结束事务。在 MySQL Server 中默认情况下，autocommit设置是启用的，但是一些连接器默认情况下禁用它。
摘要
在本章中，您研究了一种情况，其中一个被放弃的事务导致一个ALTER TABLE语句阻塞，随后阻止了同一表上其他查询的执行。确定争用原因的关键是基于performance_schema.metadata_locks表的sys.schema_table_lock_waits视图。由于等待和阻塞锁请求对的数量会很快增加，您可能希望筛选行，例如，查找等待独占锁的请求，或者您可以聚合信息以查找阻塞请求最多的连接。
解决方案是提交或回滚事务，或者终止等待排他元数据锁的 DDL 语句。您也可以选择终止事务和 DDL 语句，如果事务必须回滚许多更改，这将非常有用。防止查询堆积的一个好方法是对lock_wait_timeout使用一个较低的值，如果遇到锁等待超时，就重试 DDL 语句。
在下一章中，您将分析 InnoDB 记录锁定请求超时的情况。
