MySQL 查看被锁住的表

介绍

在 MySQL 中，当一个事务锁定了某个表时，其他事务就无法对该表进行读取和写入操作。这可能会导致性能问题和数据一致性的挑战。因此，了解哪些表被锁住是非常重要的。本文将介绍如何查看 MySQL 数据库中被锁住的表。

1. 查看锁状态

可以使用 SHOW ENGINE INNODB STATUS 命令来查看 InnoDB 存储引擎的状态，该命令将输出包含了当前锁信息的结果。但是，该命令输出的信息比较复杂，需要对输出结果进行解析才能得到有用的锁信息。

下面是使用 SHOW ENGINE INNODB STATUS 命令查看锁信息的示例：

SHOW ENGINE INNODB STATUS\G

示例输出结果：

...
------------
SEMAPHORES
------------
OS WAIT ARRAY INFO: reservation count 23445
OS WAIT ARRAY INFO: signal count 19617
Mutex spin waits 15, rounds 415, OS waits 13
RW-shared spins 48097, OS waits 26658
RW-excl spins 1092, OS waits 377
Spin rounds per wait: 27.67 mutex, 19.13 RW-shared, 10.36 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 135532
Purge done for trx's n:o < 135523 undo n:o < 0 state: running
...

以上输出结果中的 TRANSACTIONS 部分列出了当前的事务信息，包括事务 ID (Trx id) 和事务状态。如果某个事务正在执行队列中等待锁的操作，其状态可能会显示为 “waiting for table metadata lock” 或其他类似的状态。

2. 使用信息模式表

MySQL 提供了一些信息模式表（information_schema tables），可以从这些表中获取与锁相关的信息。

2.1 获取正在锁定的表

可以通过查询 information_schema.INNODB_LOCKS 表来获取当前正在锁定的表的信息。下面是查询 INNODB_LOCKS 表的示例：

SELECT * FROM information_schema.INNODB_LOCKS;

示例输出结果：

+-----------+-------------+-----------+------------+----------------+------------+------------+-----------+----------+-------------------+
| lock_id   | lock_trx_id | lock_mode | lock_type  | lock_table     | lock_index | lock_space | lock_page | lock_rec | lock_data         |
+-----------+-------------+-----------+------------+----------------+------------+------------+-----------+----------+-------------------+
| 298548031 | 298548031   | X         | TABLE      | `mydb`.`mytable` | NULL       | NULL       | NULL      | NULL     | NULL              |
+-----------+-------------+-----------+------------+----------------+------------+------------+-----------+----------+-------------------+

以上输出结果中的 lock_table 列显示了被当前锁定的表的名称。

2.2 获取等待锁的事务和锁定的表

可以通过查询 information_schema.INNODB_LOCK_WAITS 表来获取当前正在等待锁的事务以及被锁定的表的信息。下面是查询 INNODB_LOCK_WAITS 表的示例：

SELECT * FROM information_schema.INNODB_LOCK_WAITS;

示例输出结果：

+-----------+-----------------+-----------+------------+----------------+------------+------------+-----------+-----------+--------------------+
| request_id | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | blocking_pid | requesting_ip | requested_resource | blocking_query | blocking_operation |
+-----------+-----------------+-----------+------------+----------------+------------+------------+-----------+-----------+--------------------+
| 1         | 298548031       | 298548031 | 298548029  | 298548029 | 3145         | NULL           | TABLE              | NULL                | NULL               |
+-----------+-----------------+-----------+------------+----------------+------------+------------+-----------+-----------+--------------------+

以上输出结果中的 requested_resource 列显示了被锁定的表的名称。

3. 查看锁定的表的性能影响

除了查看锁信息，还可以通过查看锁定的表对性能的影响来判断是否存在锁问题。

3.1 查看等待锁定的事务数量

可以使用下面的 SQL 查询来查看当前等待锁的事务的数量：

SELECT COUNT(*) FROM information_schema.INNODB_LOCK_WAITS;

示例输出结果：

+----------+
| COUNT(*) |
+----------+
| 1        |
+----------+

以上输出结果显示了当前等待锁的事务的数量。

3.2 查看锁定表的等待时间

可以使用下面的 SQL 查询来获取锁定的表的等待时间：

SELECT MAX(TIMESTAMPDIFF(SECOND, waiting_query_started, NOW())) AS wait_time
FROM information_schema.INNODB_LOCK_WAITS
WHERE requested_resource LIKE 'TABLE';

示例输出结果：

+-----------+
| wait_time |
+-----------+
| 600       |
+-----------+

以上输出结果显示了锁定表的最长等待时间（以秒为单位）。

结论

通过使用 SHOW ENGINE INNODB STATUS 命令和信息模式表，可以查看 MySQL 数据库中被锁定的表。了解锁信息可以帮助优化数据库性能和解决数据一致性问题。

但是，需要注意的是，查看锁信息只能提供一个快照式的视图，并不能捕获瞬时的锁情况。因此，在查看锁信息的同时，还需要结合其他监控和日志工具，以全面了解数据库中的锁情况。