MySQL 并发教程（二）
四、性能模式中的事务
性能模式支持 MySQL 5.7 和更高版本中的事务监控，并且在 MySQL 8 中默认启用。在性能模式中，除了与 XA 事务和保存点相关的事务细节之外，没有多少事务细节是不能从信息模式中的INNODB_TRX视图获得的。但是，Performance Schema 事务事件的优势在于，您可以将它们与其他事件类型(如语句)相结合，以获取有关事务所做工作的信息。这是本章的主要重点。此外，性能模式提供了带有聚合统计信息的汇总表。
Transaction Events and Their Statements
性能模式中用于调查事务的主要表是事务事件表。有三个表格记录当前或最近的事务:events_transactions_current、events_transactions_history和events_transactions_history_long。它们具有表 4-1 中总结的列。
表 4-1
非汇总事务事件表的列
|列/数据类型
|
描述
|
| --- | --- |
| THREAD_ID``bigint unsigned | 执行事务的连接的性能架构线程 id。 |
| EVENT_ID``bigint unsigned | 事件的事件 id。您可以使用事件 id 对线程的事件进行排序，或者将事件 id 作为外键与事件表之间的线程 id 一起使用。 |
| END_EVENT_ID``bigint unsigned | 事务完成时的事件 id。如果事件 id 为NULL，则事务仍在进行。 |
| EVENT_NAME``varchar(128) | 事务事件名称。目前，该列的值始终为transaction。 |
| STATE``enum | 事务的状态。可能的值有ACTIVE、COMMITTED和ROLLED BACK。 |
| TRX_ID``bigint unsigned | 这是当前未使用的，将始终是NULL。 |
| GTID``varchar(64) | 事务记录的 GTID。当自动确定 GTID 时(通常)，返回AUTOMATIC。这与执行事务的连接的gtid_next变量相同。 |
| XID_FORMAT_ID``int | 对于 XA 事务，格式 id。 |
| XID_GTRID``varchar(130) | 对于 XA 事务，是 gtrid 值。 |
| XID_BQUAL``varchar(130) | 对于 XA 事务，bqual 值。 |
| XA_STATE``varchar(64) | 对于 XA 事务，是事务的状态。这可以是ACTIVE、IDLE、PREPARED、ROLLED BACK或COMMITTED。 |
| SOURCE``varchar(64) | 记录事件的源代码文件和行号。 |
| TIMER_START``bigint unsigned | 事件开始的时间，以皮秒为单位。 |
| TIMER_END``bigint unsigned | 事件完成的时间，以皮秒为单位。如果事务尚未完成，则该值对应于当前时间。 |
| TIMER_WAIT``bigint unsigned | 执行事件所用的总时间(皮秒)。如果事件尚未完成，则该值对应于事务处于活动状态的时间。 |
| ACCESS_MODE``enum | 事务处于只读(READ ONLY)还是读写(READ WRITE)模式。 |
| ISOLATION_LEVEL``varchar(64) | 事务的事务隔离级别。 |
| AUTOCOMMIT``enum | 事务是否基于autocommit选项自动提交，以及显式事务是否已经开始。可能的值是NO和YES。 |
| NUMBER_OF_SAVEPOINTS``bigint unsigned | 事务中创建的保存点数。 |
| NUMBER_OF_ROLLBACK_TO_SAVEPOINT``bigint unsigned | 事务回滚到保存点的次数。 |
| NUMBER_OF_RELEASE_SAVEPOINT``bigint unsigned | 事务释放保存点的次数。 |
| OBJECT_INSTANCE_BEGIN``bigint unsigned | 该字段目前未被使用，并且总是被设置为NULL。 |
| NESTING_EVENT_ID``bigint unsigned | 触发事务的事件的事件 id。 |
| NESTING_EVENT_TYPE``enum | 触发事务的事件的事件类型。 |
如果您正在处理 XA 事务，那么当您需要恢复一个事务时，事务事件表是非常有用的，因为格式 id、gtrid 和 bqual 值可以直接从表中获得，这与必须解析输出的XA RECOVER语句不同。同样，如果您使用保存点，您可以获得保存点使用情况的统计数据。除此之外，这些信息与information_schema.INNODB_TRX视图中的信息非常相似。
作为使用events_transactions_current表的例子，您可以启动两个事务，如清单 4-1 所示。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 57 140 6
-- 2 58 141 6
-- Connection 1
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0004 sec)
Connection 1> UPDATE world.city SET Population = 5200000 WHERE ID = 130;
Connection 1> UPDATE world.city SET Population = 4900000 WHERE ID = 131;
Connection 1> UPDATE world.city SET Population = 2400000 WHERE ID = 132;
Connection 1> UPDATE world.city SET Population = 2000000 WHERE ID = 133;
-- Connection 2
Connection 2> XA START 'abc', 'def', 1;
Connection 2> UPDATE world.city SET Population = 900000 WHERE ID = 3805;
Listing 4-1Example transactions
第一个事务是更新几个城市人口的普通事务，第二个事务是 XA 事务。清单 4-2 显示了列出当前活动事务的events_transactions_current表的输出示例。
-- Investigation #1
-- Connection 3
Connection 3> SELECT *
FROM performance_schema.events_transactions_current
WHERE state = 'ACTIVE'\G
*************************** 1\. row ***************************
THREAD_ID: 140
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: 72081362554600000
TIMER_END: 72161455792800000
TIMER_WAIT: 80093238200000
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
*************************** 2\. row ***************************
THREAD_ID: 141
EVENT_ID: 8
END_EVENT_ID: NULL
EVENT_NAME: transaction
STATE: ACTIVE
TRX_ID: NULL
GTID: AUTOMATIC
XID_FORMAT_ID: 1
XID_GTRID: abc
XID_BQUAL: def
XA_STATE: ACTIVE
SOURCE: transaction.cc:209
TIMER_START: 72081766957700000
TIMER_END: 72161455799300000
TIMER_WAIT: 79688841600000
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
2 rows in set (0.0007 sec)
Listing 4-2Using the events_transactions_current table
第 1 行中的事务是常规事务，而第 2 行中的事务是 XA 事务。两个事务都是由一个语句启动的，这可以从嵌套事件类型中看出。如果想找到触发事务的语句，可以使用它来查询events_statements_history表，如下所示
-- Investigation #2
Connection 3> SELECT sql_text
FROM performance_schema.events_statements_history
WHERE thread_id = 140
AND event_id = 7\G
*************************** 1\. row ***************************
sql_text: start transaction
1 row in set (0.0434 sec)
这表明由thread_id = 140执行的事务是使用START TRANSACTION语句开始的。因为events_statements_history表只包括连接的最后十条语句，所以不能保证启动事务的语句仍然在历史表中。当autocommit被禁用时，如果您正在查看一个单语句事务或第一条语句(当它仍在执行时),您将需要查询events_statements_current表。
事务和语句之间的关系也是相反的。
给定一个事务事件 id 和线程 id，您可以使用语句事件历史和当前表来查询为该事务执行的最后十条语句。清单 4-3 显示了thread_id = 140和事务EVENT_ID = 8的示例(来自清单 4-2 的第 1 行)，其中包含了开始事务的语句和后续语句。
-- Investigation #4
Connection 3> SET @thread_id = 140,
@event_id = 8,
@nesting_event_id = 7;
Query OK, 0 rows affected (0.0007 sec)
-- Investigation #6
Connection 3> SELECT event_id, sql_text,
FORMAT_PICO_TIME(timer_wait) AS latency,
IF(end_event_id IS NULL, 'YES', 'NO') AS current
FROM ((SELECT event_id, end_event_id,
timer_wait,
sql_text, nesting_event_id,
nesting_event_type
FROM performance_schema.events_statements_current
WHERE thread_id = @thread_id
) UNION (
SELECT event_id, end_event_id,
timer_wait,
sql_text, nesting_event_id,
nesting_event_type
FROM performance_schema.events_statements_history
WHERE thread_id = @thread_id
)
) events
WHERE (nesting_event_type = 'TRANSACTION'
AND nesting_event_id = @event_id)
OR event_id = @nesting_event_id
ORDER BY event_id DESC\G
*************************** 1\. row ***************************
event_id: 12
sql_text: UPDATE world.city SET Population = 2000000 WHERE ID = 133
latency: 384.00 us
current: NO
*************************** 2\. row ***************************
event_id: 11
sql_text: UPDATE world.city SET Population = 2400000 WHERE ID = 132
latency: 316.20 us
current: NO
*************************** 3\. row ***************************
event_id: 10
sql_text: UPDATE world.city SET Population = 4900000 WHERE ID = 131
latency: 299.30 us
current: NO
*************************** 4\. row ***************************
event_id: 9
sql_text: UPDATE world.city SET Population = 5200000 WHERE ID = 130
latency: 176.95 ms
current: NO
*************************** 5\. row ***************************
event_id: 7
sql_text: start transaction
latency: 223.20 us
current: NO
5 rows in set (0.0016 sec)
Listing 4-3Finding the last ten statements executed in a transaction
子查询(一个派生表)从events_statements_current和events_statements_history表中找到线程的所有语句事件。有必要包括当前事件，因为可能有正在进行的事务报表。通过作为事务的子事务或事务的嵌套事件来过滤语句(event_id = 7)。这将包括从启动事务的语句开始的所有语句。如果有正在进行的陈述，则最多有 11 个陈述，否则最多有 10 个。
end_event_id用于确定语句当前是否正在执行，使用event_id对语句进行反向排序，因此最新的语句在第 1 行，最老的(START TRANSACTION语句)在第 5 行。
这种类型的查询不仅对调查仍在执行查询的事务有用。当您遇到一个空闲事务，并且想知道该事务在被放弃之前做了什么时，它也非常有用。寻找活动事务的另一种相关方法是使用sys.session视图，该视图使用events_transactions_current表来包含每个连接的事务状态信息。清单 4-4 显示了一个查询活动事务的例子，不包括执行查询的连接的行。
-- Investigation #7
Connection 3> SELECT *
FROM sys.session
WHERE trx_state = 'ACTIVE'
AND conn_id <> CONNECTION_ID()\G
*************************** 1\. row ***************************
thd_id: 140
conn_id: 57
user: mysqlx/worker
db: NULL
command: Sleep
state: NULL
time: 449
current_statement: UPDATE world.city SET Population = 2000000 WHERE ID = 133
statement_latency: NULL
progress: NULL
lock_latency: 111.00 us
rows_examined: 1
rows_sent: 0
rows_affected: 1
tmp_tables: 0
tmp_disk_tables: 0
full_scan: NO
last_statement: UPDATE world.city SET Population = 2000000 WHERE ID = 133
last_statement_latency: 384.00 us
current_memory: 228.31 KiB
last_wait: NULL
last_wait_latency: NULL
source: NULL
trx_latency: 7.48 min
trx_state: ACTIVE
trx_autocommit: NO
pid: 30936
program_name: mysqlsh
*************************** 2\. row ***************************
thd_id: 141
conn_id: 58
user: mysqlx/worker
db: NULL
command: Sleep
state: NULL
time: 449
current_statement: UPDATE world.city SET Population = 900000 WHERE ID = 3805
statement_latency: NULL
progress: NULL
lock_latency: 387.00 us
rows_examined: 1
rows_sent: 0
rows_affected: 1
tmp_tables: 0
tmp_disk_tables: 0
full_scan: NO
last_statement: UPDATE world.city SET Population = 900000 WHERE ID = 3805
last_statement_latency: 49.39 ms
current_memory: 70.14 KiB
last_wait: NULL
last_wait_latency: NULL
source: NULL
trx_latency: 7.48 min
trx_state: ACTIVE
trx_autocommit: NO
pid: 30936
program_name: mysqlsh
2 rows in set (0.0422 sec)
Listing 4-4Finding active transactions with sys.session
这表明第一行中的事务已经活动了 7 分钟以上，距离上次执行查询有 449 秒(7.5 分钟)(您的值会有所不同)。last_statement可以用来确定连接执行的最后一个查询。这是一个被放弃的事务的例子，它阻止了 InnoDB 清除它的撤销日志。放弃事务的最常见原因是数据库管理员交互地启动了一个事务，然后分心了，或者是autocommit被禁用了，没有意识到一个事务已经启动了。
Caution
如果您禁用了autocommit，请始终注意在工作结束时提交或回滚。一些连接器默认禁用autocommit,所以请注意您的应用可能没有使用服务器默认设置。
您可以回滚事务以避免更改任何数据(如果您使用 MySQL Shell 脚本来重现该示例，那么在下一次调查没有答案的情况下，当按 enter 键时，这将自动完成)。对于第一次(正常)事务
-- Connection 1
Connection 1> ROLLBACK;
Query OK, 0 rows affected (0.0303 sec)
对于 XA 事务:
-- Connection 2
Connection 2> XA END 'abc', 'def', 1;
Query OK, 0 rows affected (0.0002 sec)
Connection 2> XA ROLLBACK 'abc', 'def', 1;
Query OK, 0 rows affected (0.0308 sec)
性能模式表对分析事务有用的另一种方式是使用汇总表来获得聚合数据。
Transaction Summary Tables
与可以用来获得所执行语句的报告的语句汇总表一样，也可以使用事务汇总表来分析事务的使用情况。虽然它们不像它们的对应物那样有用，但是它们确实提供了对以不同方式使用事务的连接和账户的洞察。
共有五个事务摘要表，可以按帐户、主机、线程或用户对数据进行全局分组。所有摘要也按事件名称分组，但由于目前只有一个事务事件(transaction)，所以这是一个空操作。这些桌子是
-
events_transactions_summary_global_by_event_name: 汇总所有事务。该表中只有一行。 -
events_transactions_summary_by_account_by_event_name: 按用户名和主机名分组的事务。 -
events_transactions_summary_by_host_by_event_name: 按账户主机名分组的事务。 -
events_transactions_summary_by_thread_by_event_name: 按线程分组的事务。仅包括当前存在的线程。 -
events_transactions_summary_by_user_by_event_name: 按账户用户名部分分组的事件。
每个表都包括对事务统计信息进行分组的列和三组列:总计、读写事务和只读事务。对于这三组列中的每一组，都有事务总数以及总延迟、最小延迟、平均延迟和最大延迟。清单 4-5 显示了来自events_transactions_summary_global_by_event_name表的数据的一个例子。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 60 143 6
-- Connection 1
Connection 1> SELECT *
FROM performance_schema.events_transactions_summary_global_by_event_name\G
*************************** 1\. row ***************************
EVENT_NAME: transaction
COUNT_STAR: 40485
SUM_TIMER_WAIT: 90259064465300000
MIN_TIMER_WAIT: 4800000
AVG_TIMER_WAIT: 2229444500000
MAX_TIMER_WAIT: 62122342944500000
COUNT_READ_WRITE: 40483
SUM_TIMER_READ_WRITE: 90230783742700000
MIN_TIMER_READ_WRITE: 4800000
AVG_TIMER_READ_WRITE: 2228856100000
MAX_TIMER_READ_WRITE: 62122342944500000
COUNT_READ_ONLY: 2
SUM_TIMER_READ_ONLY: 28280722600000
MIN_TIMER_READ_ONLY: 9561820600000
AVG_TIMER_READ_ONLY: 14140361300000
MAX_TIMER_READ_ONLY: 18718902000000
1 row in set (0.0007 sec)
Listing 4-5The events_transactions_summary_global_by_event_name table
当您研究输出中有多少事务，尤其是读写事务时，您可能会感到惊讶。请记住，在查询 InnoDB 表时，即使您没有明确指定事务，所有事情都是事务。因此，即使一个简单的查询单行的SELECT语句也算作一个事务。关于读写事务和只读事务之间的分布，只有当您显式地以只读方式启动事务时，性能模式才会将其视为只读
START TRANSACTION READ ONLY;
当 InnoDB 确定自动提交的单语句事务可以被视为只读事务时，它仍然会计入性能模式中的读写统计数据。
Summary
本章介绍了性能模式中与事务相关的表，并展示了如何连接到其他表。首先讨论每个事务事件占一行的三个表，events_transactions_current、events_transactions_history和events_transactions_history_long，然后使用它们连接语句事件表以获得事务中最近执行的语句。最后，介绍了事务汇总表。
现在，您已经介绍了监控锁和事务的最重要的资源，是时候详细介绍锁了。首先，您将了解锁的访问级别。
五、锁的访问级别
在介绍锁的第一章中，没有提到锁是如何工作的。不管要做什么样的工作，一次只允许一个查询访问就可以实现数据库的锁定。然而，这将是非常低效的。
就像交通灯一样，另一种方法是根据将要完成的工作授予访问权限。交通灯不仅允许一辆车同时通过十字路口，也允许所有同方向行驶的车辆通过。类似地，在数据库中，您可以区分共享(读)和独占(写)访问。访问级别顾名思义。共享锁允许其他连接也获得共享锁。这是最宽松的锁访问级别。独占锁只允许一个连接获得锁。共享锁也称为读锁，排他锁也称为写锁。
Note
锁访问级别有时也称为锁类型，但由于这可能与锁粒度(有时也称为类型)相混淆，所以这里使用术语锁访问级别。
MySQL 还有一个叫做意向锁的概念，它指定了事务的意向。意向锁可以是共享的，也可以是排他的。
本章的其余部分将更详细地介绍共享锁、排他锁以及意向锁。
共享锁
当一个线程需要保护一个资源，但它不打算改变该资源时，它可以使用一个共享锁来防止其他线程改变该资源，同时仍然允许它们访问同一资源。这是最常用的访问级别。
每当一个语句从一个表中进行选择时，MySQL 将对查询中涉及的表使用一个共享锁。对于数据锁，它的工作方式不同，因为 InnoDB 在读取行时通常不获取共享锁。只有在SERIALIZABLE事务隔离级别中明确请求共享锁时，或者工作流需要共享锁时，比如涉及外键时，才会发生这种情况。
您可以通过添加清单 5-1 中所示的FOR SHARE或其同义词LOCK IN SHARE MODE来显式请求查询所访问的行上的共享锁。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 36 80 6
-- Connection 1
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0003 sec)
Connection 1> SELECT * FROM world.city WHERE ID = 130 FOR SHARE;
+-----+--------+-------------+-----------------+------------+
| ID | Name | CountryCode | District | Population |
+-----+--------+-------------+-----------------+------------+
| 130 | Sydney | AUS | New South Wales | 3276207 |
+-----+--------+-------------+-----------------+------------+
1 row in set (0.0047 sec)
Connection 1> SELECT object_type, object_schema, object_name,
lock_type, lock_duration, lock_status
FROM performance_schema.metadata_locks
WHERE OWNER_THREAD_ID = PS_CURRENT_THREAD_ID()
AND OBJECT_SCHEMA <> 'performance_schema'\G
*************************** 1\. row ***************************
object_type: TABLE
object_schema: world
object_name: city
lock_type: SHARED_READ
lock_duration: TRANSACTION
lock_status: GRANTED
1 row in set (0.0005 sec)
Connection 1> SELECT engine, object_schema, object_name,
lock_type, lock_mode, lock_status
FROM performance_schema.data_locks
WHERE THREAD_ID = PS_CURRENT_THREAD_ID()\G
*************************** 1\. row ***************************
engine: INNODB
object_schema: world
object_name: city
lock_type: TABLE
lock_mode: IS
lock_status: GRANTED
*************************** 2\. row ***************************
engine: INNODB
object_schema: world
object_name: city
lock_type: RECORD
lock_mode: S,REC_NOT_GAP
lock_status: GRANTED
2 rows in set (0.0005 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.0004 sec)
Listing 5-1Example of obtaining a shared lock
当查询metadata_locks表时，性能模式表上的锁被排除，因为它们是针对调查查询本身而不是之前的查询。这里，在world.city表上获取了一个共享锁以及主键(ID列)等于 130 的记录。从值为SHARED_READ的metadata_locks表中的lock_type列和第二行data_locks的lock_mode列中的 S 可以看出它们是共享锁。来自data_locks的第一行的值IS意味着它是一个共享意向锁，稍后将对此进行更详细的讨论。
虽然共享锁确实允许其他使用共享锁的查询继续进行，但是它们确实会阻止获取独占锁的尝试
独占锁
排他锁与共享锁相对应。它们确保只有被授予独占锁的线程才能在锁期间访问资源。因为排他锁用于确保一次只有一个线程在修改资源，所以它们也被称为写锁。
排他锁主要是通过数据定义语言(DDL)语句(如ALTER TABLE)获得的，当使用数据修改语言(DML)语句(如UPDATE和DELETE)修改数据时也是如此。清单 5-2 中提供了获取排他锁和锁表中数据的示例。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 38 84 6
-- Connection 1
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0003 sec)
Connection 1> UPDATE world.city
SET Population = Population + 1
WHERE ID = 130;
Query OK, 1 row affected (0.0028 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Connection 1> SELECT object_type, object_schema, object_name,
lock_type, lock_duration, lock_status
FROM performance_schema.metadata_locks
WHERE OWNER_THREAD_ID = PS_CURRENT_THREAD_ID()
AND OBJECT_SCHEMA <> 'performance_schema'\G
*************************** 1\. row ***************************
object_type: TABLE
object_schema: world
object_name: city
lock_type: SHARED_WRITE
lock_duration: TRANSACTION
lock_status: GRANTED
*************************** 2\. row ***************************
object_type: TABLE
object_schema: world
object_name: country
lock_type: SHARED_READ
lock_duration: TRANSACTION
lock_status: GRANTED
2 rows in set (0.0008 sec)
Connection 1> SELECT engine, object_schema, object_name,
lock_type, lock_mode, lock_status
FROM performance_schema.data_locks
WHERE THREAD_ID = PS_CURRENT_THREAD_ID()\G
*************************** 1\. row ***************************
engine: INNODB
object_schema: world
object_name: city
lock_type: TABLE
lock_mode: IX
lock_status: GRANTED
*************************** 2\. row ***************************
engine: INNODB
object_schema: world
object_name: city
lock_type: RECORD
lock_mode: X,REC_NOT_GAP
lock_status: GRANTED
2 rows in set (0.0005 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.3218 sec)
Listing 5-2Example of obtaining exclusive locks
这个例子大部分反映了获取共享锁的例子，但是也有一些令人惊讶的地方。从data_locks表开始，它显示了表上的一个独占插入意图(IX)锁和一个独占(X)记录锁。这是意料之中的。
使用metadata_locks工作台变得更加复杂，现在有两个工作台锁，一个SHARED_WRITE锁在city工作台上，一个SHARED_READ锁在country工作台上。一个锁怎么能同时共享和写，为什么city表上的锁在被修改时是共享的，为什么country表上有锁？
一个SHARED_WRITE锁告知数据被锁定用于更新，但是元数据锁本身是一个共享锁。这是因为表的元数据没有被修改，所以允许其他并发共享访问表元数据是安全的。记住，metadata_locks表并不关心单个记录上的锁，所以从元数据的角度来看，对city表的访问是共享的。
country表上的元数据锁来自于city表到country表的外键。共享锁防止对country元数据的修改，比如在事务仍在进行时删除外键中涉及的列。第 10 章将更详细地介绍外键对锁定的影响。
意向锁
在本章到目前为止的两个例子中，都有意向锁。那些是什么？它是一个表示 InnoDB 事务意图的锁，可以是共享的，也可以是独占的。乍一看，这似乎是不必要的复杂，但是意向锁允许 InnoDB 在不阻塞兼容操作的情况下有序地解决锁请求。细节超出了本次讨论的范围。重要的是你知道意向锁的存在，所以当你看到它们时，你知道它们来自哪里。
第 6 章从更实用的角度介绍了意向锁，第 7 章的 InnoDB 锁介绍了插入意向锁这一相关概念。
锁兼容性
锁兼容性矩阵定义了两个锁请求是否相互冲突。意向锁的引入使得这比说共享锁互相兼容，排他锁不兼容任何其他锁要复杂一点。
两个意向锁总是互相兼容的。这意味着即使一个事务有一个意向排他锁，它也不会阻止另一个事务获取一个意向锁。但是，它将阻止另一个事务将其意向锁升级为完全锁。表 5-1 显示了锁类型之间的兼容性。共享锁表示为 S，排他锁表示为 x。意向锁以 I 为前缀，因此 IS 是意向共享锁，IX 是意向排他锁。
表 5-1
InnoDB 锁兼容性
| |独占(X)
|
意图排他(九)
|
共享的
|
共享意向(IS)
| | --- | --- | --- | --- | --- | | 独占(X) | -什么 | -什么 | -什么 | -什么 | | 意图排他(IX) | -什么 | ✔ | -什么 | ✔ | | 共享 | -什么 | -什么 | ✔ | ✔ | | 意向共享(是) | -什么 | ✔ | ✔ | ✔ |
在该表中，复选标记表示这两种锁兼容，而叉号表示这两种锁相互冲突。意向锁的唯一冲突是独占锁和共享锁。排他锁与所有其他锁冲突，包括两种意向锁类型。共享锁只与排他锁和意图排他锁冲突。
这听起来确实很简单；然而，这仅适用于两个相同类型的锁。当您开始在 InnoDB 级别包含不同的锁时，它会变得更加复杂，这将在第 8 章讨论锁争用时讨论。
这都是 MySQL 和 InnoDB 自动处理的；但是，在调查锁问题时，您需要理解这些规则。
摘要
本章讨论了 MySQL 锁的访问级别。锁可以是共享锁、排他锁、意向共享锁或意向排他锁。
共享锁用于对资源的读访问，并允许多个线程同时访问同一资源。另一方面，排他锁一次只允许一个线程访问资源，这使得更新资源是安全的。意向锁是 InnoDB 的一个概念，它允许 InnoDB 以更少的阻塞请求来解决锁请求。所有的意向锁都是互相兼容的，即使是意向排他锁，但是意向排他锁会阻塞共享锁。
在本章以及前面的章节中，您还会遇到锁如何保护不同资源(如表和记录)的示例。在下一章中，是时候学习更多关于高级锁访问类型的知识了，比如表和元数据锁。
六、高级锁类型
在上一章中，您学习了共享和独占访问级别。原则上，您可以创建一个只包含一种锁的锁系统，这种锁可以是共享的，也可以是排他的。然而，这意味着它必须在实例级工作，因此很难允许对数据进行并发读写访问。在这一章和下一章中，你将了解到根据它们所保护的资源，锁有多种类型。虽然这确实使锁定变得更加复杂，但它也允许更细粒度的锁定，从而支持更高的并发性。
本章讨论 MySQL 中的高级锁，从用户级锁开始，讨论在 MySQL 级(即存储引擎之上)处理的各种类型的锁。包括刷新锁、元数据锁、显式和隐式表锁(这是一个例外，因为它们由 InnoDB 处理)、备份锁和日志锁。
用户级锁
用户级锁是应用可以用来保护的显式锁类型，例如，工作流。它们不常使用，但是对于一些需要序列化访问的复杂任务来说，它们会很有用。所有用户锁都是排他锁，使用最长 64 个字符的名称获得。
您可以使用一组函数来操作用户级锁:
-
GET_LOCK(name, timeout): 通过指定锁的名称获得锁。第二个参数是以秒为单位的超时；如果在这段时间内没有获得锁，该函数将返回 0。如果获得了锁，返回值为 1。如果超时为负，该函数将无限期等待锁变为可用。 -
IS_FREE_LOCK(name): 检查命名锁是否可用。如果锁可用，函数返回 1，如果锁不可用，函数返回 0。 -
IS_USED_LOCK(name): 这是IS_FREE_LOCK()功能的反义词。如果锁在使用中(不可用)，该函数返回持有锁的连接的连接 id，如果锁不在使用中(可用)，则返回NULL。 -
RELEASE_ALL_LOCKS(): 释放连接持有的所有用户级锁。返回值是释放的锁的数量。 -
RELEASE_LOCK(name): 用提供的名字解锁。如果锁被释放，返回值为 1；如果锁存在但不属于连接，返回值为 0；如果锁不存在，返回值为NULL。
通过多次调用GET_LOCK()可以获得多个锁。如果这样做，请注意确保所有用户以相同的顺序获得锁，否则可能会发生死锁。如果发生死锁，将返回一个ER_USER_LOCK_DEADLOCK错误(错误代码 3058)。清单 6-1 中显示了一个这样的例子。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 322 617 6
-- 2 323 618 6
-- Connection 1
Connection 1> SELECT GET_LOCK('my_lock_1', -1);
+---------------------------+
| GET_LOCK('my_lock_1', -1) |
+---------------------------+
| 1 |
+---------------------------+
1 row in set (0.0003 sec)
-- Connection 2
Connection 2> SELECT GET_LOCK('my_lock_2', -1);
+---------------------------+
| GET_LOCK('my_lock_2', -1) |
+---------------------------+
| 1 |
+---------------------------+
1 row in set (0.0003 sec)
Connection 2> SELECT GET_LOCK('my_lock_1', -1);
-- Connection 1
Connection 1> SELECT GET_LOCK('my_lock_2', -1);
ERROR: 3058: Deadlock found when trying to get user-level lock; try rolling back transaction/releasing locks and restarting lock acquisition.
Listing 6-1A deadlock for user-level locks
当连接 2 试图获取my_lock_1锁时，该语句将被阻塞，直到连接 1 试图获取触发死锁的my_lock_2锁。如果您获得多个锁，您应该准备好处理死锁。请注意，对于用户级锁，死锁不会触发事务回滚。
被授予和挂起的用户级锁可以在performance_schema.metadata_locks表中找到，其中OBJECT_TYPE列设置为USER LEVEL LOCK，如清单 6-2 所示。列出的锁假设您离开了清单 6-1 中的死锁被触发时的系统。注意，有些值如OBJECT_INSTANCE_BEGIN对您来说会有所不同，您必须更改WHERE子句中owner_thread_id的 id，以匹配清单 6-1 中的 id。
-- Investigation #1
-- Connection 3
Connection 3> SELECT *
FROM performance_schema.metadata_locks
WHERE object_type = 'USER LEVEL LOCK'
AND owner_thread_id IN (617, 618)\G
*************************** 1\. row ***************************
OBJECT_TYPE: USER LEVEL LOCK
OBJECT_SCHEMA: NULL
OBJECT_NAME: my_lock_1
COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 2124404669104
LOCK_TYPE: EXCLUSIVE
LOCK_DURATION: EXPLICIT
LOCK_STATUS: GRANTED
SOURCE: item_func.cc:5067
OWNER_THREAD_ID: 617
OWNER_EVENT_ID: 8
*************************** 2\. row ***************************
OBJECT_TYPE: USER LEVEL LOCK
OBJECT_SCHEMA: NULL
OBJECT_NAME: my_lock_2
COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 2124463901664
LOCK_TYPE: EXCLUSIVE
LOCK_DURATION: EXPLICIT
LOCK_STATUS: GRANTED
SOURCE: item_func.cc:5067
OWNER_THREAD_ID: 618
OWNER_EVENT_ID: 8
*************************** 3\. row ***************************
OBJECT_TYPE: USER LEVEL LOCK
OBJECT_SCHEMA: NULL
OBJECT_NAME: my_lock_1
COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 2124463901088
LOCK_TYPE: EXCLUSIVE
LOCK_DURATION: EXPLICIT
LOCK_STATUS: PENDING
SOURCE: item_func.cc:5067
OWNER_THREAD_ID: 618
OWNER_EVENT_ID: 9
3 rows in set (0.0015 sec)
Listing 6-2Listing user-level locks
用户级锁的OBJECT_TYPE是USER LEVEL LOCK，锁的持续时间是EXPLICIT，因为这取决于用户或应用是否再次释放锁。在行 1 中，具有性能模式线程 id 617 的连接已经被授予my_lock_1锁，并且在行 3 中，线程 id 618 正在等待(待定)它被授予。线程 id 618 也具有包含在行 2 中的授权锁。一旦完成调查，记得释放锁，例如，首先在连接 1 中执行SELECT RELEASE_ALL_LOCKS()，然后在连接 2 中执行【】(当使用 MySQL Shell concurrency_book模块退出工作负载时，这将自动发生)。
下一级锁涉及非数据表级锁。首先要讨论的是冲水锁。
清空锁
大多数参与备份的人都熟悉刷新锁。它是在使用FLUSH TABLES语句时获取的，并持续整个语句期间，除非您添加了WITH READ LOCK,在这种情况下，共享(读)锁将被持有，直到该锁被显式释放。在ANALYZE TABLE语句的结尾也会触发隐式的表刷新。刷新锁是一个表级锁。用FLUSH TABLES WITH READ LOCK获取的读锁将在后面的显式表锁中讨论。
刷新锁的锁问题的一个常见原因是长时间运行的查询。只要存在打开表的查询，一个FLUSH TABLES语句就不能刷新表。这意味着，如果在一个长时间运行的查询使用一个或多个被刷新的表时执行一个FLUSH TABLES语句，那么FLUSH TABLES语句将阻塞所有其他需要这些表的语句，直到锁的情况得到解决。
嵌入式锁受lock_wait_timeout设置的影响。如果获得锁的时间超过lock_wait_timeout秒，MySQL 将放弃锁。如果FLUSH TABLES声明被扼杀，同样适用。然而，由于 MySQL 的内部原因，在长时间运行的查询完成之前，一个称为表定义缓存(TDC)版本锁的较低级别的锁不能总是被释放。 1 这意味着确保锁问题得到解决的唯一方法是终止长时间运行的查询，但是要注意，如果查询已经更改了许多行，回滚查询可能需要很长时间。
当围绕刷新锁存在锁争用时，FLUSH TABLES语句和随后启动的查询都将状态设置为“等待表刷新”清单 6-3 展示了一个包含三个查询的例子。如果您自己正在重现这个场景(而不是使用 MySQL Shell concurrency_book模块)，那么您可以将连接 1 中的参数改为SLEEP(),给自己更多的时间来完成这个示例。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 375 691 6
-- 2 376 692 6
-- 3 377 693 6
-- 4 378 694 6
-- Connection 1
Connection 1> SELECT city.*, SLEEP(3) FROM world.city WHERE ID = 130;
-- Connection 2
Connection 2> FLUSH TABLES world.city;
-- Connection 3
Connection 3> SELECT * FROM world.city WHERE ID = 201;
-- Connection 4
-- Query sys.session for the three threads involved in the lock situation
Connection 4> SELECT thd_id, conn_id, state,
current_statement
FROM sys.session
WHERE current_statement IS NOT NULL
AND thd_id IN (691, 692, 693)
ORDER BY thd_id\G
*************************** 1\. row ***************************
thd_id: 691
conn_id: 375
state: User sleep
current_statement: SELECT city.*, SLEEP(3) FROM world.city WHERE ID = 130
*************************** 2\. row ***************************
thd_id: 692
conn_id: 376
state: Waiting for table flush
current_statement: FLUSH TABLES world.city
*************************** 3\. row ***************************
thd_id: 693
conn_id: 377
state: Waiting for table flush
current_statement: SELECT * FROM world.city WHERE ID = 201
3 rows in set (0.0586 sec)
Listing 6-3Example of waiting for a flush lock
该示例使用了sys.session视图；使用performance_schema.threads和SHOW PROCESSLIST可以获得类似的结果。为了将输出减少到只包括与刷新锁讨论相关的查询，将WHERE子句设置为只包括前三个连接的线程 id。
与conn_id = 375的连接正在执行一个使用world.city表的慢速查询(使用了一个SLEEP(3)来确保它花费足够的时间来执行其他连接的语句)。同时，conn_id = 376为world.city表执行了一条FLUSH TABLES语句。因为第一个查询仍然打开着表(一旦查询完成，它就会被释放)，所以FLUSH TABLES语句最终会等待表刷新锁。最后，conn_id = 377试图查询表，因此必须等待FLUSH TABLES语句。
另一种非数据表锁是元数据锁。
元数据锁
元数据锁是 MySQL 中较新的锁类型之一。它们是在 MySQL 5.5 中引入的，它们的目的是保护模式，因此当查询或事务依赖于模式不变时，它不会被改变。元数据锁在表级别工作，但是它们应该被视为独立于表锁的锁类型，因为它们不保护表中的数据。
语句和 DML 查询使用共享元数据锁，而 DDL 语句使用排他锁。当第一次使用表时，连接获取表上的元数据锁，并保持该锁直到事务结束。当持有元数据锁时，不允许其他连接更改表的模式定义。但是，执行SELECT语句和 DML 语句的其他连接不受限制。通常关于元数据锁的最大问题是长时间运行的事务，可能是空闲的，阻止 DDL 语句开始它们的工作。
如果遇到关于元数据锁定的冲突，您会看到进程列表中的查询状态设置为“等待表元数据锁定”清单 6-4 中显示了一个包括设置查询的例子。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 428 768 6
-- 2 429 769 6
-- Connection 1
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0003 sec)
Connection 1> SELECT * FROM world.city WHERE ID = 130\G
*************************** 1\. row ***************************
ID: 130
Name: Sydney
CountryCode: AUS
District: New South Wales
Population: 3276207
1 row in set (0.0006 sec)
-- Connection 2
Connection 2> OPTIMIZE TABLE world.city;
Listing 6-4Example of waiting for table metadata lock
连接 2 阻塞，当您处于这种情况时，您可以查询清单 6-5 中所示的sys.session或类似内容。
-- Investigation #1
-- Connection 3
Connection 3> SELECT thd_id, conn_id, state,
current_statement, statement_latency,
last_statement, trx_state
FROM sys.session
WHERE conn_id IN (428, 429)
ORDER BY conn_id\G
*************************** 1\. row ***************************
thd_id: 768
conn_id: 428
state: NULL
current_statement: SELECT * FROM world.city WHERE ID = 130
statement_latency: NULL
last_statement: SELECT * FROM world.city WHERE ID = 130
trx_state: ACTIVE
*************************** 2\. row ***************************
thd_id: 769
conn_id: 429
state: Waiting for table metadata lock
current_statement: OPTIMIZE TABLE world.city
statement_latency: 26.62 s
last_statement: NULL
trx_state: COMMITTED
2 rows in set (0.0607 sec)
Listing 6-5sys.session
for the connections involved in the metadata lock
在本例中，与conn_id = 428的连接有一个正在进行的事务，并且在前一条语句中查询了world.city表(本例中的当前语句与下一条语句执行之前不会被清除的语句相同)。当事务仍然活跃时，conn_id = 429已经执行了一个OPTIMIZE TABLE语句，该语句现在正在等待元数据锁定。(是的，OPTIMIZE TABLE不改变模式定义，但作为 DDL 语句，它仍然受元数据锁定的影响。)因为 MySQL 没有事务性 DDL 语句，所以conn_id = 429的事务状态显示为 committed。
当导致元数据锁定的是当前或最后一条语句时，这是很方便的。在更一般的情况下，您可以使用将OBJECT_TYPE列设置为TABLE的performance_schema.metadata_locks表来查找授予的和挂起的元数据锁。清单 6-6 展示了一个使用与前一个例子相同的设置的被授予和挂起的元数据锁的例子。第 14 章详细介绍了元数据锁的研究。
-- Investigation #2
Connection 3> SELECT object_type, object_schema, object_name,
lock_type, lock_duration, lock_status,
owner_thread_id
FROM performance_schema.metadata_locks
WHERE owner_thread_id IN (768, 769)
AND object_type = 'TABLE'\G
*************************** 1\. row ***************************
object_type: TABLE
object_schema: world
object_name: city
lock_type: SHARED_READ
lock_duration: TRANSACTION
lock_status: GRANTED
owner_thread_id: 768
*************************** 2\. row ***************************
object_type: TABLE
object_schema: world
object_name: city
lock_type: SHARED_NO_READ_WRITE
lock_duration: TRANSACTION
lock_status: PENDING
owner_thread_id: 769
2 rows in set (0.0010 sec)
Listing 6-6Example of metadata locks
在该示例中，由于正在进行的事务，线程 id 768(与来自sys.session输出的conn_id = 428相同)拥有对world.city表的共享读锁，并且线程 id 769 在试图对该表执行 DDL 语句时正在等待锁。
完成后，确保回滚或提交连接 1 中的事务，这样OPTIMIZE TABLE就可以完成:
-- Connection 1
Connection 1> ROLLBACK;
Query OK, 0 rows affected (0.0004 sec)
元数据锁的一个特例是用LOCK TABLES语句显式获取的锁。
显式表锁
使用LOCK TABLES和FLUSH TABLES WITH READ LOCK语句获取显式表锁。使用LOCK TABLES语句，可以获取共享锁或独占锁；FLUSH TABLES WITH READ LOCK总是使用共享锁。这些表被锁定，直到用UNLOCK TABLES语句显式释放它们。当FLUSH TABLES WITH READ LOCK在没有列出任何表的情况下被执行时，全局读锁(即，影响所有表)被获取。虽然这些锁也保护数据，但在 MySQL 中它们被视为元数据锁。
除了与备份相关的FLUSH TABLES WITH READ LOCK之外，显式表锁并不经常与 InnoDB 一起使用，因为 InnoDB 复杂的锁特性在大多数情况下都优于自己处理锁。但是，如果您真的需要锁定整个表，显式锁会很有用，因为 MySQL 检查它们非常便宜。
清单 6-7 中显示了一个连接的例子，该连接在world.country和world.countrylanguage表上采用显式读锁，在world.city表上采用写锁。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 432 772 6
-- Connection 1
Connection 1> LOCK TABLES world.country READ,
world.countrylanguage READ,
world.city WRITE;
Query OK, 0 rows affected (0.0029 sec)
Listing 6-7Using explicit table locks
当您使用显式锁时，只允许您根据请求的锁来使用您已经锁定的表。这意味着如果你获取一个读锁并试图写入表(ER_TABLE_NOT_LOCKED_FOR_WRITE)或者如果你试图使用一个没有获取锁(ER_TABLE_NOT_LOCKED)的表，你将得到一个错误，例如(清单 6-7 的延续)
Connection 1> UPDATE world.country
SET Population = Population + 1
WHERE Code = 'AUS';
ERROR: 1099: Table 'country' was locked with a READ lock and can't be updated
Connection 1> SELECT *
FROM sakila.film
WHERE film_id = 1;
ERROR: 1100: Table 'film' was not locked with LOCK TABLES
由于显式锁被视为元数据锁，performance_schema.metadata_locks表中的症状和信息与隐式元数据锁相同，您也可以使用UNLOCK TABLES语句解锁表:
Connection 1> UNLOCK TABLES;
Query OK, 0 rows affected (0.0006 sec)
另一种隐式处理的表级锁被称为表锁。
隐式表锁
当查询一个表时，MySQL 采用隐式表锁。除了刷新、元数据和显式锁之外，表锁对 InnoDB 表没有太大作用，因为 InnoDB 使用记录锁来允许对表的并发访问，只要事务不修改相同的行(粗略地说——如下一章所示——还有更多内容)。
然而，InnoDB 确实在表级别使用了意向锁的概念。由于您在研究锁问题时可能会遇到这些问题，因此有必要熟悉一下它们。正如在锁访问级别的讨论中提到的，意图锁标记了事务的意图。
对于由事务获取的锁，首先获取一个意向锁，然后如果需要的话可以升级它。这不同于不变的显式LOCK TABLES。为了获得共享锁，事务首先获取意向共享锁，然后获取共享锁。类似地，对于排他锁，首先采用意图排他锁。意向锁定的一些示例如下:
-
一个
SELECT ... FOR SHARE语句在被查询的表上获取一个意向共享锁。SELECT ... LOCK IN SHARE MODE语法是同义词。 -
一个
SELECT ... FOR UPDATE语句在被查询的表上获取一个意向排他锁。 -
一个 DML 语句(不包括
SELECT)在修改后的表上获取一个意向排他锁。如果修改了外键列，就会在父表上获得一个意向共享锁。
可以在LOCK_TYPE列设置为TABLE的performance_schema.data_locks表中找到表级锁。清单 6-8 展示了一个意向共享锁的例子。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 446 796 6
-- 2 447 797 6
-- Connection 1
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0003 sec)
Connection 1> SELECT *
FROM world.city
WHERE ID = 130
FOR SHARE\G
*************************** 1\. row ***************************
ID: 130
Name: Sydney
CountryCode: AUS
District: New South Wales
Population: 3276207
1 row in set (0.0010 sec)
-- Connection 2
Connection 2> SELECT engine, thread_id, object_schema,
object_name, lock_type, lock_mode,
lock_status, lock_data
FROM performance_schema.data_locks
WHERE lock_type = 'TABLE'
AND thread_id = 796\G
*************************** 1\. row ***************************
engine: INNODB
thread_id: 796
object_schema: world
object_name: city
lock_type: TABLE
lock_mode: IS
lock_status: GRANTED
lock_data: NULL
1 row in set (0.0011 sec)
-- Connection 1
Connection 1> ROLLBACK;
Query OK, 0 rows affected (0.0004 sec)
Listing 6-8Example of an InnoDB intention shared lock
这显示了一个在world.city表上的意向共享锁。注意，engine被设置为INNODB，而lock_data为NULL。
备用锁
备份锁是实例级锁；也就是说，它影响整个系统。它是 MySQL 8 中引入的新锁。备份锁防止可能导致备份不一致的语句，同时仍然允许其他语句与备份同时执行。目前，备份锁的主要用户是 MySQL Enterprise Backup，它与日志锁一起使用，以避免对 InnoDB 表执行FLUSH TABLES WITH READ LOCK。被阻止的语句包括
-
创建、重命名或删除文件的语句。这些语句包括
CREATE TABLE、CREATE TABLESPACE、RENAME TABLE和DROP TABLE语句。 -
CREATE USER、ALTER USER、DROP USER、GRANT等账户管理报表。 -
不将其更改记录到重做日志中的 DDL 语句。例如，这些包括添加索引。
用LOCK INSTANCE FOR BACKUP语句创建备份锁，用UNLOCK INSTANCE语句释放锁。执行LOCK INSTANCE FOR BACKUP需要BACKUP_ADMIN权限。获取备份锁并再次释放它的一个示例是
mysql> LOCK INSTANCE FOR BACKUP;
Query OK, 0 rows affected (0.0002 sec)
mysql> UNLOCK INSTANCE;
Query OK, 0 rows affected (0.0003 sec)
Note
在编写时，使用 X 协议(通过用mysqlx_port指定的端口或用mysqlx_socket指定的套接字连接)时，不允许获取备份锁并释放它。尝试这样做将返回一个ER_PLUGGABLE_PROTOCOL_COMMAND_NOT_SUPPORTED错误:ERROR: 3130: Command not supported by pluggable protocols。
此外，与备份锁冲突的语句也会使用备份锁。由于 DDL 语句有时由几个步骤组成，例如，在新文件中重建一个表并重命名文件，备份锁可以在这些步骤之间释放，以避免阻塞LOCK INSTANCE FOR BACKUP超过必要的时间。
备份锁可以在performance_schema.metadata_locks表中找到，其中OBJECT_TYPE列设置为BACKUP LOCK。清单 6-9 展示了一个查询等待LOCK INSTANCE FOR BACKUP持有的备份锁的例子。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 484 851 1
-- 2 485 852 1
-- 3 486 853 1
-- Connection 1
Connection 1> LOCK INSTANCE FOR BACKUP;
Query OK, 0 rows affected (0.0004 sec)
-- Connection 2
Connection 2> OPTIMIZE TABLE world.city;
-- Connection 3
Connection 3> SELECT object_type, object_schema, object_name,
lock_type, lock_duration, lock_status,
owner_thread_id
FROM performance_schema.metadata_locks
WHERE object_type = 'BACKUP LOCK'
AND owner_thread_id IN (851, 852)\G
*************************** 1\. row ***************************
object_type: BACKUP LOCK
object_schema: NULL
object_name: NULL
lock_type: SHARED
lock_duration: EXPLICIT
lock_status: GRANTED
owner_thread_id: 851
*************************** 2\. row ***************************
object_type: BACKUP LOCK
object_schema: NULL
object_name: NULL
lock_type: INTENTION_EXCLUSIVE
lock_duration: TRANSACTION
lock_status: PENDING
owner_thread_id: 852
2 rows in set (0.0007 sec)
-- Connection 1
Connection 1> UNLOCK INSTANCE;
Query OK, 0 rows affected (0.0003 sec)
Listing 6-9Example of a conflict for the backup lock
在本例中，线程 id 为 851 的连接拥有备份锁，而线程 id 为 852 的连接正在等待它。注意LOCK INSTANCE FOR BACKUP持有一个共享锁，而 DDL 语句请求一个意向排他锁。
与备份锁相关的是日志锁，它的引入也是为了减少备份过程中的锁定。
日志锁
当您创建备份时，您通常希望包含与备份一致的日志位置和 GTID 集的相关信息。在 MySQL 5.7 和更早的版本中，在获取这些信息时需要全局读锁。在 MySQL 8 中，引入了日志锁，允许您在不使用全局读锁的情况下读取 InnoDB 的信息，如执行的全局事务标识符(GTIDs)、二进制日志位置和日志序列号(LSN)。
日志锁防止对日志相关信息进行更改的操作。实际上，这意味着提交、FLUSH LOGS等等。日志锁是通过查询performance_schema.log_status表隐式获取的。它需要BACKUP_ADMIN特权来访问表。清单 6-10 显示了log_status表的输出示例。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 490 857 6
-- Connection 1
Connection 1 > SELECT *
FROM performance_schema.log_status\G
*************************** 1\. row ***************************
SERVER_UUID: fcbb7afc-bdde-11ea-b95f-ace2d35785be
LOCAL: {"gtid_executed": "d2549c41-86ca-11ea-9dc7-ace2d35785be:1-351", "binary_log_file": "binlog.000002", "binary_log_position": 39348}
REPLICATION: {"channels": [{"channel_name": "", "relay_log_file": "relay-bin.000002", "relay_log_position": 39588}]}
STORAGE_ENGINES: {"InnoDB": {"LSN": 2073604726, "LSN_checkpoint": 2073604726}}
1 row in set (0.0012 sec)
Listing 6-10Example output of the log_status table
可用信息取决于实例的配置，值取决于使用情况。
对 MySQL 中主要高锁类型的回顾到此结束。
摘要
本章已经讨论了高级锁类型。这些锁大多独立于所使用的存储引擎，包括从用户级和实例级锁到表和元数据锁的一系列锁。
用户级锁可用于保护应用中的工作流，是最通用的锁类型。刷新表时会遇到刷新锁，由于低级表定义缓存(TDC)版本锁，刷新锁会导致难以诊断的问题。元数据锁保护模式对象的元数据，例如表的列定义。
有显式表锁和隐式表锁，其中隐式锁在使用 InnoDB 表时最为常见。隐式锁也包括意图锁。
在实例级别，有两种考虑到备份而开发的锁类型。备份锁防止会使备份不一致的更改，如用户和权限的更改以及某些模式的更改。日志锁是一种隐式锁，在查询performance_schema.log_status时使用，以确保日志相关的状态值可以用最小的锁定以一致的方式获得。
除了高级锁之外，InnoDB 还有自己的一套工作在记录级的锁，这将在下一章讨论。
Footnotes [1](#Fn1_source)https://bugs.mysql.com/bug.php?id=44884
七、InnoDB 锁
除了 InnoDB 意向锁之外，前一章研究的锁都是 MySQL 的通用锁。InnoDB 拥有自己复杂的锁定系统，允许高度并发地访问数据。在在线事务处理(OLTP)工作负载中，基准测试表明，根据工作负载的不同，InnoDB 可以很好地处理多达 100 多个并发查询。 1 这不仅与记录级锁有关，还与低级信号量有关，后者是一个正在改进的领域，这也是 MySQL 新版本比旧版本更好地处理并发的主要原因。
Tip
MySQL 的新版本比旧版本支持更高程度的并发查询执行。在最新的 8.0.21 中，锁系统互斥被分片以减少高并发系统上的争用。
在本章中，首先将讨论 InnoDB 记录锁和 next-key 锁，然后讨论间隙锁和谓词锁。所涉及的最后一个数据级锁是自动增量锁，它对于在高并发插入时保持良好的性能也很重要。本章的最后一个主题是信号量。
记录锁和下一键锁
记录锁通常被称为行锁；但是，它不仅仅是行上的锁，因为它还包括索引和间隙锁。相关的是下一键锁。下一键锁是记录锁和记录前间隙上的间隙锁的组合。下一键锁实际上是 InnoDB 中的默认锁类型，因此在锁输出中您只会看到 S(共享)和 X(独占)。
记录锁和下一键锁通常是指 InnoDB 锁。它们是细粒度的锁，旨在锁定最少量的数据，同时仍然确保数据的完整性。
记录锁或下一键锁可以是共享的，也可以是排他的，并且只影响事务访问的行和索引。排他锁的持续时间通常是有例外的事务，例如，INSERT INTO ... ON DUPLICATE KEY和REPLACE语句中用于唯一性检查的删除标记的记录。对于共享锁，持续时间可以取决于第 9 和 12 章中讨论的事务隔离级别。
使用performance_schema.data_locks表可以找到记录和下一键锁。清单 7-1 展示了一个使用二级索引CountryCode更新world.city表中的行的锁的例子。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 544 919 6
-- 2 545 920 6
-- Connection 1
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0002 sec)
Connection 1> UPDATE world.city
SET Population = Population + 1
WHERE CountryCode = 'LUX';
Query OK, 1 row affected (0.0008 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- Connection 2
Connection 2> SELECT thread_id, event_id,
object_schema, object_name, index_name,
lock_type, lock_mode, lock_status, lock_data
FROM performance_schema.data_locks
WHERE thread_id = 919\G
*************************** 1\. row ***************************
thread_id: 919
event_id: 10
object_schema: world
object_name: city
index_name: NULL
lock_type: TABLE
lock_mode: IX
lock_status: GRANTED
lock_data: NULL
*************************** 2\. row ***************************
thread_id: 919
event_id: 10
object_schema: world
object_name: city
index_name: CountryCode
lock_type: RECORD
lock_mode: X
lock_status: GRANTED
lock_data: 'LUX', 2452
*************************** 3\. row ***************************
thread_id: 919
event_id: 10
object_schema: world
object_name: city
index_name: PRIMARY
lock_type: RECORD
lock_mode: X,REC_NOT_GAP
lock_status: GRANTED
lock_data: 2452
*************************** 4\. row ***************************
thread_id: 919
event_id: 10
object_schema: world
object_name: city
index_name: CountryCode
lock_type: RECORD
lock_mode: X,GAP
lock_status: GRANTED
lock_data: 'LVA', 2434
4 rows in set (0.0014 sec)
-- Connection 1
Connection 1> ROLLBACK;
Query OK, 0 rows affected (0.1702 sec)
Listing 7-1Example of InnoDB record locks
第一行是已经讨论过的意图排他表锁。第二行是值(' LUX '，2452)在CountryCode索引上的 next-key 锁，其中' LUX '是在WHERE子句中使用的国家代码，2452 是添加到非唯一二级索引的主键 id。带有ID = 2452的城市是唯一匹配WHERE子句的城市，主键记录(行本身)显示在输出的第三行。锁定模式是X,REC_NOT_GAP，这意味着它是记录上的排他锁，而不是间隙上的排他锁。
什么是差距？输出的第四行显示了一个示例。间隙锁如此重要，以至于关于间隙锁的讨论被分成单独的部分。
间隙锁
间隙锁保护两条记录之间的空间。这可以在聚集索引的行中，也可以在辅助索引中。在索引页的第一条记录之前和最后一条记录之后，分别有称为下确界记录和上确界记录的伪记录。间隙锁通常是最容易引起混淆的锁类型。研究锁问题的经验是熟悉它们的最好方法。
考虑前面示例中的查询:
UPDATE world.city
SET Population = Population + 1
WHERE CountryCode = 'LUX';
该查询更改所有带有CountryCode = 'LUX'的城市的人口。如果在事务的更新和提交之间插入一个新的城市，会发生什么情况？如果UPDATE和INSERT语句提交的顺序与它们执行的顺序相同，一切都没问题。但是，如果以相反的顺序提交更改，结果将会不一致，因为预计插入的行也将被更新。
这就是间隙锁发挥作用的地方。它保护插入新记录(包括从不同位置移动的记录)的空间，因此在持有间隙锁的事务完成之前，它不会被更改。如果您查看清单 7-1 中示例输出的第四行，您可以看到一个间隙锁的示例:
*************************** 4\. row ***************************
thread_id: 919
event_id: 10
object_schema: world
object_name: city
index_name: CountryCode
lock_type: RECORD
lock_mode: X,GAP
lock_status: GRANTED
lock_data: 'LVA', 2434
4 rows in set (0.0014 sec)
这是值(' LVA '，2434)的CountryCode索引上的独占间隙锁。由于该查询请求更新所有将CountryCode设置为“LUX”的行，间隙锁确保没有为“LUX”国家代码插入新行。国家代码“LVA”是CountryCode索引中的下一个值，因此“勒克司”和“LVA”之间的差距受到独占锁的保护。另一方面，用CountryCode = 'LVA'插入新城市还是有可能的。在某些地方，这被称为“记录前间隙”,这样更容易理解间隙锁是如何工作的。
间隙锁的一个特点是间隙锁不会与另一个间隙锁冲突，即使两者都是互斥的。间隙锁的目的不是防止对间隙的访问，而是专门防止将数据插入间隙。在讨论插入意图锁时，您将看到间隙锁是如何阻塞插入的。
当您使用READ COMMITTED事务隔离级别而不是REPEATABLE READ或SERIALIZABLE时，间隙锁被采用的程度要小得多。
与间隙锁相关的是谓词锁。
谓词和页锁
谓词锁类似于间隙锁，但它适用于无法进行绝对排序的空间索引，因此间隙锁没有意义。对于REPEATABLE READ和SERIALIZABLE事务隔离级别中的空间索引，InnoDB 在用于查询或整个页面的最小边界矩形(MBR)上创建一个谓词锁，而不是间隙锁。这将通过防止对最小边框或页面内的数据进行更改来实现一致的读取。
当查询performance_schema.data_locks表时，谓词锁将有PREDICATE或PRDT_PAGE，后者是一个页锁。
作为谓词锁的一个例子，考虑数据库sakila中的address表。其中的列location属于几何数据类型，空间参考系统标识符(SRID)设置为 0。(MySQL 8 中需要一个 SRID 来建立空间索引。)在location列上的索引被命名为idx_location。清单 7-2 展示了在更新其中一个地址时如何使用谓词锁。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 562 954 6
-- 2 563 955 6
-- Connection 1
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0002 sec)
Connection 1> UPDATE sakila.address
SET address = '42 Concurrency Boulevard',
district = 'Punjab',
city_id = 208,
postal_code = 40509,
location = ST_GeomFromText('POINT(75.91 31.53)', 0)
WHERE address_id = 372;
Query OK, 1 row affected (0.0008 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- Connection 2
Connection 2> SELECT engine_lock_id, thread_id, event_id,
object_schema, object_name, index_name,
lock_type, lock_mode, lock_status, lock_data
FROM performance_schema.data_locks
WHERE thread_id = 954
AND index_name = 'idx_location'\G
*************************** 1\. row ***************************
engine_lock_id: 2123429833312:1074:12:0:2123393008216
thread_id: 954
event_id: 10
object_schema: sakila
object_name: address
index_name: idx_location
lock_type: RECORD
lock_mode: S,PRDT_PAGE
lock_status: GRANTED
lock_data: infimum pseudo-record
*************************** 2\. row ***************************
engine_lock_id: 2123429833312:1074:13:0:2123393008560
thread_id: 954
event_id: 10
object_schema: sakila
object_name: address
index_name: idx_location
lock_type: RECORD
lock_mode: S,PRDT_PAGE
lock_status: GRANTED
lock_data: infimum pseudo-record
2 rows in set (0.0006 sec)
-- Connection 1
Connection 1> ROLLBACK;
Query OK, 0 rows affected (0.0435 sec)
Listing 7-2Example of predicate/page locks
更新的重要部分是改变了location列。在data_locks表的输出中，可以看到一个谓词页锁被占用了。
您应该知道的与记录相关的最后一种锁类型是插入意图锁。
插入意向锁
请记住，对于表锁，InnoDB 有意向锁，决定事务是以共享还是独占的方式使用表。类似地，InnoDB 在记录级别有插入意图锁。InnoDB 使用这些锁——顾名思义——和INSERT语句向其他事务发出信号。因此，锁是在一个尚未创建的记录上(因此它是一个间隙锁)，而不是在一个现有的记录上。使用插入意图锁有助于提高执行插入的并发性。
您不太可能在锁输出中看到插入意图锁，除非一个INSERT语句正在等待一个锁被授予。您可以通过在另一个事务中创建一个间隙锁来阻止INSERT语句完成，从而强制出现这种情况。清单 7-3 中的例子在连接 1 中创建了一个间隙锁，然后在连接 2 中试图插入一个与间隙锁冲突的行。最后，在第三个连接中，检索锁信息。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 577 972 6
-- 2 578 973 6
-- 3 579 974 6
-- Connection 1
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0003 sec)
Connection 1> SELECT *
FROM world.city
WHERE ID > 4079
FOR UPDATE\G
0 rows in set (0.0007 sec)
-- Connection 2
Connection 2> START TRANSACTION;
Query OK, 0 rows affected (0.0002 sec)
Connection 2> INSERT INTO world.city
VALUES (4080, 'Darwin', 'AUS',
'Northern Territory', 146000);
-- Connection 3
Connection 3> SELECT thread_id, event_id,
object_schema, object_name, index_name,
lock_type, lock_mode, lock_status, lock_data
FROM performance_schema.data_locks
WHERE thread_id IN (972, 973)
AND object_name = 'city'
AND index_name = 'PRIMARY'\G
*************************** 1\. row ***************************
thread_id: 972
event_id: 10
object_schema: world
object_name: city
index_name: PRIMARY
lock_type: RECORD
lock_mode: X
lock_status: GRANTED
lock_data: supremum pseudo-record
*************************** 2\. row ***************************
thread_id: 973
event_id: 10
object_schema: world
object_name: city
index_name: PRIMARY
lock_type: RECORD
lock_mode: X,INSERT_INTENTION
lock_status: WAITING
lock_data: supremum pseudo-record
2 rows in set (0.0007 sec)
-- Connection 1
Connection 1> ROLLBACK;
Query OK, 0 rows affected (0.0003 sec)
-- Connection 2
Connection 2> ROLLBACK;
Query OK, 0 rows affected (0.3035 sec)
Listing 7-3Example of an insert intention lock
注意对于RECORD锁，锁模式包括INSERT_INTENTION——插入意图锁。在这种情况下，锁定的数据是上确界伪记录，但根据具体情况，它也可以是主键的值。如果您还记得下一个键锁的讨论，那么 X 表示下一个键锁，但是这是一个特例，因为锁位于上确界伪记录上，并且不可能锁定它，所以实际上它只是上确界伪记录之前的间隙上的间隙锁。
插入数据时需要注意的另一个锁是自动增量锁。
自动增量锁
当您将数据插入到具有自动递增计数器的表中时，有必要保护计数器，以便保证两个事务获得唯一的值。如果对二进制日志使用基于语句的日志记录，则会有进一步的限制，因为在重播语句时，将为除第一行之外的所有行重新创建自动增量值。
InnoDB 支持三种锁定模式，因此您可以根据需要调整锁定量。使用innodb_autoinc_lock_mode选项选择锁定模式，该选项取值为 0、1 和 2，MySQL 8 中的默认值为 2。它需要重新启动 MySQL 来改变这个值。表 7-1 中总结了这些值的含义。
表 7-1
innodb_autoinc_lock_mode选项支持的值
价值
|
方式
|
描述
|
| --- | --- | --- |
| Zero | 传统的 | MySQL 5.0 及更早版本的锁定行为。锁一直保持到语句结束，所以值是以可重复的连续顺序赋值的。 |
| one | 连续的 | 对于查询开始时行数已知的INSERT语句，所需数量的自动增量值被分配在一个轻量级互斥体下，并且避免了自动增量锁。对于行数未知的语句，自动增量锁被获取并保持到语句结束。这是 MySQL 5.7 和更早版本的默认设置。 |
| Two | 插入纸 | 自动增量锁永远不会被占用，并发插入的自动增量值可能是交错的。只有当二进制记录被禁用或binlog_format被设置为ROW时，该模式才是安全的。它是 MySQL 8 中的默认值。 |
innodb_autoinc_lock_mode的值越高，锁定越少。为此付出的代价是增加自动增量值序列中的间隙数量，以及innodb_autoinc_lock_mode = 2交错值的可能性。除非不能使用基于行的二进制日志记录，或者对连续的自动增量值有特殊需求，否则建议使用值 2。
数据级锁的讨论到此结束，但是在讨论 MySQL 并发性时，还有一个重要的话题:互斥体和 rw 锁信号量。
互斥和读写锁信号量
在 MySQL 源代码内部，有必要保护代码路径。一个例子是保护修改缓冲池内容的代码，以避免两个线程同时修改缓冲池内容，从而可能导致冲突更改。在某种程度上，您可以将互斥锁与用户级锁进行比较，只是前者用于 MySQL 代码路径，后者用于使用 MySQL 的应用代码路径。
Note
InnoDB 在某种程度上互换使用术语互斥和信号量。例如，InnoDB 监控器中的SEMAPHORES部分也包含互斥等待的信息，而SHOW ENGINE INNODB MUTEX包含信号量。
不是只有 InnoDB 在 MySQL 中使用同步对象；例如，表 open cache 也由互斥体保护。然而，在大多数情况下，当您在同步对象上遇到争用问题时，这与 InnoDB 有关，因为高并发性操作的压力通常是最大的，对于 InnoDB 来说，有现成的监控工具来调查争用。因此，这里只讨论 InnoDB。
互斥体和信号量比数据锁更难研究，因为不可能在锁就位时暂停代码执行并直接研究它们。(是的，但是这需要使用调试器，比如gdb和使用断点。)即使在性能模式中启用了同步等待，您通常也会有所欠缺，因为会有许多等待，即使是默认为 10000 行的长历史表也会很快被一个连接耗尽，如清单 7-4 所示。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 638 1057 6
-- 2 639 1058 6
-- Connection 1
Connection 1> UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES',
TIMED = 'YES'
WHERE NAME LIKE 'wait/synch/%';
Query OK, 323 rows affected (0.0230 sec)
Rows matched: 323 Changed: 323 Warnings: 0
Connection 1> UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME IN ('events_waits_current', 'events_waits_history_long');
Query OK, 2 rows affected (0.0004 sec)
Rows matched: 2 Changed: 2 Warnings: 0
-- Connection 2
Connection 2> UPDATE world.city
SET Population = Population + 1
WHERE CountryCode = 'USA';
Query OK, 274 rows affected (0.1522 sec)
Rows matched: 274 Changed: 274 Warnings: 0
-- Connection 1
Connection 1> SELECT REPLACE(event_name, 'wait/synch/', '') AS event, COUNT(*)
FROM performance_schema.events_waits_history_long
WHERE thread_id = 1058
AND event_name LIKE 'wait/synch/%'
GROUP BY event_name
WITH ROLLUP
ORDER BY COUNT(*);
+----------------------------------------------+----------+
| event | COUNT(*) |
+----------------------------------------------+----------+
| mutex/sql/MYSQL_BIN_LOG::LOCK_done | 1 |
| mutex/innodb/purge_sys_pq_mutex | 1 |
| mutex/sql/MYSQL_BIN_LOG::LOCK_sync | 1 |
| mutex/sql/MYSQL_BIN_LOG::LOCK_log | 1 |
| mutex/mysqlx/vio_shutdown | 1 |
| mutex/sql/LOCK_plugin | 1 |
| mutex/sql/LOCK_slave_trans_dep_tracker | 1 |
| mutex/sql/MYSQL_BIN_LOG::LOCK_binlog_end_pos | 1 |
| mutex/sql/MYSQL_BIN_LOG::LOCK_commit | 1 |
| mutex/sql/MYSQL_BIN_LOG::LOCK_xids | 1 |
| sxlock/innodb/rsegs_lock | 1 |
| sxlock/innodb/undo_spaces_lock | 1 |
| mutex/sql/MYSQL_BIN_LOG::LOCK_sync_queue | 2 |
| mutex/innodb/lock_sys_table_mutex | 2 |
| mutex/sql/MYSQL_BIN_LOG::LOCK_flush_queue | 2 |
| mutex/sql/Gtid_state | 2 |
| mutex/sql/LOCK_table_cache | 2 |
| mutex/sql/MYSQL_BIN_LOG::LOCK_commit_queue | 2 |
| mutex/sql/THD::LOCK_thd_query | 2 |
| mutex/innodb/undo_space_rseg_mutex | 3 |
| mutex/sql/THD::LOCK_thd_data | 3 |
| rwlock/sql/gtid_commit_rollback | 3 |
| mutex/mysys/THR_LOCK_open | 4 |
| mutex/sql/THD::LOCK_query_plan | 4 |
| mutex/innodb/flush_list_mutex | 5 |
| sxlock/innodb/index_tree_rw_lock | 5 |
| mutex/innodb/trx_undo_mutex | 274 |
| mutex/innodb/trx_sys_mutex | 279 |
| sxlock/innodb/hash_table_locks | 288 |
| sxlock/innodb/btr_search_latch | 550 |
| sxlock/innodb/lock_sys_global_rw_lock | 551 |
| sxlock/innodb/log_sn_lock | 551 |
| mutex/innodb/lock_sys_page_mutex | 554 |
| mutex/innodb/trx_mutex | 850 |
| NULL | 3950 |
+----------------------------------------------+----------+
35 rows in set (0.0173 sec)
Connection 1> UPDATE performance_schema.setup_instruments
SET ENABLED = 'NO',
TIMED = 'NO'
WHERE NAME LIKE 'wait/synch/%';
Query OK, 323 rows affected (0.0096 sec)
Rows matched: 323 Changed: 323 Warnings: 0
Connection 1> UPDATE performance_schema.setup_consumers
SET ENABLED = 'NO'
WHERE NAME IN ('events_waits_current', 'events_waits_history_long');
Query OK, 2 rows affected (0.0004 sec)
Rows matched: 2 Changed: 2 Warnings: 0
Listing 7-4Example of synchronization waits
在这个简单的例子中，请求了将近 4000 个同步对象(查询结果底部的第NULL行)。等待的确切列表和数量会因执行和系统的不同而不同，这取决于系统的状态(比如数据是否已经在缓冲池中)和配置。如果有足够多的其他活动，这个数字可能会低得多，因为一些等待可能已经被更新的事件推出了events_waits_history_long表。更复杂的是，后台线程也会生成等待事件，所以即使系统没有连接，也会创建等待事件。
虽然很难建立测试用例来演示单个同步对象的使用，但好消息是，作为最终用户，您最需要担心的是争用，SHOW ENGINE INNODB STATUS和SHOW ENGINE INNODB MUTEX语句将为您提供关于 InnoDB 互斥体和信号量争用的信息。
一般来说，你需要研究源代码来理解等待是为了什么；然而，单独考虑文件通常可以很好地指示发生争用的功能区域。表 7-2 展示了几个如何将互斥体和信号量信息提供的文件名映射到功能区的例子。源代码路径与包含 InnoDB 存储引擎实现的storage/innobase相关。
表 7-2
互斥和信号量文件名及其功能区
|文件名
|
源代码路径
|
功能区
|
| --- | --- | --- |
| btr0sea.cc | btr/btr0sea.cc | 自适应哈希索引。 |
| buf0buf.cc | buf/buf0buf.cc | 缓冲池。 |
| buf0flu.cc | buf/buf0flu.cc | 缓冲池刷新算法。 |
| dict0dict.cc | dict/dict0dict.cc | InnoDB 数据字典。 |
| sync0sharded_rw.h | include/sync0sharded_rw.h | 线程的分片读写锁。 |
| hash0hash.cc | ha/hash0hash.cc | 用于保护哈希表。 |
| fil0fil.cc | fil/fil0fil.cc | 表空间内存缓存。 |
除了在头文件中实现的互斥体和信号量，一般来说，您可以通过使用文件名中 0 之前的名称(例如btr0sea.cc中的btr)作为目录名，然后使用文件名本身来访问源代码文件。如果你在编辑器中打开这个文件，那么在许可证和版权标题之后，你会看到一个简短的注释，描述这个文件的用途，例如来自storage/innobase/btr/btr0sea.cc:
/** @file btr/btr0sea.cc
The index tree adaptive search
Created 2/17/1996 Heikki Tuuri
*************************************************************************/
因此，btr0sea.cc文件在索引树上实现了自适应搜索，自适应散列索引是索引树的一部分(也是最常发生争用的地方)。
WHY INNOBASE? A BRIEF HISTORY OF INNODB
您可能会感到困惑，为什么通往 innodb 源代码的路径是使用“innobase”而不是“InnoDB”的storage/innobase/要理解这一点，您需要深入了解 InnoDB 的历史——这非常有趣。
Innobase 是 Heikki Tuuri 在 1995 年成立的公司(没错，就是在文件storage/innobase/btr/btr0sea.cc的评论中列出的那家)，同年 MySQL 首次发布，但当时这两家公司还没有任何关系。Innobase 用于开发 InnoDB，在当时，这意味着是一个独立的产品。直到后来 MySQL 增加了对第三方存储引擎的支持，Heikki 才把 InnoDB 作为开源发布，并与 MySQL 进行了集成。
2005 年，Oracle 收购了 Innobase 和 InnoDB，这导致了一个有趣的情况，MySQL 的主要事务存储引擎(另一个使用较少的引擎是 BDB 的 Berkley DB，它也被 Oracle 收购了)由竞争对手维护。这也是为 MySQL 6 开发 Falcon 存储引擎的原因之一。然而，在这项工作完成之前，Sun Microsystems 收购了 MySQL，Oracle 又收购了 Sun Microsystems，因此在 2010 年，MySQL 和 InnoDB 终于成为了同一家公司的一部分，今天 InnoDB 和 MySQL 是由 Oracle 内部的同一部门开发的。这也意味着 Falcon 存储引擎被放弃，永远不会以正式发布(GA)状态发布。
虽然 Innobase 作为一家公司已经消失很久了，但它的名字仍然存在于 MySQL 源代码中，既存在于 InnoDB 源代码的路径中，也作为源代码中的名称。
摘要
本章介绍了 InnoDB 数据级锁以及互斥和读写锁信号量。这些锁对于支持对 InnoDB 数据的并发访问非常重要，这是 InnoDB 的优势之一。
首先，讨论了记录锁和下一键锁。在讨论 InnoDB 记录锁时，这通常就是所指的内容。next-key 锁是 InnoDB 中的默认锁，保护记录以及记录前的 gab。其次，讨论了间隙锁的概念。当提到间隙锁时，它指的是保护两个记录之间的空间，而不保护记录本身。第三，讲述了与空间索引一起使用的谓词和页锁的相关概念。
第四种和第五种是两种锁类型，您遇到的程度不会与前三种锁类型相同。插入意图锁顾名思义与插入数据结合使用，自动增量锁用于确保自动增量值被正确分配。
第六，也是最后一点，主要讨论了 InnoDB 中的互斥和读写锁信号量。这些是最复杂的锁，在很大程度上需要研究源代码。
以上是 MySQL 和 InnoDB 中可用锁的概述。下一章继续讨论当锁不能被获取时会发生什么。
Footnotes [1](#Fn1_source)http://dimitrik.free.fr/blog/posts/mysql-performance-80-ga-and-tpcc-workloads.html
八、处理锁冲突
锁的整体思想是限制对对象或记录的访问，以避免冲突的操作，从而以安全的方式同时访问对象或记录。这意味着，有时锁不能被授予。那种情况下会发生什么？这取决于请求的锁和环境。元数据(包括显式请求的表锁)和 InnoDB 锁在超时的情况下运行，对于某些锁情况，存在显式死锁检测。
使用数据库时，无法获得锁是不可避免的，理解这一点很重要。原则上，您可以使用非常粗粒度的锁并避免失败的锁，除非超时——这就是 MyISAM 存储引擎在写入并发性非常差的情况下所做的事情。然而，在实践中，为了允许写工作负载的高并发性，优选细粒度锁，这也引入了死锁的可能性。
结论是，您应该始终让您的应用准备好重试获取锁或优雅地失败。无论是显式锁还是隐式锁，这都适用。
Tip
总是准备好处理失败以获得锁。无法获得锁并不是一个灾难性的错误，通常不应该被认为是一个 bug。也就是说，正如第 9 章“减少锁定问题”中所讨论的，在开发应用时，有一些减少锁争用的技术值得考虑。
本章的其余部分将讨论当有多个对同一数据锁的请求时，InnoDB 如何选择哪个事务应该首先被授予锁请求，InnoDB 数据锁的兼容性，以及表级超时、记录级超时、InnoDB 死锁、InnoDB 互斥和信号量等待的细节。
竞争感知事务调度(CATS)
当对同一个锁有多个请求时，一个重要的决策是决定应该以什么顺序授予锁。最简单的解决方案，也是数据库中最常用的解决方案，是维护一个队列，并根据先进先出(FIFO)原则来处理请求。这也是 MySQL 5.7 及更早版本中锁的授予方式；然而在 MySQL 8 中，实现了一个新的调度算法。
新的实现基于竞争感知事务调度(CATS)算法，该算法由密歇根大学的巴尔赞·莫扎法里教授的团队开发，并由桑尼·贝恩斯与莫扎法里教授的团队(尤其是黄嘉敏)合作在 MySQL 中实现。
Tip
如果你想了解更多关于 CATS 算法的知识，那么 https://mysqlserverteam.com/contention-aware-transaction-scheduling-arriving-in-innodb-to-boost-performance/ 是一个很好的起点，评论中有两篇研究论文的链接——主要论文是 http://web.eecs.umich.edu/~mozafari/php/data/uploads/pvldb_2018_sched.pdf 。另一个来源是参考手册中的 https://dev.mysql.com/doc/refman/en/innodb-transaction-scheduling.html 。
CATS 算法的工作原理是，已经持有大量锁的事务对于完成最重要，因此为了所有事务的利益，可以尽快释放它们的锁。这种方法的一个潜在缺点是，如果不断有具有许多现有锁的事务等待给定的锁，那么它们可能会使具有很少锁的事务永远得不到锁。为了防止这种情况，算法有防止饥饿的安全措施。这种保护的工作方式是在当前锁请求队列的末尾添加一个屏障，在屏障前面的所有请求都得到处理，然后再考虑后面到达的请求。
CATS 算法的主要优点是在高并发工作负载下，直到 MySQL 8.0.20，它只在 InnoDB 检测到大量锁争用时使用。在 8.0.20 中对算法进行了改进，提高了可扩展性，在information_schema.INNODB_TRX中增加了trx_schedule_weight列，这样就可以根据 CATS 算法查询一个处于LOCK WAIT状态的事务当前拥有的权重。与此同时，它被改变了，所以 CATS 算法总是被使用，FIFO 算法已经退休。
InnoDB 数据锁兼容性
记住，在讨论锁访问级别兼容性时，规则相对简单。然而，确定两个 InnoDB 数据锁是否相互兼容是非常复杂的。这变得特别有趣，因为这种关系是不对称的，也就是说，一个锁可以在另一个锁存在时被允许，但反之则不行。例如，插入意图锁必须等待间隙锁，但是间隙锁不必等待插入意图锁。另一个例子(缺乏传递性)是间隙加记录锁必须等待仅记录锁，插入意图锁必须等待间隙加记录锁，但是插入意图锁不需要等待仅记录锁。
这对你意味着什么？这意味着当您调查锁争用问题时，您需要意识到锁的顺序非常重要，因此在重现该问题时，所有锁必须以相同的顺序获得。
关于 InnoDB 算法处理锁争用背后的理论以及可能导致锁不被授予的原因已经讲得够多了。当锁不能被授予时会发生什么是下一个要讨论的主题。
元数据和备份锁等待超时
当您请求刷新、元数据或备份锁时，获取锁的尝试将在lock_wait_timeout秒后超时。默认超时为 31，536，000 秒(365 天)。您可以在全局和会话范围内动态设置lock_wait_timeout选项，这允许您根据给定流程的特定需求调整超时。
当超时发生时，语句失败，出现一个ER_LOCK_WAIT_TIMEOUT(错误号 1205)错误，如清单 8-1 所示。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 647 1075 6
-- 2 648 1076 6
-- Connection 1
Connection 1> LOCK TABLES world.city WRITE;
Query OK, 0 rows affected (0.0015 sec)
-- Connection 2
Connection 2> SET SESSION lock_wait_timeout = 5;
Query OK, 0 rows affected (0.0003 sec)
Connection 2> LOCK TABLES world.city WRITE;
ERROR: 1205: Lock wait timeout exceeded; try restarting transaction
-- Connection 1
Connection 1> UNLOCK TABLES;
Query OK, 0 rows affected (0.0003 sec)
Listing 8-1Lock wait timeout for table lock request
lock_wait_timeout的会话值设置为 5 秒，以减少连接 2 在试图获取world.city表上的写锁时阻塞的时间。等待 5 秒钟后，返回错误，错误号设置为 1205。
lock_wait_timeout选项的推荐设置取决于应用的要求。使用较小的值来防止锁请求长时间阻塞其他查询可能是一个优势。这通常需要您实现对锁请求失败的处理，例如，通过重试该语句。另一方面，较大的值有助于避免重试该语句。
对于FLUSH TABLES语句，还要记住它与低级表定义缓存(TDC)版本锁交互，这可能意味着放弃该语句不允许后续查询继续进行。在这种情况下，lock_wait_timeout的值越大越好，这样可以更清楚地了解锁的关系。
InnoDB 锁等待超时
当查询请求 InnoDB 中的记录级锁时，它会超时，类似于刷新、元数据和备份锁的超时。由于记录级锁争用比表级锁争用更常见，并且记录级锁增加了死锁的可能性，因此超时默认为 50 秒。它可以使用innodb_lock_wait_timeout选项进行设置，该选项可以针对全局和会话范围进行设置。
当超时发生时，查询失败，并出现ER_LOCK_WAIT_TIMEOUT错误(错误号 1205 ),就像表级锁超时一样。清单 8-2 展示了一个发生 InnoDB 锁等待超时的例子。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 656 1087 6
-- 2 657 1088 6
-- Connection 1
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0002 sec)
Connection 1> UPDATE world.city
SET Population = Population + 1
WHERE ID = 130;
Query OK, 1 row affected (0.0006 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- Connection 2
Connection 2> SET SESSION innodb_lock_wait_timeout = 3;
Query OK, 0 rows affected (0.2621 sec)
Connection 2> UPDATE world.city
SET Population = Population + 1
WHERE ID = 130;
ERROR: 1205: Lock wait timeout exceeded; try restarting transaction
-- Connection 1
Connection 1> ROLLBACK;
Query OK, 0 rows affected (0.0751 sec)
Listing 8-2Example of an InnoDB lock wait timeout
在本例中，连接 2 的锁等待超时设置为 3 秒，因此没有必要等待通常的 50 秒超时。
当超时发生时，innodb_rollback_on_timeout选项定义了事务完成的工作有多少被回滚。当innodb_rollback_on_timeout被禁用时(默认)，只有触发超时的语句被回滚。启用该选项后，整个事务将回滚。innodb_rollback_on_timeout选项只能在全局级别配置，并且需要重启才能更改值。
Caution
处理锁等待超时是非常重要的，否则它可能会使事务带有未释放的锁。如果发生这种情况，其他事务可能无法获得它们需要的锁。因此，您需要确保重试事务的剩余部分，显式回滚事务，或者启用innodb_rollback_on_timeout在锁等待超时时自动回滚事务。
一般情况下，建议将 InnoDB 记录级锁的超时值保持在较低水平。通常，最好降低默认值 50 秒。允许查询等待锁的时间越长，其他锁请求受影响的可能性就越大，这也可能导致其他查询停止。这也使得死锁更有可能发生。如果您禁用死锁检测(接下来将讨论)，您应该为innodb_lock_wait_timeout使用一个非常小的值，比如 1 秒或 2 秒，因为您将使用超时来检测死锁。如果没有死锁检测，也建议启用innodb_rollback_on_timeout选项。
僵局
死锁听起来是一个非常可怕的概念，但是你不应该让这个名字吓住你。就像锁等待超时一样，死锁是高并发数据库世界中的现实。它真正的意思是锁请求之间有一个循环关系，如图 8-1 中的交通阻塞所示。解决僵局的唯一方法是强制放弃其中一个请求。从这个意义上说，死锁与锁等待超时没有什么不同。事实上，您可以禁用死锁检测，在这种情况下，其中一个锁将以锁等待超时结束。
图 8-1
交通堵塞
那么，如果不是真正需要的话，为什么会有死锁呢？因为当锁请求之间存在循环关系时会出现死锁，所以 InnoDB 可以在循环完成后立即检测到死锁。这允许 InnoDB 立即告诉用户发生了死锁，而不必等待锁等待超时。告知发生了死锁也是有用的，因为这通常提供了改进应用中数据访问的机会。因此，您应该将死锁视为朋友，而不是敌人。图 8-2 显示了两个事务查询一个导致死锁的表的例子。
图 8-2
导致死锁的两个事务的示例
在本例中，事务 1 首先用ID = 130更新行，然后用ID = 3805更新行。在此期间，事务 2 首先用ID = 3805更新行，然后用ID = 130更新行。这意味着当事务 1 试图更新ID = 3805时，事务 2 已经锁定了该行。事务 2 也无法继续，因为它无法锁定ID = 130，因为事务 1 已经持有该锁。这是一个简单死锁的典型例子。环锁关系也如图 8-3 所示。
图 8-3
导致死锁的锁的循环关系
在该图中，事务 1 和事务 2 持有哪个锁，请求哪个锁，以及如果没有干预，冲突如何永远无法解决，这一点很清楚。这使得它有资格成为一个僵局。
在现实世界中，死锁往往更加复杂。在这里讨论的例子中，只涉及到主键记录锁。一般来说，通常还包括二级钥匙、间隙锁和其他可能的锁类型。也可能涉及两个以上的事务。然而，原则是一样的。
Note
对于两个事务中的每一个，即使只有一个查询，也会发生死锁。如果一个查询按升序读取记录，而另一个按降序读取记录，则可能会出现死锁。
当死锁发生时，InnoDB 选择“工作最少”的事务成为受害者。这类似于在一些高可用性解决方案(如 MySQL NDB 集群)中使用的“射中另一个节点的头部”(STONITH)方法，只是这里是一个事务被“射中头部”您可以检查information_schema.INNODB_TRX视图中的trx_weight列，查看 InnoDB 使用的权重(完成的工作越多，权重越高)。实际上，这意味着持有最少锁的事务将被回滚。当这种情况发生时，事务中被选作牺牲品的查询失败，并返回错误ER_LOCK_DEADLOCK(错误代码 1213)，事务被回滚以释放尽可能多的锁。清单 8-3 中显示了一个发生死锁的例子。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 659 1093 6
-- 2 660 1094 6
-- Connection 1
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0002 sec)
Connection 1> UPDATE world.city
SET Population = Population + 1
WHERE ID = 130;
Query OK, 1 row affected (0.0098 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- Connection 2
Connection 2> START TRANSACTION;
Query OK, 0 rows affected (0.0003 sec)
Connection 2> UPDATE world.city
SET Population = Population + 1
WHERE ID = 3805;
Query OK, 1 row affected (0.0009 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Connection 2> UPDATE world.city
SET Population = Population + 1
WHERE ID = 130;
-- Connection 1
Connection 1> UPDATE world.city
SET Population = Population + 1
WHERE ID = 3805;
ERROR: 1213: Deadlock found when trying to get lock; try restarting transaction
-- Connection 2
Query OK, 1 row affected (0.1019 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- Connection 1
Connection 1> ROLLBACK;
Query OK, 0 rows affected (0.0002 sec)
-- Connection 2
Connection 2> ROLLBACK;
Query OK, 0 rows affected (0.0293 sec)
Listing 8-3Example of a deadlock
死锁甚至可以比本例中的更简单(尽管这种情况很少见，除非您显式地使用锁定SELECT语句或者使用SERIALIZABLE事务隔离级别)。清单 8-4 显示了仅使用一行就发生的死锁。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 663 1097 6
-- 2 664 1098 6
-- Connection 1
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0004 sec)
Connection 1> SELECT * FROM world.city WHERE ID = 130 FOR SHARE;
+-----+--------+-------------+-----------------+------------+
| ID | Name | CountryCode | District | Population |
+-----+--------+-------------+-----------------+------------+
| 130 | Sydney | AUS | New South Wales | 3276207 |
+-----+--------+-------------+-----------------+------------+
1 row in set (0.0005 sec)
-- Connection 2
Connection 2> START TRANSACTION;
Query OK, 0 rows affected (0.0002 sec)
Connection 2> UPDATE world.city
SET Population = Population + 1
WHERE ID = 130;
-- Connection 1
Connection 1> UPDATE world.city
SET Population = Population + 1
WHERE ID = 130;
Query OK, 1 row affected (0.0447 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- Connection 2
ERROR: 1213: Deadlock found when trying to get lock; try restarting transaction
-- Connection 1
Connection 1> ROLLBACK;
Query OK, 0 rows affected (0.0280 sec)
-- Connection 2
Connection 2> ROLLBACK;
Query OK, 0 rows affected (0.0003 sec)
Listing 8-4A single row deadlock
在这种情况下，连接 2 成为死锁的受害者，而没有被授予记录锁。发生这种死锁是因为 InnoDB 当前不允许将连接 1 中的共享锁升级为独占锁的请求跳到连接 2 的锁请求之前。这是有可能实现的，但是因为这些场景相对较少，所以还没有实现。也就是说，如果您有外键，DML 语句可能会在外键关系中的另一个表上获取一个共享锁(另请参见第 10 章)，因此，如果同一事务中的后续语句试图升级该共享锁，那么您会看到与本例中相同类型的死锁。
在大多数情况下，自动死锁检测对于避免查询延迟过长时间是非常有用的。不过死锁检测不是免费的。对于具有非常高的查询并发性的 MySQL 实例，查找死锁的成本会变得很高，您最好禁用死锁检测，这是通过将innodb_deadlock_detect选项设置为OFF来完成的。也就是说，在 MySQL 8.0.18 和更高版本中，死锁检测被移到了一个专用的后台线程中，从而提高了性能。
如果禁用死锁检测，建议将innodb_lock_wait_timeout设置为一个非常低的值，比如 1 秒，以快速检测锁争用。此外，启用innodb_rollback_on_timeout选项以确保锁被释放。
最后一种锁冲突处理发生在 InnoDB 互斥体和信号量中。
InnoDB 互斥和信号量等待
当 InnoDB 请求一个互斥或 rw 锁信号量并且不能立即获得时，它将不得不等待。因为等待发生在比数据锁更低的级别，InnoDB 将在等待时采用两种方法中的一种。它可以进入一个循环并轮询锁是否可用，或者它可以挂起线程并使其可用于其他任务。
轮询允许更快地获得锁，但它会使 CPU 线程忙碌，并且轮询会导致其他线程的 CPU 缓存失效。有三个配置选项可用于控制行为:
-
innodb_spin_wait_delay: 轮询时，InnoDB 会计算一个介于零和innodb_spin_wait_delay之间的随机数。这与innodb_spin_wait_pause_multiplier相乘，以确定轮询循环中出现的PAUSE指令的数量。PAUSE事件的随机数用于降低缓存失效的影响。较小的值(甚至 0)可能会对具有单个共享 fast CPU 缓存的系统有所帮助。较大的值可以降低缓存失效的影响，尤其是在多 CPU 系统上。 -
innodb_spin_wait_pause_multiplier: 与旋转等待延迟一起使用的乘数。这个选项是 MySQL 8.0.16 中新增的，是为了适应 Skylake 一代处理器中引入的PAUSE指令的持续时间的变化而引入的。更改该值的主要用途是在与 Skylake 之前的 x86/x86-64 相比，PAUSE指令持续时间不同的架构上使用 MySQL。在早期版本中，乘数被硬编码为 50，这也是innodb_spin_wait_pause_multiplier的默认值。 -
innodb_sync_spin_loops: 暂停线程前要执行的旋转循环次数。该值越低，CPU 线程可用于其他任务的速度就越快，代价是更多的上下文切换。当超过自旋循环时，操作系统等待 rw 锁计数器递增。该值越高，获得锁的速度就越快，但代价是 CPU 使用率更高。默认值为 30。
您很少需要调整这些设置；然而，在极少数情况下，它们可以在某种程度上提高性能。也就是说，如果您使用的不是最新的 MySQL 版本，升级比调整这些选项更能减少互斥/信号量争用。在所有情况下，如果您决定更改这些设置，请确保在与您的生产系统相同的体系结构和硬件配置上彻底测试性能，并且工作负载能够很好地代表您的生产工作负载。
如果不能立即获得 InnoDB 互斥或 rw 锁信号量等待，InnoDB 也会在内部注册。通过SHOW ENGINE INNODB MUTEX显示的相关计数器将增加(虽然只显示至少有一个操作系统等待的互斥体和读写锁)，如果您在等待过程中生成 InnoDB monitor 报告，它将包含在报告的SEMAPHORES等待部分。如果等待持续超过 240 秒而没有检测到进度，InnoDB 将自动启用 InnoDB 监控器并将输出写入错误日志，以便您可以调查问题。如果在接下来的 600 秒内没有检测到任何进展，InnoDB 将关闭 MySQL 作为预防措施，因为它假设出现了无法解决的情况。在这种情况下，您将看到一个解释关闭原因的错误，例如(是的，打印的持续时间有些误导，因为它是自 240 秒触发“长信号量等待”条件以来的时间)
2020-07-05T09:30:24.151782Z 0 [ERROR] [MY-012872] [InnoDB] Semaphore wait has lasted > 600 seconds. We intentionally crash the server because it appears to be hung.
通过使服务器崩溃，InnoDB 确保了如果遇到 InnoDB 内部的错误，情况会得到解决，但代价是 MySQL 将不得不重新启动并进行崩溃恢复。由于这个原因，它被认为是最后的手段，目前，超时是不可配置的。
Note
执行CHECK TABLE时，超时阈值增加到 7200 秒(2 小时)。
像这样的关机通常有两个原因:
-
有一个硬件或操作系统问题阻碍了 InnoDB 的进展。
-
InnoDB 中有一个错误，例如，检测不到缓慢操作的进度，或者在获取互斥体或信号量时出现死锁。
如果您遇到类似这样的关闭，请从错误日志中的 InnoDB monitor 输出中验证发生等待的位置。在某些情况下，您可以使用线程 id 来确定哪个查询导致了等待。您还应该检查系统日志，以验证硬件的健康状况，以及是否有任何操作系统级别的问题迹象。
摘要
本章概述了当不能立即获得锁时会发生什么。首先，描述了竞争感知事务调度(CATS)算法。这在 MySQL 8 中使用，允许已经持有许多锁的事务更快地获得锁请求，因此它们的锁也可以更快地再次释放。
其次，讨论了 InnoDB 数据锁的兼容性是一个非常复杂的问题，这意味着在试图重现问题时必须考虑锁的顺序。
本章的其余部分介绍了元数据、备份、InnoDB 锁等待超时、死锁、InnoDB 互斥和 rw 锁信号量等待。锁等待和死锁在高并发系统中是自然发生的，它们本身不应该成为警报的原因。主要问题是它们何时变得频繁。默认的锁等待超时也可能太长，因此减少它们并处理超时可能是一个选项。
现在您已经了解了锁是如何工作的，以及锁请求是如何失败的，您需要考虑如何减少锁定的影响，这是下一章的主题。
九、减少锁定问题
请记住，MySQL 和 InnoDB 中的锁定是提供并发访问的一种方式，通常 InnoDB 的细粒度锁定允许高度并发的工作负载。然而，如果您有过多的锁定，它将导致并发性降低和查询堆积，在最糟糕的情况下，它可能会导致应用停止工作，并导致糟糕的用户体验。
因此，当您编写应用并为其数据和访问设计模式时，记住锁是很重要的。减少锁定的策略包括添加索引、更改事务隔离级别、更改配置和抢先锁定。本章涵盖了所有这些策略。
Tip
不要被优化锁冲昏了头脑。如果只是偶尔遇到锁等待超时和死锁，通常最好重试查询或事务，而不是花时间来避免这个问题。多频繁取决于您的工作负载，但是对于许多应用来说，每小时重试几次不是问题。
事务规模和年龄
减少锁问题的一个重要策略是保持您的事务较小，并避免使事务打开的时间超过必要时间的延迟。锁问题最常见的原因是事务修改了大量的行，或者事务的活动时间超过了必要的时间。
事务的大小是事务所做的工作量，尤其是它占用的锁的数量，但是事务执行所花费的时间也很重要。正如本讨论中的一些其他主题将会提到的，您可以通过索引和事务隔离级别来部分地降低影响。然而，记住总体结果也很重要。如果您需要修改许多行，问问自己是否可以将工作分成更小的批，或者是否要求所有事情都在同一个事务中完成。也可以将一些准备工作分离出来，在主事务之外完成。
事务的持续时间也很重要。一个常见的问题是使用autocommit = 0的连接。每次在没有活动事务的情况下执行查询(包括SELECT)时，都会启动一个新的事务，直到执行显式的COMMIT或ROLLBACK、执行 DDL 语句或关闭连接时，事务才会完成。一些连接器默认禁用自动提交，所以您可能在没有意识到的情况下使用了这种模式，这可能会错误地让事务打开几个小时。
Tip
启用autocommit选项，除非您有特定的理由禁用它。当您启用自动提交时，InnoDB 还可以为许多SELECT查询检测出它是一个只读事务，并减少查询的开销。
另一个缺陷是在事务活动时启动事务并在应用中执行缓慢的操作。这可以是发送回用户的数据、交互式提示或文件 I/O。确保在 MySQL 中没有打开活动事务时执行这些缓慢的操作。
索引
索引减少了访问给定行所需的工作量。这样，索引是减少锁定的一个很好的工具，因为只有在执行查询时访问的记录才会被锁定。
考虑一个简单的例子，在world.city表中查询名为 Sydney 的城市:
START TRANSACTION;
SELECT *
FROM world.city
WHERE Name = 'Sydney'
FOR SHARE;
FOR SHARE选项用于强制查询对读取的记录使用共享锁。默认情况下,Name列上没有索引，因此查询将执行全表扫描来查找结果中需要的行。没有索引，有 4103 个记录锁(其中 24 个锁在主键的上确界伪记录上)，如清单 9-1 所示。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 697 1143 6
-- 2 698 1144 6
-- Connection 1
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0002 sec)
Connection 1> SELECT ID, Name, CountryCode, District
FROM world.city
WHERE Name = 'Sydney'
FOR SHARE;
+-----+--------+-------------+-----------------+
| ID | Name | CountryCode | District |
+-----+--------+-------------+-----------------+
| 130 | Sydney | AUS | New South Wales |
+-----+--------+-------------+-----------------+
1 row in set (0.0034 sec)
-- Connection 2
Connection 2> SELECT index_name, lock_type,
lock_mode, COUNT(*)
FROM performance_schema.data_locks
WHERE object_schema = 'world'
AND object_name = 'city'
AND thread_id = 1143
GROUP BY index_name, lock_type, lock_mode;
+------------+-----------+-----------+----------+
| index_name | lock_type | lock_mode | COUNT(*) |
+------------+-----------+-----------+----------+
| NULL | TABLE | IS | 1 |
| PRIMARY | RECORD | S | 4103 |
+------------+-----------+-----------+----------+
2 rows in set (0.0323 sec)
-- Connection 1
Connection 1> ROLLBACK;
Query OK, 0 rows affected (0.0005 sec)
Listing 9-1Record locks without an index on the Name column
如果在Name列上添加一个索引，锁计数将减少到总共三个记录锁，如清单 9-2 所示。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 699 1145 6
-- 2 700 1146 6
-- Connection 1
Connection 1> ALTER TABLE world.city
ADD INDEX (Name);
Query OK, 0 rows affected (1.5063 sec)
Records: 0 Duplicates: 0 Warnings: 0
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0003 sec)
Connection 1> SELECT ID, Name, CountryCode, District
FROM world.city
WHERE Name = 'Sydney'
FOR SHARE;
+-----+--------+-------------+-----------------+
| ID | Name | CountryCode | District |
+-----+--------+-------------+-----------------+
| 130 | Sydney | AUS | New South Wales |
+-----+--------+-------------+-----------------+
1 row in set (0.0004 sec)
-- Connection 2
Connection 2> SELECT index_name, lock_type,
lock_mode, COUNT(*)
FROM performance_schema.data_locks
WHERE object_schema = 'world'
AND object_name = 'city'
AND thread_id = 1145
GROUP BY index_name, lock_type, lock_mode;
+------------+-----------+---------------+----------+
| index_name | lock_type | lock_mode | COUNT(*) |
+------------+-----------+---------------+----------+
| NULL | TABLE | IS | 1 |
| Name | RECORD | S | 1 |
| PRIMARY | RECORD | S,REC_NOT_GAP | 1 |
| Name | RECORD | S,GAP | 1 |
+------------+-----------+---------------+----------+
4 rows in set (0.0011 sec)
-- Connection 1
Connection 1> ROLLBACK;
Query OK, 0 rows affected (0.0004 sec)
Connection 1> ALTER TABLE world.city
DROP INDEX Name;
Query OK, 0 rows affected (0.3288 sec)
Records: 0 Duplicates: 0 Warnings: 0
Listing 9-2Record locks with an index on the Name column
另一方面，更多的索引提供了更多访问相同行的方法，这可能会增加死锁的数量。
记录访问顺序
确保您尽可能多地以相同的顺序访问不同事务的记录。在第 8 章讨论的死锁例子中，导致死锁的原因是两个事务以相反的顺序访问行。如果它们以相同的顺序访问这些行，就不会出现死锁。当您访问不同表中的记录时，这也适用。
确保相同的访问顺序绝非易事。当您执行连接并且优化器为两个查询决定不同的连接顺序时，甚至可能发生不同的访问顺序。如果不同的连接顺序导致过多的锁问题，您可以考虑使用优化器提示来告诉优化器更改连接顺序 1 ，但是在这种情况下，您当然也应该考虑查询性能。
事务隔离级别
InnoDB 支持几种事务隔离级别。不同的隔离级别有不同的锁需求:特别是REPEATABLE READ和SERIALIZABLE比READ COMMITTED需要更多的锁。
READ COMMITTED事务隔离级别可以从两个方面帮助解决锁定问题。使用的间隙锁要少得多，并且在 DML 语句期间被访问但未被修改的行在语句完成后会再次释放它们的锁。对于REPEATABLE READ和SERIALIZABLE，锁仅在事务结束时释放。
Note
人们常说READ COMMITTED事务隔离级别不采用间隙锁。这是一个神话，是不正确的。虽然使用的间隙锁要少得多，但仍然需要一些。例如，这包括 InnoDB 检查外键和唯一键约束的情况，以及发生页面分割的情况。
考虑一个例子，其中使用CountryCode列将查询限制在一个国家，名为 Sydney 的城市的人口发生了变化。这可以通过以下查询来完成:
START TRANSACTION;
UPDATE world.city
SET Population = 5000000
WHERE Name = 'Sydney'
AND CountryCode = 'AUS';
在Name列上没有索引，但是在CountryCode上有一个。因此，更新需要扫描部分CountryCode索引。清单 9-3 展示了一个在REPEATABLE READ事务隔离级别执行查询的例子。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 701 1149 6
-- 2 702 1150 6
-- Connection 1
Connection 1> SET SESSION transaction_isolation = 'REPEATABLE-READ';
Query OK, 0 rows affected (0.2697 sec)
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0007 sec)
Connection 1> UPDATE world.city
SET Population = 5000000
WHERE Name = 'Sydney'
AND CountryCode = 'AUS';
Query OK, 1 row affected (0.0024 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- Connection 2
Connection 2> SELECT index_name, lock_type,
lock_mode, COUNT(*)
FROM performance_schema.data_locks
WHERE object_schema = 'world'
AND object_name = 'city'
AND thread_id = 1149
GROUP BY index_name, lock_type, lock_mode;
+-------------+-----------+---------------+----------+
| index_name | lock_type | lock_mode | COUNT(*) |
+-------------+-----------+---------------+----------+
| NULL | TABLE | IX | 1 |
| CountryCode | RECORD | X | 14 |
| PRIMARY | RECORD | X,REC_NOT_GAP | 14 |
| CountryCode | RECORD | X,GAP | 1 |
+-------------+-----------+---------------+----------+
4 rows in set (0.0102 sec)
-- Connection 1
Connection 1> ROLLBACK;
Query OK, 0 rows affected (0.0730 sec)
Connection 1> SET SESSION transaction_isolation = @@global.transaction_isolation;
Query OK, 0 rows affected (0.0004 sec)
Listing 9-3The locks held in the REPEATABLE READ transaction isolation level
在每个CountryCode索引和主键上有 14 个记录锁，在CountryCode索引上有一个间隙锁。将这与在清单 9-4 中所示的READ COMMITTED事务隔离级别中执行查询后持有的锁进行比较。
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 703 1153 6
-- 2 704 1154 6
-- Connection 1
Connection 1> SET SESSION transaction_isolation = 'READ-COMMITTED';
Query OK, 0 rows affected (0.0003 sec)
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0002 sec)
Connection 1> UPDATE world.city
SET Population = 5000000
WHERE Name = 'Sydney'
AND CountryCode = 'AUS';
Query OK, 1 row affected (0.0014 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- Connection 2
Connection 2> SELECT index_name, lock_type,
lock_mode, COUNT(*)
FROM performance_schema.data_locks
WHERE object_schema = 'world'
AND object_name = 'city'
AND thread_id = 1153
GROUP BY index_name, lock_type, lock_mode;
+-------------+-----------+---------------+----------+
| index_name | lock_type | lock_mode | COUNT(*) |
+-------------+-----------+---------------+----------+
| NULL | TABLE | IX | 1 |
| CountryCode | RECORD | X,REC_NOT_GAP | 1 |
| PRIMARY | RECORD | X,REC_NOT_GAP | 1 |
+-------------+-----------+---------------+----------+
3 rows in set (0.0035 sec)
-- Connection 1
Connection 1> ROLLBACK;
Query OK, 0 rows affected (0.0780 sec)
Connection 1> SET SESSION transaction_isolation = @@global.transaction_isolation;
Query OK, 0 rows affected (0.0003 sec)
Listing 9-4The locks held in the READ-COMMITTED transaction isolation level
在这里，记录锁减少为每个索引和主键上的一个锁。没有间隙锁。
并非所有工作负载都可以使用READ COMMITTED事务隔离级别。如果您必须让SELECT语句在同一事务中多次执行时返回相同的结果，或者让不同的查询对应于同一时间点，那么您必须使用REPEATABLE READ或SERIALIZABLE。但是，在许多情况下，降低隔离级别是一个选项，您可以为不同的事务选择不同的隔离级别。如果您正在从 Oracle DB 迁移应用，那么您已经在使用READ COMMITTED，并且您也可以在 MySQL 中使用它。
配置
直接影响锁定的配置选项并不多，但是熟悉那些确实存在的选项是有好处的，特别是因为有些选项会影响互斥和信号量争用的级别。本节介绍了将资源分成多个分区、禁用 InnoDB 自适应散列索引以及限制写锁的数量。
资源划分
互斥和信号量争用是由许多线程同时使用同一资源引起的。减少争用的一个简单而强大的方法是将一个资源分成多个部分，这正是对 InnoDB 缓冲池、InnoDB 自适应散列索引和表开放缓存所做的。表 9-1 显示了控制一个资源分成多少个实例的配置选项。
表 9-1
控制资源实例数量的配置选项
|[计]选项
|
缺省值
|
描述
|
| --- | --- | --- |
| innodb_adaptive_hash_index_parts | eight | 自适应哈希索引的分区数。分区在索引之上。 |
| innodb_buffer_pool_instances | 1 或 8 | 缓冲池分成多少部分。如果缓冲池的总大小小于 1gb，缺省值为 1，否则为 8，除非是在 32 位 Windows 系统上。 |
| table_open_cache_instances | Sixteen | 表打开缓存的部分数。 |
Note
对于所有三个选项，它们都需要重启 MySQL 来更改值。
对于 InnoDB 缓冲池，默认的实例数量取决于平台和缓冲池的大小。如果总大小小于 1gb，默认值为 1，否则为 8。对于 32 位 Windows，缺省值为 1.3 GiB 以下的 1；否则，每个实例为 128 MiB。最大实例数为 64。
Note
你可能也听说过metadata_locks_hash_instances选项。这在 MySQL 5.7 中被否决，在 MySQL 8.0.13 中被删除。这是因为元数据锁的实现发生了变化，使得该选项变得不必要。
如果一个资源的多个分区有助于减少争用，那么增加分区的数量似乎是显而易见的。但是，它比这更复杂，因为更多的分区也会引入开销，所以这是一个在减少资源闩锁争用上平衡这种开销的问题。在极端情况下，数据库只执行一个并发查询，通常每个资源只有一个分区会更好。“通常”是因为对于大型表开放缓存，当需要从缓存中逐出表时，多个分区有助于使最近最少使用(LRU)算法更有效。
一般来说，分区的数量不应该大于 CPU 核心的数量。也就是说，默认值是一个很好的起点，最后，您需要结合系统和工作负载进行测试，以验证最佳设置。对于自适应散列索引，您甚至需要完全禁用它。
禁用 InnoDB 自适应散列索引
自适应散列索引特性在 InnoDB 中自动工作。如果 InnoDB 检测到您正在频繁使用二级索引，并且启用了自适应散列索引，它将动态构建最常用值的散列索引。哈希索引以独占方式存储在缓冲池中，因此当您重新启动 MySQL 时不会持久化。如果 InnoDB 检测到内存可以更好地用于将更多页面加载到缓冲池中，它将丢弃部分散列索引。这就是所谓的自适应索引的含义:InnoDB 将努力使它适应您的查询。
理论上，自适应哈希索引是一个双赢的局面。您获得了拥有散列索引的优势，而无需考虑需要为哪些列添加它，并且内存使用都是自动处理的。但是，启用它会产生开销，而且并非所有工作负载都能从中受益。事实上，对于某些工作负载，开销可能会变得非常大，以至于出现严重的性能问题，在这种情况下，更改哈希分区的数量没有任何帮助。
工作数据集不适合缓冲池的部分越大，对辅助索引的更改就越多，用于过滤的辅助索引就越少，禁用自适应散列索引就越有可能使您受益。自适应散列索引是一个问题的情况通常通过互斥体上的大量等待和实现自适应散列索引搜索的btr0sea.cc文件中的 rw 锁信号量表现出来。
如果您遇到自适应散列索引成为瓶颈，您可以使用innodb_adaptive_hash_index选项启用或禁用该特性。请注意，虽然您可以动态地启用和禁用该特性，但是禁用自适应散列索引会从缓冲池中驱逐所有散列索引，并且在重新启用索引时需要一段预热时间。因此，在复制设置中，首先在一个副本中禁用自适应哈希索引是值得的，并在系统范围内禁用它之前，监控您的应用是否从更改中受益。如果您需要在读写复制副本上重新启用自适应哈希索引，请考虑故障切换到另一个仍启用该功能的复制副本，以便在重新启用的复制副本经历预热期间，应用受影响较小。
Tip
如果要禁用自适应哈希索引，请首先在单个复制副本上禁用，这样，当您需要重新启用该功能时，可以避免所有复制副本都经历预热期。
将讨论的最后一个配置选项允许您降低元数据写锁的优先级。
降低元数据写锁的优先级
默认情况下，如果一个表有两个元数据锁定请求，一个是读请求，另一个是写请求，那么写请求具有优先权。这通常没问题，因为写入比读取更具侵入性，所以在大多数情况下，最好给它们优先级，这样它们可以尽快完成。
然而，在外键的情况下，这种方法可能会遇到问题。当对具有外键的表执行 DDL 时，该语句请求父表上的共享元数据锁。如果您有针对父表持有写锁的持续事务，那么子表上的 DDL 语句将永远无法继续，即使子表从未被使用过。因此，您需要某种方法让 MySQL 停止运行，并允许读取元数据锁定请求继续进行。
您可以使用max_write_lock_count选项来实现这一点，该选项的取值介于 1 和系统支持的最大整数之间。默认值是支持的最大值。每次max_write_lock_count锁被授予后，MySQL 会优先考虑一些读锁。这有助于确保读锁请求不会被饿死。
更改max_write_lock_count的值时需要小心，因为太低的值会导致带写锁的事务——记住它们是排他锁——需要太长时间才能完成。当写事务未完成时，它们的锁会阻止其他事务继续进行。因为您可以动态地更改max_write_lock_count，所以请密切关注系统，并准备好恢复更改，如果它导致的副作用比治疗更糟糕的话。
抢先锁定
将讨论的最后一个策略是抢先锁定。如果您有一个执行多个查询的复杂事务，在某些情况下，执行一个SELECT ... FOR UPDATE或SELECT ... FOR SHARE查询来锁定您知道在事务中稍后会用到的记录可能是一种优势。另一个有用的情况是，确保对于不同的任务以相同的顺序访问行。
抢先锁定对于减少死锁的频率特别有效。一个缺点是，你最终会持有更长时间的锁。总的来说，抢占式锁定是一种应该谨慎使用的策略，但是在正确的情况下，它可以有效地防止死锁。
摘要
本章研究了减少锁的影响的策略，这些策略包括减少锁的数量和保持多长时间，以改变配置来减少锁的影响。
最重要的是，不要持有不必要的锁，也不要持有超过需要的时间。减少事务的大小和完成事务所需的时间是减少锁争用的两种最有效的方法。此外，通过适当地选择索引，可以减少给定语句所需的锁的数量。类似地，事务隔离级别会影响锁的数量及其持续时间，而READ COMMITTED事务隔离级别是减少锁影响的常见选择。
对于死锁，在整个应用中尽可能以相同的顺序访问记录是很重要的。确保这一点的一个选择是先发制人的锁定，尽管这应该谨慎使用，因为它增加了持有锁的持续时间。
最后，更改配置以减少锁的影响。如果在缓冲池、自适应散列索引或表开放缓存上有互斥争用，可以对资源进行分区，或者对于自适应散列索引，可以完全禁用该特性。对于由于外键而请求元数据读锁的 DDL 语句，在读锁被赋予优先级之前，限制将被授予的写锁的数量也是有用的。
本章介绍了索引和外键对锁定的影响。下一章将更详细地讨论这些话题。
Footnotes [1](#Fn1_source)https://dev.mysql.com/doc/refman/en/optimizer-hints.html#optimizer-hints-join-order
