MySQL的锁等待：如何通过sys模式下的锁视图（Lock View）快速定位问题，并找到锁定的资源？

好的，下面我们开始今天的讲座，主题是：MySQL的锁等待：如何通过sys模式下的锁视图（Lock View）快速定位问题，并找到锁定的资源。

在MySQL数据库的日常运维中，锁等待问题是常见的性能瓶颈之一。当多个事务尝试访问同一资源时，锁机制可以保证数据的一致性，但也可能导致事务阻塞，从而降低数据库的整体性能。MySQL的sys模式提供了一系列视图，可以帮助我们快速定位锁等待问题，并找到被锁定的资源。

一、锁的类型和层级

在深入sys模式的锁视图之前，我们先简单回顾一下MySQL中常见的锁类型和层级：

• 锁类型：
  • 共享锁 (Shared Lock, S Lock): 允许持有锁的事务读取数据，但不允许修改。多个事务可以同时持有同一资源的共享锁。使用SELECT ... LOCK IN SHARE MODE 语句获取共享锁。
  • 排他锁 (Exclusive Lock, X Lock): 允许持有锁的事务读取和修改数据，其他事务无法持有该资源的任何锁（包括共享锁和排他锁）。使用SELECT ... FOR UPDATE 语句或者 UPDATE、DELETE 语句获取排他锁。
  • 意向锁 (Intention Lock, IS/IX Lock): 表级别的锁，表示事务打算在更细粒度的级别（例如行级别）获取共享锁或排他锁。
    • 意向共享锁 (Intention Shared Lock, IS Lock): 表示事务打算在表中的某些行上获取共享锁。
    • 意向排他锁 (Intention Exclusive Lock, IX Lock): 表示事务打算在表中的某些行上获取排他锁。
  • 自增锁 (Auto-inc Lock): 一种特殊的表级锁，用于确保 AUTO_INCREMENT 列值的唯一性。
• 锁层级：
  • 表锁： 锁定整个表，影响范围大，并发性差。
  • 行锁： 锁定表中的特定行，影响范围小，并发性好。InnoDB存储引擎支持行锁。
  • 页锁： 锁定数据页，介于表锁和行锁之间。MyISAM存储引擎支持页锁。

二、sys模式锁视图概览

sys模式提供了一系列与锁相关的视图，其中最常用的包括：

• sys.processlist：显示当前MySQL实例中所有连接的详细信息，包括状态、命令、执行时间等。虽然不是专门的锁视图，但可以用来查看哪些线程正在执行长时间运行的查询，这些查询可能持有锁。
• sys.innodb_lock_waits：显示当前正在等待锁的事务的信息，包括阻塞事务和被阻塞事务的ID、等待时间和锁定的资源等。这是定位锁等待问题的关键视图。
• sys.innodb_locks：显示当前所有InnoDB事务持有的锁的信息，包括锁类型、锁定的资源和事务ID等。
• sys.schema_table_lock_waits: 提供更详细的表级别锁等待信息。

三、使用sys.innodb_lock_waits定位锁等待问题

sys.innodb_lock_waits视图是定位锁等待问题的核心。它的结构如下：

列名	数据类型	描述
wait_started	datetime	锁等待开始的时间。
wait_age	time	锁等待持续的时间。
wait_age_secs	bigint	锁等待持续的时间，以秒为单位。
locked_table	varchar	被锁定的表名，格式为 schema.table_name。
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语句 (KILL QUERY <process_id>)。
sql_kill_blocking_connection	varchar	可以用来杀死阻塞连接的SQL语句 (KILL CONNECTION <process_id>)。

示例：

假设我们执行以下查询：

SELECT * FROM sys.innodb_lock_waits;

可能会得到类似以下的输出：

