极客笔记如何使用MySQL查看事务
在使用MySQL数据库进行开发和管理时，事务是一个非常重要的概念。事务可以保证数据的一致性和可靠性，同时也提供了回滚和提交的操作。本文将详细介绍如何使用MySQL查看事务。
1. 查看当前事务状态
在MySQL中，可以使用如下命令查看当前事务的状态：
SHOW ENGINE INNODB STATUS;
运行上述命令后，会得到一段包含事务信息的输出。其中，事务的状态通常位于”TRANSACTIONS”区域中，可以根据输出信息中的”ACTIVE”、”HISTORY LIST”等关键词来定位事务的状态。
示例代码和运行结果如下：
mysql> SHOW ENGINE INNODB STATUS;
输出：
...
TRANSACTIONS
------------
Trx id counter 19570
Purge done for trx's n:o < 19568 undo n:o < 0 state: running but idle
History list length 7
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 294, OS thread handle 0x7f8f3bde8700, query id 1189 localhost root
SHOW ENGINE INNODB STATUS
---TRANSACTION 19569, ACTIVE 592 sec
MySQL thread id 293, OS thread handle 0x7f8f3bde9d00, query id 1178 localhost root
2. 查看当前连接的事务
如果需要查看当前连接的事务，可以使用如下命令：
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
运行上述命令后，会得到一个表格，其中包含了当前连接的所有事务的详细信息，包括事务ID、事务状态、事务持续时间等。
示例代码和运行结果如下：
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
输出：
+---------------------+--------+-----------+-------------------+-------------------+-------------------+-------------+------------------------+-------------------+-------------------------------------+-------------------+-----------------+
| trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use | trx_lock_structs | trx_isolation_level |
+---------------------+--------+-----------+-------------------+-------------------+-------------------+-------------+------------------------+-------------------+-------------------------------------+-------------------+-----------------+
| 19570 | RUNNING | 2021-12-01 10:54:12 | NULL | 2021-12-01 10:54:12 | 97 | 294 | NULL | NULL | 4 | 2 | REPEATABLE READ |
| 19569 | ACTIVE | 2021-12-01 10:54:05 | NULL | NULL | 97 | 293 | SHOW ENGINE INNODB STATUS | NULL | 0 | 0 | REPEATABLE READ |
+---------------------+--------+-----------+-------------------+-------------------+-------------------+-------------+------------------------+-------------------+-------------------------------------+-------------------+-----------------+
3. 查看事务持有的锁信息
如果需要查看事务所持有的锁信息，可以使用如下命令：
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS WHERE trx_id = <trx_id>;
其中，<trx_id>需要替换为实际事务的ID。
示例代码和运行结果如下：
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS WHERE trx_id = 19569;
输出：
+-------------+---------+-----------+----------------+------------------------+----------------+-----------------+--------------+-------------+-----------+------------+-------------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data | lock_info | lock_status |
+-------------+---------+-----------+----------------+------------------------+----------------+-----------------+--------------+-------------+-----------+------------+-------------+
| 71:691:3:12 | 19569 | RECORD | BINARY | `test`.`example_table` | `PRIMARY` | | 12 | 691 | 3 | PRIMARY | GRANTED |
+-------------+---------+-----------+----------------+------------------------+----------------+-----------------+--------------+-------------+-----------+------------+-------------+
4. 查看事务历史信息
如果需要查看事务的历史信息，可以使用如下命令：
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX_HISTORY;
运行上述命令后，会得到一个表格，其中包含了所有活动和历史事务的详细信息，包括事务ID、事务状态、开始时间和结束时间等。
示例代码和运行结果如下：
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX_HISTORY;
输出：
+-------------------+---------------------+---------------------+--------------+-------------------+---------------------+------------+-------------------+---------------------+------------------------+--------------------------+------------------+
| trx_id | trx_started | trx_requested_locks | trx_state | trx_mysql_thread_id | trx_query | trx_started_at | trx_requested_lock_id | trx_wait_started_at | trx_weight | trx_example_info | trx_operation_id |
+-------------------+---------------------+---------------------+--------------+-------------------+---------------------+------------+-------------------+---------------------+------------------------+--------------------------+------------------+
| 19569 | 2021-12-01 10:53:56 | 0 | RUNNING | 293 | SHOW ENGINE INNODB | NULL | NULL | 2021-12-01 10:54:05 | 97 | | NULL |
| 19570 | 2021-12-01 10:54:12 | 2 | RUNNING | 294 | NULL | NULL | 727441 | NULL | 97 | trx_mysql_thread_id = 294 | NULL |
| 19568 | 2021-12-01 10:53:54 | 0 | RUNNING | 292 | UPDATE `example_table` SET `value` = 'new_value' WHERE `id` = 1 | NULL | NULL | NULL | 20 | trx_mysql_thread_id = 292 | NULL |
+-------------------+---------------------+---------------------+--------------+-------------------+---------------------+------------+-------------------+---------------------+------------------------+--------------------------+------------------+
5. 查看存储引擎层面事务日志信息
如果需要查看存储引擎层面的事务日志信息，可以使用如下命令：
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TRANSACTIONS;
运行上述命令后，会得到一个表格，其中包含了存储引擎层面的事务信息，包括事务ID、事务状态、开始时间和结束时间等。
示例代码和运行结果如下：
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TRANSACTIONS;
输出：
+-----------+-----------+-----------+---------+---------------+-----------+-----------+-----------+-----------------------+------------------+------------------------+------------------------+------------------------+------------------------+
| TRX_ID | TRX_STATE | TRX_PURGE | TRX_SYS | TRX_USER | TRX_HOST | TRX_IP | TRX_MYSQL_THREAD_ID | TRX_QUERY | TRX_OPERATION_ID | TRX_OPERATION_STATE | TRX_OPERATION_COUNTER | TRX_STARTED | TRX_CREATED |
+-----------+-----------+-----------+---------+---------------+-----------+-----------+---------------------+-----------------------+------------------+------------------------+------------------------+------------------------+------------------------+
| 19571 | RUNNING | 0 | 0 | root | localhost | ::1 | 297 | | 297329 | NULL | 0 | 2021-12-01 10:55:06.0 | 2021-12-01 10:55:06.0 |
| 19572 | RUNNING | 0 | 0 | root | localhost | ::1 | 299 | | 297331 | NULL | 0 | 2021-12-01 10:55:07.0 | 2021-12-01 10:55:07.0 |
| 19573 | RUNNING | 0 | 0 | root | localhost | ::1 | 300 | | 297332 | NULL | 0 | 2021-12-01 10:55:09.0 | 2021-12-01 10:55:09.0 |
+-----------+-----------+-----------+---------+---------------+-----------+-----------+---------------------+-----------------------+------------------+------------------------+------------------------+------------------------+------------------------+
以上是使用MySQL查看事务的几个示例代码和对应的运行结果。通过这些命令，我们可以轻松地查看当前的事务状态、连接的事务、事务持有的锁、事务历史信息以及存储引擎层面的事务日志信息。这些信息对于进行数据库监控、故障排查和性能优化非常有帮助。