MySQL 查看事务和锁情况的常用语句分享

MySQL 是一个常用的关系型数据库管理系统，在应用过程中，事务和锁的处理非常重要。本篇文章将分享如何使用 MySQL 命令来查看事务和锁的情况，以及如何使用这些命令来分析和调试 MySQL 数据库。

查看事务情况

1. 查看当前事务

我们可以使用以下命令查看当前正在进行的事务：

SHOW ENGINE Innodb STATUS\G

命令执行后会返回一些 Innodb 引擎的状态信息，其中包含当前正在进行的事务信息。在返回的信息中，我们可以找到以下内容：

---TRANSACTION 351fd9, ACTIVE 424 sec
2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 2
MySQL thread id 1234, OS thread handle 0x7f69b8489700, query id 1234567 localhost root

其中，“ACTIVE”表示该事务正在进行；“MySQL thread id” 表示该事务对应的 MySQL 进程 ID；“query id” 表示该事务对应的查询 ID。我们可以使用这些信息来定位当前正在进行的事务。

2. 查看已提交或已回滚的事务

我们可以使用以下命令查看已提交或已回滚的事务：

SELECT * FROM information_schema.INNODB_TRX;

命令执行后会返回所有已提交或已回滚的事务信息。在返回的信息中，我们可以找到以下内容：

+-----+-----------------------+---------------------+---------------------+------------+
| trx | trx_state             | trx_started         | trx_requested_locks | trx_wait_started |
+-----+-----------------------+---------------------+---------------------+------------+
| 329 | COMMITTED             | 2021-05-13 10:20:32 |                     |                |
| 330 | COMMITTED             | 2021-05-13 10:22:32 |                     |                |
| 331 | ROLLING BACK          | 2021-05-13 10:23:34 |                     |                |
| 332 | RUNNING               | 2021-05-13 10:24:36 |                     |                |
+-----+-----------------------+---------------------+---------------------+------------+

其中，“trx”表示该事务的 ID；“trx_state” 表示该事务的状态，包括 RUNNING（进行中）、LOCK WAIT（等待锁）、COMMITTED（已提交）、ROLLING BACK（正在回滚）等；“trx_started” 表示该事务的开始时间；“trx_requested_locks” 表示该事务请求的锁信息；“trx_wait_started” 表示该事务等待锁的开始时间。我们可以使用这些信息来定位已提交或已回滚的事务。

查看锁情况

1. 查看当前锁

我们可以使用以下命令查看当前正在存在的锁：

SELECT * FROM information_schema.INNODB_LOCKS;

命令执行后会返回所有当前正在存在的锁信息。在返回的信息中，我们可以找到以下内容：

+-------------+-------------+-------------+-------------------+-------------------+------------------+---------------+---------------+-------------------+
| lock_id     | lock_trx_id | lock_mode   | lock_type         | lock_table        | lock_index       | lock_space_id | lock_page_no  | lock_rec_lock_els |
+-------------+-------------+-------------+-------------------+-------------------+------------------+---------------+---------------+-------------------+
| 7388:45:3:1 | 7388        | S           | RECORD            | `test`.`user`     | PRIMARY          | 45            | 3             |                   |
| 7389:45:3:1 | 7389        | S           | RECORD            | `test`.`user`     | PRIMARY          | 45            | 3             |                   |
| 7390:45:3:1 | 7390        | X           | RECORD            | `test`.`user`     | PRIMARY          | 45            | 3             |                   |
+-------------+-------------+-------------+-------------------+-------------------+------------------+---------------+---------------+-------------------+

其中，“lock_id”表示锁的 ID；“lock_trx_id” 表示锁对应的事务 ID；“lock_mode” 表示锁的模式，包括 S（共享锁）和 X（排他锁）；“lock_type” 表示锁的类型，包括 RECORD（行锁）和 TABLE（表锁）；“lock_table” 表示被锁定的表名；“lock_index” 表示被锁定的索引；“lock_space_id” 表示锁定对象在磁盘上的空间 ID；“lock_page_no” 表示锁定对象在磁盘上的页号；“lock_rec_lock_els” 表示记录锁额外状态信息。我们可以使用这些信息来定位当前正在存在的锁。

2. 查看当前锁等待列表

如果一个事务需要等待一个正在被其他事务占用的锁时，该事务就会被添加到锁等待列表中。我们可以使用以下命令查看当前锁等待列表：

SELECT * FROM information_schema.INNODB_LOCK_WAITS;

命令执行后会返回所有当前在等待锁的事务信息。在返回的信息中，我们可以找到以下内容：

+-------------------+-------------+-------------+-------------+-------------------+-------------------+------------------+---------------+---------------+-------------------+
| requesting_trx_id | blocking_trx_id | lock_mode   | lock_type  | lock_table                  | lock_index                  | lock_space_id | lock_page_no  | lock_rec_lock_els |
+-------------------+-------------+-------------+---------------------+-------------------+------------------+---------------+---------------+-------------------+
| 21235             | 21234       | X           | RECORD            | `test`.`user`               | PRIMARY          | 45            | 3             |                   |
+-------------------+-------------+-------------+---------------------+-------------------+------------------+---------------+---------------+-------------------+

其中，“requesting_trx_id”表示正在等待锁的事务 ID；“blocking_trx_id” 表示正在占用该锁的事务 ID；“lock_mode” 表示被占用的锁的模式，包括 S（共享锁）和 X（排他锁）；“lock_type” 表示被占用的锁的类型，包括 RECORD（行锁）和 TABLE（表锁）；“lock_table” 表示被锁定的表名；“lock_index” 表示被锁定的索引；“lock_space_id” 表示锁定对象在磁盘上的空间 ID；“lock_page_no” 表示锁定对象在磁盘上的页号；“lock_rec_lock_els” 表示记录锁额外状态信息。我们可以使用这些信息来定位当前锁等待列表中的事务及锁的信息。

总结

本篇文章介绍了如何使用 MySQL 命令来查看事务和锁的情况，以及如何使用这些命令来进行调试和分析。通过对事务和锁的了解，我们可以更好地管理数据库并解决相关问题。