+---------------------+----------+---------------+---------------+--------------------+--------------+------------------+-----------------------+-------------------+-------------------------+----------------------------+------------------+-------------------+-------------------+-----------------------+-------------------+-------------------------+----------------------------+------------------+-------------------+-------------------------+------------------------------+
| wait_started        | wait_age | wait_age_secs | locked_table      | locked_index | locked_type | waiting_trx_id | waiting_trx_started  | waiting_trx_age | waiting_trx_rows_locked | waiting_trx_rows_modified | waiting_lock_id | waiting_lock_mode | blocking_trx_id | blocking_trx_started | blocking_trx_age | blocking_trx_rows_locked | blocking_trx_rows_modified | blocking_lock_id | blocking_lock_mode | sql_kill_blocking_query | sql_kill_blocking_connection |
+---------------------+----------+---------------+---------------+--------------+-------------+------------------+-----------------------+-------------------+-------------------------+----------------------------+------------------+-------------------+-------------------+-----------------------+-------------------+-------------------------+----------------------------+------------------+-------------------+-------------------------+------------------------------+
| 2023-10-27 10:00:00 | 00:00:10 |            10 | `test`.`orders` | NULL         | TABLE       | 12345            | 2023-10-27 09:59:50 | 00:00:20        |                       0 |                          0 | 12345:10         | X                 | 67890            | 2023-10-27 09:59:30 | 00:00:30        |                       1 |                          1 | 67890:20         | X                 | KILL QUERY 100        | KILL CONNECTION 100           |
+---------------------+----------+---------------+---------------+--------------+-------------+------------------+-----------------------+-------------------+-------------------------+----------------------------+------------------+-------------------+-------------------+-----------------------+-------------------+-------------------------+----------------------------+------------------+-------------------+-------------------------+------------------------------+

解读：

• locked_table: 表 test.orders 被锁定。
• waiting_trx_id: 事务ID为 12345 的事务正在等待锁。
• waiting_lock_mode: 事务 12345 正在等待排他锁 (X)。
• blocking_trx_id: 事务ID为 67890 的事务阻塞了 12345。
• blocking_lock_mode: 事务 67890 持有排他锁 (X)。
• sql_kill_blocking_query 和 sql_kill_blocking_connection: 提供了可以直接杀死阻塞查询或连接的SQL语句。KILL QUERY 100 或 KILL CONNECTION 100，其中 100 是阻塞连接的线程ID。

四、找到锁定的资源：深入分析

仅仅知道哪个表被锁定是不够的，我们还需要知道具体锁定了哪些行，以及阻塞事务正在执行什么操作。

1. 查看阻塞事务正在执行的SQL语句：

   我们需要找到 blocking_trx_id 对应的线程ID（process ID）。可以通过以下查询：

   SELECT id, user, host, db, command, time, state, info
   FROM information_schema.processlist
   WHERE id IN (SELECT SUBSTRING_INDEX(sql_kill_blocking_query, ' ', -1) FROM sys.innodb_lock_waits WHERE sql_kill_blocking_query IS NOT NULL);

   或者如果sql_kill_blocking_connection可用，直接使用：

   SELECT id, user, host, db, command, time, state, info
   FROM information_schema.processlist
   WHERE id IN (SELECT SUBSTRING_INDEX(sql_kill_blocking_connection, ' ', -1) FROM sys.innodb_lock_waits WHERE sql_kill_blocking_connection IS NOT NULL);

   这将显示阻塞事务的详细信息，包括它正在执行的SQL语句 (info 列)。 通过分析SQL语句，我们可以了解事务正在访问哪些行。

2. 查看阻塞事务持有的锁：

   我们可以使用 sys.innodb_locks 视图来查看阻塞事务持有的锁的详细信息。

   SELECT *
   FROM sys.innodb_locks
   WHERE lock_trx_id = (SELECT blocking_trx_id FROM sys.innodb_lock_waits WHERE waiting_trx_id = '12345'); -- 将 '12345' 替换为实际的 waiting_trx_id

   这将显示事务 67890 持有的所有锁的信息，包括锁定的表、索引和行。如果 lock_type 是 RECORD，则表示行锁。

