极客笔记Mysql查看锁
简介
在使用MySQL数据库时，可能会遇到锁的问题。锁是一种用来控制并发访问的机制，用于保证数据的完整性和一致性。通过查看锁的信息，可以了解到当前数据库中的锁定情况，便于定位和解决并发冲突的问题。
本文将详细介绍如何在MySQL中查看锁的相关信息，包括查看当前会话的锁状态、查看正在被当前会话持有的锁、查看正在被阻塞的锁等。
查看当前会话的锁状态
可以使用SHOW FULL PROCESSLIST;命令来查看当前会话的锁状态。该命令会显示当前所有正在运行的MySQL线程的详细信息，包括线程的ID、状态、执行的SQL语句以及所持有的锁等。下面是一个示例：
SHOW FULL PROCESSLIST;
运行结果：
+----+------+-----------+---------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+---------+---------+------+------------------------+------------------+
| 1 | root | localhost | test | Sleep | 15 | | NULL |
| 2 | root | localhost | test | Query | 15 | User lock | SELECT * FROM tbl |
| 3 | root | localhost | test | Sleep | 15 | | NULL |
+----+------+-----------+---------+---------+------+------------------------+------------------+
上述结果中的State列表示当前线程的状态，如果有User lock的状态表示该线程持有了锁。
查看正在被当前会话持有的锁
使用SELECT * FROM INFORMATION_SCHEMA.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 |
+------------------+-------------+-----------+-----------+-----------+-----------+---------------+-------------+-----------+-------------------+
| 107737:10:3:4 | 107737 | X | RECORD | `test`.`tbl` | PRIMARY | 10 | 3 | 4 | 123 |
| 107738:10:3:5 | 107738 | X | RECORD | `test`.`tbl` | PRIMARY | 10 | 3 | 5 | 456 |
+------------------+-------------+-----------+-----------+-----------+-----------+---------------+-------------+-----------+-------------------+
上述结果中的lock_table列代表锁所属的数据库和表名，lock_mode列代表锁的模式（X代表写锁，S代表共享锁），lock_type列代表锁的类型，lock_data列代表锁的详细信息。
查看正在被阻塞的锁
使用SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;命令可以查看当前正在被阻塞的锁的信息。该命令会返回当前正在被阻塞的锁以及引发阻塞的锁的详细信息。下面是一个示例：
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
运行结果：
+------------------+--------------+-----------+-----------+--------------+-----------+---------------+--------------+---------------+---------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | blocking_trx_started | blocking_query | blocking_lock_mode | blocking_lock_type | blocking_lock_table | blocking_lock_index |
+------------------+--------------+-----------+-----------+--------------+-----------+---------------+--------------+---------------+-----------------+
| 107739 | 107740:10:3:7 | 107738 | 107738:10:3:5 | 2022-01-01 00:00:01 | UPDATE tbl SET | X | RECORD | `test`.`tbl` | PRIMARY |
+------------------+--------------+-----------+-----------+--------------+-----------+---------------+--------------+---------------+-----------------+
上述结果中的requesting_trx_id列代表正在请求锁的事务ID，requested_lock_id列代表正在请求的锁的ID，blocking_trx_id列代表当前被阻塞的事务ID，blocking_lock_id列代表引发阻塞的锁的ID。
示例代码
下面给出5个示例代码，演示如何在MySQL中查看锁的相关信息。
示例1：查看当前会话的锁状态
SHOW FULL PROCESSLIST;
输出：
+----+------+-----------+---------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+---------+---------+------+------------------------+------------------+
| 1 | root | localhost | test | Sleep | 5 | | NULL |
| 2 | root | localhost | test | Query | 5 | User lock | SELECT * FROM tbl |
| 3 | root | localhost | test | Sleep | 5 | | NULL |
+----+------+-----------+---------+---------+------+------------------------+------------------+
示例2：查看正在被当前会话持有的锁
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 |
+------------------+-------------+-----------+-----------+-----------+-----------+---------------+-------------+-----------+-------------------+
| 107737:10:3:4 | 107737 | X | RECORD | `test`.`tbl` | PRIMARY | 10 | 3 | 4 | 123 |
| 107738:10:3:5 | 107738 | X | RECORD | `test`.`tbl` | PRIMARY | 10 | 3 | 5 | 456 |
+------------------+-------------+-----------+-----------+-----------+-----------+---------------+-------------+-----------+-------------------+
示例3：查看正在被阻塞的锁
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
输出：
+------------------+--------------+-----------+-----------+--------------+-----------+---------------+--------------+---------------+---------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | blocking_trx_started | blocking_query | blocking_lock_mode | blocking_lock_type | blocking_lock_table | blocking_lock_index |
+------------------+--------------+-----------+-----------+--------------+-----------+---------------+--------------+---------------+-----------------+
| 107739 | 107740:10:3:7 | 107738 | 107738:10:3:5 | 2022-01-01 00:00:01 | UPDATE tbl SET | X | RECORD | `test`.`tbl` | PRIMARY |
+------------------+--------------+-----------+-----------+--------------+-----------+---------------+--------------+---------------+-----------------+
示例4：查看特定事务持有的锁
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS WHERE lock_trx_id = 107737;
输出：
+------------------+-------------+-----------+-----------+-----------+-----------+---------------+-------------+-----------+-------------------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table| lock_index| lock_space | lock_page | lock_rec | lock_data |
+------------------+-------------+-----------+-----------+-----------+-----------+---------------+-------------+-----------+-------------------+
| 107737:10:3:4 | 107737 | X | RECORD | `test`.`tbl` | PRIMARY | 10 | 3 | 4 | 123 |
+------------------+-------------+-----------+-----------+-----------+-----------+---------------+-------------+-----------+-------------------+
示例5：查看特定事务被阻塞的锁
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS WHERE blocking_trx_id = 107738;
输出：
+------------------+--------------+-----------+-----------+--------------+-----------+---------------+--------------+---------------+---------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | blocking_trx_started | blocking_query | blocking_lock_mode | blocking_lock_type | blocking_lock_table | blocking_lock_index |
+------------------+--------------+-----------+-----------+--------------+-----------+---------------+--------------+---------------+-----------------+
| 107739 | 107740:10:3:7 | 107738 | 107738:10:3:5 | 2022-01-01 00:00:01 | UPDATE tbl SET | X | RECORD | `test`.`tbl` | PRIMARY |
+------------------+--------------+-----------+-----------+--------------+-----------+---------------+--------------+---------------+-----------------+
通过上述示例代码，可以轻松地在MySQL中查看锁的相关信息。这对于发现并发冲突、解决锁冲突以及优化数据库性能都非常有帮助。通过了解锁定情况，可以更好地理解数据库的并发访问机制，并及时处理潜在的问题。