MySQL查看锁表语句|极客笔记

MySQL查看锁表语句

在MySQL数据库中，锁表是一种重要的机制，用于控制并发访问数据库表的操作。通过对表进行加锁，可以确保在同一时刻只有一个会话能够对表执行写操作，避免数据不一致和冲突。

在实际开发和运维过程中，我们经常需要查看数据库中哪些表被锁定，以及哪些会话正在等待或持有锁。本文将详细介绍如何使用MySQL来查看锁表情况，并提供一些常用的锁表语句和示例。

查看当前锁表情况

要查看当前数据库中哪些表被锁定，可以通过以下两种方式进行：

1. SHOW VARIABLES LIKE ‘innodb_status_output’;

这个语句用于查看InnoDB引擎的状态输出设置，默认情况下是OFF，需要手动开启。开启方法如下：

SET GLOBAL innodb_status_output = ON;

然后可以使用以下语句查看当前的锁表情况：

SHOW ENGINE INNODB STATUS;

这个命令将返回一个包含大量信息的字符串，其中包含了当前的锁表情况、事务信息等等。在返回的字符串中，可以寻找关键字”LOCK WAIT”、”TRANSACTION”等，来查找哪些表被锁定、哪些会话正在等待锁等信息。

2. 使用信息模式

另一种查看当前锁表情况的方法是使用信息模式，可以通过以下语句查看：

SHOW OPEN TABLES WHERE `In_use` > 0;

这个命令将列出当前有锁定的表，包括表名、引擎、当前状态等信息。

查看特定表的锁情况

除了查看整个数据库的锁表情况外，有时候我们也需要查看某个特定表的锁情况。可以通过以下语句实现：

SELECT * FROM information_schema.INNODB_TRX WHERE trx_mysql_thread_id > 0;

这个语句将列出当前正在活动的事务信息，包括持有锁的会话ID、锁定的表名等。

查找正在锁定某个表的会话

有时候我们需要找到当前正在锁定某个表的会话，可以使用以下语句：

SELECT * FROM information_schema.INNODB_LOCKS WHERE LOCK_TABLE = 'table_name';

这个语句将返回当前正在锁定指定表的会话信息，包括会话ID、锁类型等。

查找等待某个表锁定的会话

如果需要查找当前正在等待某个表锁的会话，可以使用以下语句：

SELECT * FROM information_schema.INNODB_LOCK_WAITS WHERE BLOCKING_TABLE = 'table_name';

这个语句将返回当前正在等待指定表锁的会话信息，包括会话ID、等待的锁类型等。

示例

下面我们通过一个简单的示例来演示如何查看当前数据库中的锁表情况：

首先，创建一个测试表lock_table：

CREATE TABLE lock_table (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

接着，启动一个会话A，并对表lock_table进行写操作：

-- 会话A
BEGIN;
INSERT INTO lock_table VALUES (1, 'Alice');

再启动一个会话B，试图对同一个表进行写操作，但不提交事务：

-- 会话B
BEGIN;
INSERT INTO lock_table VALUES (2, 'Bob');

此时，如果我们尝试查看锁表情况：

SHOW ENGINE INNODB STATUS;

可以在返回的结果中找到如下信息：

---TRANSACTION 1279022, ACTIVE 6 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 3, OS thread handle 12345, query id 1234 localhost root update
INSERT INTO lock_table VALUES (2, 'Bob')
------- TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1234 page no 5 n bits 72 index PRIMARY of table `test`.`lock_table` trx id 1279022 lock_mode X locks rec but not gap waiting

可以看到，会话B正在等待会话A对表lock_table的锁释放。