MySQL 锁等待：利用 sys 模式 Lock View 快速定位问题
大家好，今天我们来聊聊 MySQL 中的锁等待问题，以及如何利用 sys 模式下的锁视图（Lock View）快速定位并解决这些问题。锁是数据库并发控制的重要机制，但过度或不当的使用会导致锁等待，进而影响系统性能。sys 模式提供了一系列视图，可以帮助我们更好地理解和诊断锁相关的问题。
1. 锁的基本概念
在深入 sys 模式的锁视图之前，我们先回顾一下锁的基本概念。MySQL 中常见的锁类型包括：
- 共享锁 (Shared Lock, S Lock)： 允许事务读取数据。多个事务可以同时持有同一资源的共享锁。
- 排他锁 (Exclusive Lock, X Lock)： 允许事务修改数据。同一时刻只能有一个事务持有资源的排他锁。
- 意向共享锁 (Intention Shared Lock, IS Lock)： 表示事务打算在某些行上持有共享锁。
- 意向排他锁 (Intention Exclusive Lock, IX Lock)： 表示事务打算在某些行上持有排他锁。
- 自增锁 (Auto-Increment Lock)： 用于保证
AUTO_INCREMENT列的唯一性。 - 元数据锁 (Metadata Lock, MDL Lock)： 用于保护数据库对象的元数据，例如表结构。
锁的粒度可以分为：
- 表锁： 锁定整个表。
- 行锁： 锁定表中的特定行。
- 页面锁： 锁定数据页，MySQL 的 InnoDB 存储引擎在某些场景下会使用页面锁。
2. 锁等待的产生
锁等待发生在当一个事务尝试获取一个已经被其他事务持有的锁时。导致锁等待的原因有很多，例如：
- 长事务： 长时间运行的事务会持有锁的时间更长，增加其他事务等待的概率。
- 不合理的事务隔离级别： 较高的隔离级别（如
SERIALIZABLE）会增加锁的竞争。 - 未优化的 SQL 查询： 低效的查询会导致持有锁的时间更长。
- 死锁： 两个或多个事务相互等待对方释放锁。
3. sys 模式简介
sys 模式是在 MySQL 5.7 版本引入的，它提供了一系列视图和存储过程，用于诊断和监控 MySQL 实例的性能。sys 模式中的视图基于 Performance Schema 和 Information Schema，提供了更易于理解和使用的信息。
要启用 sys 模式，需要确保 Performance Schema 已经启用。通常情况下，Performance Schema 在 MySQL 5.7 及更高版本中默认启用。如果没有启用，可以通过修改 MySQL 配置文件 (my.cnf 或 my.ini) 来启用：
[mysqld]
performance_schema=ON重启 MySQL 服务后，sys 模式应该就可以使用了。如果没有，执行 mysql_upgrade 命令。
4. 利用 sys 模式 Lock View 定位锁等待
sys 模式提供了多个与锁相关的视图，其中最常用的包括：
sys.innodb_lock_waits：显示当前正在等待锁的事务的信息。sys.schema_table_lock_waits：显示表级别的锁等待信息。sys.processlist：虽然不完全是锁视图，但可以显示当前正在执行的线程的信息，包括状态、执行的 SQL 等，可以辅助定位锁等待问题。
4.1 sys.innodb_lock_waits 视图
sys.innodb_lock_waits 视图提供了关于 InnoDB 锁等待的详细信息。它的主要列包括：
| 列名 | 数据类型 | 描述 |
|---|---|---|
wait_started |
datetime | 等待开始的时间。 |
wait_age |
time | 等待的时间长度。 |
wait_age_secs |
bigint | 等待的秒数。 |
locked_table |
varchar | 被锁定的表名。 |
locked_index |
varchar | 被锁定的索引名（如果适用）。 |
locked_type |
varchar | 锁的类型（例如，RECORD，TABLE）。 |
waiting_trx_id |
varchar | 等待锁的事务 ID。 |
waiting_trx_started |
datetime | 等待锁的事务开始的时间。 |
waiting_trx_age |
time | 等待锁的事务的持续时间。 |
waiting_trx_rows_locked |
bigint | 等待锁的事务锁定的行数。 |
waiting_trx_rows_modified |
bigint | 等待锁的事务修改的行数。 |
waiting_lock_id |
varchar | 等待锁的锁 ID。 |
waiting_lock_mode |
varchar | 等待锁的模式（例如，X，S）。 |
blocking_trx_id |
varchar | 持有锁的事务 ID。 |
blocking_trx_started |
datetime | 持有锁的事务开始的时间。 |
blocking_trx_age |
time | 持有锁的事务的持续时间。 |
blocking_trx_rows_locked |
bigint | 持有锁的事务锁定的行数。 |
blocking_trx_rows_modified |
bigint | 持有锁的事务修改的行数。 |
blocking_lock_id |
varchar | 持有锁的锁 ID。 |
blocking_lock_mode |
varchar | 持有锁的模式（例如，X，S）。 |
sql_kill_blocking_query |
varchar | 用于终止持有锁的查询的 SQL 语句。 |
sql_kill_waiting_query |
varchar | 用于终止等待锁的查询的 SQL 语句。 |
以下是一个查询 sys.innodb_lock_waits 视图的例子：
SELECT * FROM sys.innodb_lock_waits;通过分析 sys.innodb_lock_waits 视图的输出，我们可以识别出哪些事务正在等待锁，以及哪些事务正在持有锁。wait_age 列可以帮助我们确定锁等待的时间长度，locked_table 列可以帮助我们确定被锁定的表。sql_kill_blocking_query 和 sql_kill_waiting_query 列提供了终止持有锁和等待锁的查询的 SQL 语句，方便我们快速解决问题。
示例:
假设执行上述查询后，得到如下结果：
| wait_started | wait_age | locked_table | waiting_trx_id | blocking_trx_id | sql_kill_blocking_query |
|---|---|---|---|---|---|
| 2023-10-27 10:00:00 | 00:00:10 | test.users |
12345 | 67890 | KILL QUERY 67890 |
从这个结果中，我们可以看出：
- 有一个事务 (ID
12345) 正在等待锁。 - 等待时间已经 10 秒。
- 被锁定的表是
test数据库的users表。 - 持有锁的事务是
67890。 - 可以使用
KILL QUERY 67890来终止持有锁的查询。
接下来，我们需要进一步调查事务 67890 正在执行的 SQL 语句，才能判断是否需要终止该查询。
4.2 sys.schema_table_lock_waits 视图
sys.schema_table_lock_waits 视图提供了表级别的锁等待信息。它的主要列包括：
| 列名 | 数据类型 | 描述 |
|---|---|---|
object_schema |
varchar | 数据库名。 |
object_name |
varchar | 表名。 |
waiting_pid |
bigint | 等待锁的线程 ID。 |
waiting_query |
longtext | 等待锁的 SQL 查询。 |
waiting_query_secs |
bigint | 等待锁的查询的持续时间（秒）。 |
waiting_lock_type |
varchar | 等待锁的类型（例如，EXCLUSIVE，SHARED）。 |
blocking_pid |
bigint | 持有锁的线程 ID。 |
blocking_query |
longtext | 持有锁的 SQL 查询。 |
blocking_query_secs |
bigint | 持有锁的查询的持续时间（秒）。 |
blocking_lock_type |
varchar | 持有锁的类型（例如，EXCLUSIVE，SHARED）。 |
sql_kill_blocking_query |
varchar | 用于终止持有锁的查询的 SQL 语句。 |
sql_kill_waiting_query |
varchar | 用于终止等待锁的查询的 SQL 语句。 |
以下是一个查询 sys.schema_table_lock_waits 视图的例子：
SELECT * FROM sys.schema_table_lock_waits;通过分析 sys.schema_table_lock_waits 视图的输出，我们可以识别出哪些表正在被锁定，以及哪些查询正在等待锁。waiting_query 和 blocking_query 列可以帮助我们确定等待锁和持有锁的 SQL 查询。同样，sql_kill_blocking_query 和 sql_kill_waiting_query 列提供了终止查询的 SQL 语句。
示例:
假设执行上述查询后，得到如下结果：
| object_schema | object_name | waiting_pid | waiting_query | waiting_query_secs | blocking_pid | blocking_query | blocking_query_secs |
|---|---|---|---|---|---|---|---|
test |
orders |
100 | SELECT * FROM orders WHERE customer_id = 1 |
5 | 200 | UPDATE orders SET status = 'shipped' WHERE order_id = 1 |
60 |
从这个结果中，我们可以看出：
test数据库的orders表正在被锁定。- 线程
100正在等待锁，它正在执行SELECT * FROM orders WHERE customer_id = 1。 - 线程
200正在持有锁，它正在执行UPDATE orders SET status = 'shipped' WHERE order_id = 1。 - 持有锁的查询已经执行了 60 秒，而等待锁的查询已经等待了 5 秒。
根据这些信息，我们可以判断是 UPDATE 语句导致了锁等待，并尝试优化该 UPDATE 语句或缩短其执行时间。
4.3 结合 sys.processlist 视图
sys.processlist 视图提供了当前正在执行的线程的信息。虽然它不是专门的锁视图，但它可以帮助我们确定事务正在执行的 SQL 语句，以及事务的状态。
以下是一个查询 sys.processlist 视图的例子：
SELECT * FROM sys.processlist WHERE ID IN (SELECT waiting_pid FROM sys.schema_table_lock_waits UNION ALL SELECT blocking_pid FROM sys.schema_table_lock_waits);这个查询可以显示所有参与锁等待的线程的信息，包括等待锁的线程和持有锁的线程。INFO 列显示了线程正在执行的 SQL 语句，COMMAND 列显示了线程的状态，例如 Sleep，Query，Locked 等。
结合 sys.processlist 和 sys.schema_table_lock_waits 视图，我们可以更全面地了解锁等待的原因，并采取相应的措施。
5. 解决锁等待问题的常用方法
定位到锁等待问题后，我们可以采取以下方法来解决问题：
- 优化 SQL 查询： 检查并优化导致锁等待的 SQL 查询，例如添加索引，避免全表扫描，减少锁定的行数。
- 缩短事务的持续时间： 将长事务分解为多个短事务，减少持有锁的时间。
- 调整事务隔离级别： 如果可以接受较低的隔离级别，可以考虑降低隔离级别，减少锁的竞争。
- 避免死锁： 设计事务时，确保事务以相同的顺序访问资源，避免循环等待。
- 使用
KILL QUERY命令： 如果确定某个查询导致了长时间的锁等待，并且无法立即优化，可以使用KILL QUERY命令终止该查询。但需要谨慎使用，因为终止查询可能会导致数据不一致。 - 升级 MySQL 版本： 新版本的 MySQL 通常会改进锁机制，提高并发性能。
- 分析慢查询日志： 启用慢查询日志，定期分析慢查询，找出需要优化的 SQL 查询。
6. 一个完整的案例分析
假设我们遇到了一个锁等待问题，并且通过查询 sys.innodb_lock_waits 视图发现以下信息：
| wait_started | wait_age | locked_table | waiting_trx_id | blocking_trx_id | sql_kill_blocking_query |
|---|---|---|---|---|---|
| 2023-10-27 10:00:00 | 00:00:10 | mydb.accounts |
12345 | 67890 | KILL QUERY 67890 |
我们知道事务 12345 正在等待 mydb 数据库的 accounts 表的锁，并且事务 67890 持有该锁。接下来，我们需要确定事务 67890 正在执行的 SQL 语句。
我们使用以下查询来获取事务 67890 的信息：
SELECT * FROM sys.processlist WHERE ID = 67890;假设查询结果如下：
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
|---|---|---|---|---|---|---|---|
| 67890 | root | localhost | mydb | Query | 60 | updating | UPDATE accounts SET balance = balance – 100 WHERE account_id = 1; |
从这个结果中，我们知道事务 67890 正在执行 UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;，并且已经执行了 60 秒。
接下来，我们需要分析这个 UPDATE 语句是否可以优化。例如，我们可以检查 accounts 表是否在 account_id 列上建立了索引。如果没有，我们可以添加索引来提高查询效率：
CREATE INDEX idx_account_id ON accounts (account_id);如果添加索引后，锁等待问题仍然存在，我们可以考虑其他方法，例如缩短事务的持续时间，或者使用 KILL QUERY 67890 命令终止该查询。
7. 定期监控和分析
为了避免锁等待问题影响系统性能，我们需要定期监控和分析数据库的锁情况。可以编写脚本定期查询 sys.innodb_lock_waits 和 sys.schema_table_lock_waits 视图，并将结果记录到日志文件中。然后，可以使用分析工具分析日志文件，找出潜在的锁等待问题，并及时采取措施。
锁等待定位方法和解决思路
通过 sys 模式的锁视图可以方便地定位到锁等待的事务和 SQL 语句。在解决锁等待问题时，优先考虑优化 SQL 查询，缩短事务持续时间，合理设置事务隔离级别等方法。 KILL QUERY 命令虽然可以快速解决问题，但需要谨慎使用。定期监控和分析数据库的锁情况，可以帮助我们及时发现和解决潜在的锁等待问题。