3. 查找具体行锁定的记录

   确定锁定行记录的具体信息比较复杂，需要结合InnoDB的内部结构。 一般来说， sys.innodb_locks 中的信息不足以直接确定锁定的行记录内容。 但是，结合步骤1中获得的SQL语句，以及sys.innodb_locks中的lock_index信息，可以缩小范围。

   比如，如果步骤1查到的SQL语句是 UPDATE orders SET status = 'processed' WHERE order_id = 123, 而sys.innodb_locks中 lock_index显示主键索引被锁定，那么我们就可以推断出orders表中 order_id = 123 的记录被锁定。

   更精确的排查需要借助InnoDB的内部工具和调试信息，超出了一般运维的范围。

五、示例代码和案例分析

假设我们有两张表：users 和 orders。

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(255) NOT NULL,
    balance DECIMAL(10, 2) NOT NULL
);

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

INSERT INTO users (username, balance) VALUES ('Alice', 100.00);
INSERT INTO users (username, balance) VALUES ('Bob', 200.00);

INSERT INTO orders (user_id, amount, status) VALUES (1, 50.00, 'pending');
INSERT INTO orders (user_id, amount, status) VALUES (2, 25.00, 'pending');

场景：

• 事务A：更新用户Alice的余额，并创建一个新的订单。
• 事务B：更新现有订单的状态。

事务A:

-- 事务A
START TRANSACTION;
UPDATE users SET balance = balance - 50.00 WHERE id = 1; -- Alice的id是1
INSERT INTO orders (user_id, amount, status) VALUES (1, 50.00, 'completed');
COMMIT;

事务B:

-- 事务B
START TRANSACTION;
UPDATE orders SET status = 'processed' WHERE id = 1; -- 订单id是1
COMMIT;

模拟锁等待：

1. 首先，在会话1中执行事务A的第一个语句（UPDATE users ...），但不提交。这将锁定 users 表中 id = 1 的行。
2. 然后，在会话2中执行事务B的语句（UPDATE orders ...）。 如果两个更新操作都尝试在同一行上获取排他锁，就会发生锁等待。

使用sys模式定位问题：

1. 查询sys.innodb_lock_waits：

   假设我们执行 SELECT * FROM sys.innodb_lock_waits;，得到如下结果：

   +---------------------+----------+---------------+-----------------+--------------+-------------+------------------+-----------------------+-------------------+-------------------------+----------------------------+------------------+-------------------+-------------------+-----------------------+-------------------+-------------------------+----------------------------+------------------+-------------------+-------------------------+------------------------------+
   | wait_started        | wait_age | wait_age_secs | locked_table      | locked_index | locked_type | waiting_trx_id | waiting_trx_started  | waiting_trx_age | waiting_trx_rows_locked | waiting_trx_rows_modified | waiting_lock_id | waiting_lock_mode | blocking_trx_id | blocking_trx_started | blocking_trx_age | blocking_trx_rows_locked | blocking_trx_rows_modified | blocking_lock_id | blocking_lock_mode | sql_kill_blocking_query | sql_kill_blocking_connection |
   +---------------------+----------+---------------+-----------------+--------------+-------------+------------------+-----------------------+-------------------+-------------------------+----------------------------+------------------+-------------------+-------------------+-----------------------+-------------------+-------------------------+----------------------------+------------------+-------------------+-------------------------+------------------------------+
   | 2023-10-27 10:10:00 | 00:00:05 |             5 | `test`.`users`  | PRIMARY      | RECORD      | 23456            | 2023-10-27 10:09:55 | 00:00:10        |                       0 |                          0 | 23456:10         | X                 | 78901            | 2023-10-27 10:09:30 | 00:00:35        |                       1 |                          1 | 78901:20         | X                 | KILL QUERY 200        | KILL CONNECTION 200           |
   +---------------------+----------+---------------+-----------------+--------------+-------------+------------------+-----------------------+-------------------+-------------------------+----------------------------+------------------+-------------------+-------------------+-----------------------+-------------------+-------------------------+----------------------------+------------------+-------------------+-------------------------+------------------------------+

   这表明事务 23456 正在等待锁定 test.users 表的 PRIMARY 索引（主键），等待的是排他锁。 事务 78901 阻塞了它。

