MySQL锁等待问题诊断：Sys Schema Lock View深度解析
大家好，今天我们来深入探讨MySQL中锁等待问题的诊断与排查，重点是如何利用sys schema提供的锁视图(Lock View)快速定位问题根源。锁是数据库并发控制的重要机制，但过度的锁竞争会导致锁等待，严重影响系统性能。sys schema提供了一系列强大的视图，能够帮助我们更清晰地理解锁的状态，从而更快地解决问题。
锁的基础概念回顾
在深入sys schema的锁视图之前，我们先简单回顾一下MySQL中锁的一些基本概念：
- 锁的粒度: MySQL 支持多种锁粒度，包括表锁、行锁和页锁(InnoDB)。
- 锁的类型: 常见的锁类型包括共享锁 (Shared Lock, S) 和排他锁 (Exclusive Lock, X)。共享锁允许多个事务同时读取数据，而排他锁则只允许一个事务独占地修改数据。
- 锁的模式: InnoDB引擎还支持意向锁（Intention Lock），包括意向共享锁（IS）和意向排他锁（IX）。意向锁表明了事务想要在更细粒度（例如行级别）上加锁的意图。
- 死锁: 当两个或多个事务互相等待对方释放锁时，就会发生死锁。
sys Schema简介
sys schema是MySQL 5.7版本之后引入的一个系统数据库，它提供了一组用于监控和诊断MySQL服务器性能的视图和存储过程。这些视图的数据来源于performance_schema和information_schema，但经过了更加友好的封装和聚合，使得我们能够更方便地分析数据。
sys schema默认是安装的，如果没有安装，可以通过以下命令安装：
INSTALL COMPONENT 'file://component_sys';sys Schema锁视图概览
sys schema提供了几个与锁相关的关键视图：
sys.processlist: 提供了当前MySQL实例中所有连接的信息，包括线程ID、用户、主机、数据库、命令、状态、持续时间等。sys.innodb_lock_waits: 这是我们今天重点关注的视图，它展示了当前正在等待锁的事务信息，包括等待哪个锁、哪个事务持有该锁。sys.innodb_locks: 提供了当前InnoDB引擎中所有锁的信息，包括锁的类型、锁定的对象、持有锁的事务等。sys.schema_table_lock_waits: 展示了表级别的锁等待信息。
利用sys.innodb_lock_waits定位锁等待问题
sys.innodb_lock_waits视图是诊断锁等待问题的核心工具。 它将information_schema.innodb_lock_waits中的信息进行了一些封装，使其更易于理解。
sys.innodb_lock_waits视图包含以下列：
| 列名 | 数据类型 | 描述 |
|---|---|---|
wait_started |
datetime |
等待开始的时间。 |
wait_age |
time |
等待持续的时间。 |
wait_age_secs |
bigint |
等待持续的秒数。 |
locked_table |
varchar |
被锁定的表名，格式为 schema.table。 |
locked_index |
varchar |
被锁定的索引名，如果没有索引被锁定，则为 NULL。 |
locked_partition |
varchar |
被锁定的分区名，如果没有分区被锁定，则为 NULL。 |
locked_space |
bigint |
被锁定的空间ID。 |
locked_page |
bigint |
被锁定的页ID。 |
locked_row |
bigint |
被锁定的行数。 |
locking_trx_id |
varchar |
持有锁的事务ID。 |
locking_trx_started |
datetime |
持有锁的事务开始的时间。 |
locking_trx_age |
time |
持有锁的事务持续的时间。 |
locking_trx_rows_locked |
bigint |
持有锁的事务锁定的行数。 |
locking_trx_rows_modified |
bigint |
持有锁的事务修改的行数。 |
locking_trx_query |
varchar |
持有锁的事务正在执行的SQL语句。 |
locking_trx_state |
varchar |
持有锁的事务的状态。 |
waiting_trx_id |
varchar |
正在等待锁的事务ID。 |
waiting_trx_started |
datetime |
正在等待锁的事务开始的时间。 |
waiting_trx_age |
time |
正在等待锁的事务持续的时间。 |
waiting_trx_rows_locked |
bigint |
正在等待锁的事务锁定的行数。 |
waiting_trx_rows_modified |
bigint |
正在等待锁的事务修改的行数。 |
waiting_trx_query |
varchar |
正在等待锁的事务正在执行的SQL语句。 |
waiting_trx_state |
varchar |
正在等待锁的事务的状态。 |
示例查询：
SELECT * FROM sys.innodb_lock_waits;这条语句会列出所有当前正在等待锁的事务信息。
实际案例：
假设我们执行上述查询后，得到以下结果：
| wait_started | wait_age | locked_table | locking_trx_query | waiting_trx_query |
|---|---|---|---|---|
| 2023-10-27 10:00:00 | 00:00:10 | test.t1 |
UPDATE t1 SET col1 = 1 WHERE id = 1; | UPDATE t1 SET col2 = 2 WHERE id = 1; |
从这个结果中，我们可以清晰地看到：
- 有一个事务从 2023-10-27 10:00:00 开始等待锁，已经等待了 10 秒。
- 等待的锁位于
test数据库的t1表上。 - 持有锁的事务正在执行
UPDATE t1 SET col1 = 1 WHERE id = 1;。 - 等待锁的事务正在执行
UPDATE t1 SET col2 = 2 WHERE id = 1;。
通过这些信息，我们可以很容易地判断出这是一个典型的行锁竞争导致的锁等待。两个事务都在尝试更新同一行数据，导致其中一个事务需要等待。
结合sys.processlist进一步分析
仅仅知道哪些事务在等待锁还不够，我们还需要知道这些事务的更多信息，例如它们是由哪个用户发起的、从哪个主机连接的等等。 这时，我们可以结合sys.processlist视图来获取更详细的信息。
示例查询：
SELECT
pl.user,
pl.host,
pl.db,
ilw.wait_age,
ilw.locked_table,
ilw.locking_trx_query,
ilw.waiting_trx_query
FROM sys.innodb_lock_waits ilw
JOIN sys.processlist pl ON ilw.waiting_trx_id = pl.trx_id;这个查询会将sys.innodb_lock_waits和sys.processlist两个视图连接起来，从而可以获得等待锁的事务的用户、主机、数据库等信息。
实际案例：
假设我们在上述案例的基础上，执行这个查询后，得到以下结果：
| user | host | db | wait_age | locked_table | locking_trx_query | waiting_trx_query |
|---|---|---|---|---|---|---|
| user1 | 192.168.1.10 | test | 00:00:10 | test.t1 |
UPDATE t1 SET col1 = 1 WHERE id = 1; | UPDATE t1 SET col2 = 2 WHERE id = 1; |
现在，我们不仅知道是两个UPDATE语句导致了锁等待，还知道等待锁的事务是由 user1 从 192.168.1.10 主机连接到 test 数据库发起的。
案例分析与解决方案
有了这些信息，我们就可以开始分析问题并制定解决方案了。 针对上述案例，可能的解决方案包括：
- 优化SQL语句: 检查
UPDATE语句的执行计划，确保使用了正确的索引，避免全表扫描。 - 减少锁的持有时间: 尽量缩短事务的执行时间，避免长时间持有锁。
- 调整事务隔离级别: 如果业务允许，可以考虑降低事务隔离级别，例如从
REPEATABLE READ降到READ COMMITTED，以减少锁竞争。 - 应用层避免并发更新同一行: 通过在应用层实现乐观锁或悲观锁，避免多个事务同时更新同一行数据。
- 调整参数: 可以调整innodb_lock_wait_timeout 参数，控制锁等待超时时间。
死锁检测与处理
除了锁等待之外，死锁也是一个常见的问题。MySQL会自动检测死锁，并回滚其中一个事务来解除死锁。 但是，了解死锁的发生原因，避免死锁的发生，才是更重要的。
可以通过查看MySQL的错误日志来了解死锁的发生情况。 错误日志中会包含死锁发生的详细信息，包括参与死锁的事务、它们正在执行的SQL语句等等。
sys schema并没有直接提供死锁相关的视图，但我们可以通过解析错误日志来获取死锁信息。也可以通过设置 log_error_verbosity 参数为 3 来增加错误日志的详细程度，从而更容易地发现和分析死锁。
其他sys Schema锁视图的使用
除了sys.innodb_lock_waits之外，sys schema还提供了其他的锁视图，可以帮助我们更全面地了解锁的状态。
sys.innodb_locks: 提供了当前InnoDB引擎中所有锁的信息。 可以使用此视图来查看当前有哪些锁被持有，以及持有锁的事务信息。
SELECT * FROM sys.innodb_locks;这个视图提供了更加底层的锁信息，包括锁的类型（例如，共享锁、排他锁）、锁定的对象（例如，表、行）等等。
sys.schema_table_lock_waits: 展示了表级别的锁等待信息。 如果怀疑是表锁导致的性能问题，可以使用此视图进行分析。
SELECT * FROM sys.schema_table_lock_waits;这个视图提供了表级别的锁等待信息，包括等待的事务、持有锁的事务、等待的时间等等。
模拟锁等待场景进行分析
为了更好地理解如何使用 sys.innodb_lock_waits，我们来模拟一个简单的锁等待场景：
- 创建测试表:
CREATE TABLE t1 (
id INT PRIMARY KEY,
col1 INT,
col2 INT
);
INSERT INTO t1 (id, col1, col2) VALUES (1, 1, 1);-
开启两个事务: 分别在两个MySQL客户端中执行以下操作。
事务 1:
START TRANSACTION;
UPDATE t1 SET col1 = 2 WHERE id = 1;
-- 故意不提交事务**事务 2:**START TRANSACTION;
UPDATE t1 SET col2 = 2 WHERE id = 1;
COMMIT;由于事务 1 已经锁定了 t1 表中 id = 1 的行，事务 2 在尝试更新同一行时，会被阻塞，进入锁等待状态。
- 使用
sys.innodb_lock_waits查看锁等待信息:
SELECT * FROM sys.innodb_lock_waits;执行该语句后，我们应该能够看到类似前面案例的结果，显示事务 2 正在等待事务 1 释放锁。
- 提交事务 1:
COMMIT;提交事务 1 后，事务 2 应该能够立即执行完成。
通过这个简单的实验，我们可以更直观地理解锁等待的原理，以及如何使用 sys.innodb_lock_waits 视图来定位锁等待问题。
注意事项
sysschema依赖于performance_schema，因此需要确保performance_schema已经启用。 可以通过检查performance_schema变量的状态来确认：
SHOW GLOBAL VARIABLES LIKE 'performance_schema';如果 performance_schema 的值为 OFF，需要修改MySQL配置文件，将其设置为 ON，然后重启MySQL服务。
sysschema的视图是只读的，不能直接修改其中的数据。- 分析锁等待问题时，需要综合考虑多个因素，例如SQL语句的执行计划、事务的隔离级别、硬件资源等等。
- 在生产环境中，应该谨慎地执行
KILL QUERY或KILL CONNECTION命令，因为这可能会导致数据不一致或其他问题。
深入理解锁，高效应对等待
通过今天的讨论，我们深入了解了如何利用sys schema提供的锁视图来快速定位MySQL锁等待问题。sys.innodb_lock_waits等视图为我们提供了强大的分析工具，结合sys.processlist可以更全面地了解问题根源。 记住，理解锁的原理，结合实际案例进行分析，才能更有效地解决锁等待问题，提升MySQL数据库的性能。