2. 查询阻塞事务的SQL：

   SELECT id, user, host, db, command, time, state, info
   FROM information_schema.processlist
   WHERE id = 200;  -- 阻塞事务的线程ID 从sql_kill_blocking_connection 或者 sql_kill_blocking_query 获取

   假设我们看到 info 列显示：

   UPDATE users SET balance = balance - 50.00 WHERE id = 1

   这表明事务 78901 正在更新 users 表中 id = 1 的行。

3. 查询阻塞事务持有的锁：

   SELECT *
   FROM sys.innodb_locks
   WHERE lock_trx_id = '78901';

   这将显示事务 78901 持有的锁的详细信息，确认它锁定了 users 表的 PRIMARY 索引中的 id = 1 的行。

结论：

通过以上分析，我们可以得出结论：

• 事务 23456 正在等待锁定 users 表中 id = 1 的行，等待的是排他锁。
• 事务 78901 阻塞了事务 23456，因为它已经锁定了 users 表中 id = 1 的行，持有的也是排他锁。
• 阻塞事务 78901 正在执行 UPDATE users SET balance = balance - 50.00 WHERE id = 1。

解决方案：

• 如果事务 78901 不需要长时间持有锁，可以等待其提交或回滚。
• 如果事务 78901 持有锁的时间过长，可以考虑优化其SQL语句，减少锁的持有时间。
• 如果锁等待时间过长，影响了数据库的性能，可以考虑杀死事务 78901 (使用 KILL QUERY 200 或 KILL CONNECTION 200)，释放锁资源。 但是，杀死事务可能会导致数据不一致，需要谨慎操作。

六、其他有用的sys模式视图

• sys.schema_table_lock_waits: 提供表级别的锁等待信息，与innodb_lock_waits相比，它侧重于表级别的锁争用，可以帮助识别哪些表经常发生锁等待。
• sys.processlist: 虽然不是专门的锁视图，但是可以用来查看哪些线程正在执行长时间运行的查询，这些查询可能持有锁。结合sys.innodb_lock_waits 可以找出占用锁的线程，并分析其执行的SQL。

七、避免锁等待的建议

• 尽量缩小事务的范围： 事务范围越小，持有锁的时间就越短，减少了锁等待的可能性。
• 尽量减少锁的粒度： 使用行锁代替表锁，减少锁的影响范围。
• 尽量避免长事务： 长事务持有锁的时间较长，容易导致锁等待。
• 尽量使用索引： 合理使用索引可以减少扫描的行数，从而减少锁的持有时间。
• 避免死锁： 按照固定的顺序访问资源，避免循环依赖。
• 设置合理的锁等待超时时间： 如果锁等待时间超过了超时时间，事务会自动回滚，避免长时间阻塞。

简而言之，利用sys.innodb_lock_waits可以快速找到锁等待的事务，然后通过processlist找到阻塞事务执行的SQL，最后用innodb_locks确认阻塞事务持有的锁的详细信息，从而定位锁定的资源。

高效定位锁，合理优化策略

通过sys模式的锁视图，我们可以快速定位锁等待问题，分析阻塞事务和被阻塞事务，找到被锁定的资源。结合实际场景，我们可以采取相应的优化措施，减少锁等待，提升数据库的性能。

熟悉视图结构，解决实际问题

深入理解sys模式下锁视图的结构和使用方法，能够帮助我们更好地诊断和解决锁相关的问题，保障数据库的稳定运行。