SHOW TRANSACTION ISOLAATION LEVEL; 查看当前会话的事务隔离级别。MySQL查询事务详解
一、背景介绍
在MySQL数据库中，事务管理是确保数据完整性和一致性的重要机制，事务是指一组操作的集合，这些操作要么全部成功，要么全部失败回滚，了解如何查询和管理事务对于数据库管理员和开发人员来说至关重要，本文将详细介绍如何使用MySQL查询当前正在执行的事务以及等待锁的事务，并提供相关示例和常见问题解答。
二、查询正在执行的事务
要查询当前正在执行的事务，可以使用information_schema.INNODB_TRX表，该表包含了所有正在进行的事务的信息，以下是常用的SQL语句：
SELECT * FROM information_schema.INNODB_TRX;
字段说明：
trx_id: 事务ID。
trx_state: 事务状态，有以下几种状态：RUNNING、LOCK WAIT、ROLLING BACK 和 COMMITTING。
trx_started: 事务开始时间。
trx_requested_lock_id: 事务当前正在等待锁的标识，可以与INNODB_LOCKS 表 JOIN 以得到更多详细信息。
trx_wait_started: 事务开始等待的时间。
trx_weight: 事务的权重。
trx_mysql_thread_id: 事务线程 ID，可以与PROCESSLIST 表 JOIN。
trx_query: 事务正在执行的 SQL 语句。
trx_operation_state: 事务当前操作状态。
trx_tables_in_use: 当前事务执行的 SQL 中使用的表的个数。
trx_tables_locked: 当前执行 SQL 的行锁数量。
trx_lock_structs: 事务保留的锁数量。
trx_lock_memory_bytes: 事务锁住的内存大小，单位为 BYTES。
trx_rows_locked: 事务锁住的记录数，包含标记为 DELETED，并且已经保存到磁盘但对事务不可见的行。
trx_rows_modified: 事务更改的行数。
trx_concurrency_tickets: 事务并发票数。
trx_isolation_level: 当前事务的隔离级别。
trx_unique_checks: 是否打开唯一性检查的标识。
trx_foreign_key_checks: 是否打开外键检查的标识。
trx_last_foreign_key_error: 最后一次的外键错误信息。
trx_adaptive_hash_latched: 自适应散列索引是否被当前事务锁住的标识。
trx_adaptive_hash_timeout: 是否立刻放弃为自适应散列索引搜索 LATCH 的标识。
三、查询等待锁的事务
要查询等待锁的事务，可以使用以下SQL语句：
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
字段说明：
requesting_trx_id: 请求事务的 ID。
requested_lock_id: 事务所等待的锁定的 ID，可以与INNODB_LOCKS 表 JOIN。
blocking_trx_id: 阻塞事务的 ID。
blocking_lock_id: 某一事务的锁的 ID，该事务阻塞了另一事务的运行，可以与INNODB_LOCKS 表 JOIN。
四、查询锁信息
要查看当前正在锁的事务，可以使用以下SQL语句：
SELECT * FROM information_schema.INNODB_LOCKS;
字段说明：
lock_id: 锁 ID。
lock_trx_id: 拥有锁的事务 ID，可以与INNODB_TRX 表 JOIN 得到事务的详细信息。
lock_mode: 锁的模式，有如下锁类型：行级锁包括：S、X、IS、IX，分别代表：共享锁、排它锁、意向共享锁、意向排它锁，表级锁包括：S_GAP、X_GAP、IS_GAP、IX_GAP 和 AUTO_INC，分别代表共享间隙锁、排它间隙锁、意向共享间隙锁、意向排它间隙锁和自动递增锁。
lock_type: 锁的类型，RECORD 代表行级锁，TABLE 代表表级锁。
lock_table: 被锁定的或者包含锁定记录的表的名称。
lock_index: 当LOCK_TYPE=’RECORD’ 时，表示索引的名称；否则为 NULL。
lock_space: 当LOCK_TYPE=’RECORD’ 时，表示锁定行的表空间 ID；否则为 NULL。
lock_page: 当LOCK_TYPE=’RECORD’ 时，表示锁定行的页号；否则为 NULL。
lock_rec: 当LOCK_TYPE=’RECORD’ 时，表示一堆页面中锁定行的数量，亦即被锁定的记录号；否则为 NULL。
lock_data: 当LOCK_TYPE=’RECORD’ 时，表示锁定行的主键；否则为NULL。
五、示例操作
1. 开启两个会话窗口
会话1：开启一个事务，但不提交。
begin; update test.sbtest1 set pad='yyy' where id =1; select sys.ps_thread_id(connection_id());
会话2：查看会话1的事务信息。
SELECT * FROM information_schema.INNODB_TRX\G;
输出结果：
1. row***************
trx_id: 387864,04589, trx_state: LOCK WAIT, ...通过上述查询，可以看到会话1中的事务ID为38786404589，状态为LOCK WAIT。
2. 查询等待锁的事务
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
输出结果：
1. row***************
requesting_trx_id: 38786404589, requesting_lock_id: 38786404589:113:4:3, ...通过上述查询，可以看到会话1中的事务正在等待锁，且请求锁ID为38786404589:113:4:3。
3. 查询锁信息
SELECT * FROM information_schema.INNODB_LOCKS;
输出结果：
1. row***************
lock_id: 38786404589:113:4:3, lock_trx_id: 38786404589, lock_mode: X, ...通过上述查询，可以看到锁的ID为38786404589:113:4:3，拥有锁的事务ID为38786404589。
六、FAQs
Q1: 如何确定某个事务是否处于活动状态？
A1: 你可以通过查询information_schema.INNODB_TRX 表中的trx_state 字段来确定事务的状态，如果trx_state 为 "RUNNING"，则表示事务处于活动状态。
SELECT * FROM information_schema.INNODB_TRX WHERE trx_state = 'RUNNING';
Q2: 如果发现一个死锁，应该如何处理？
A2: 如果发现一个死锁，通常需要终止其中一个事务来解除死锁，你可以使用以下命令终止特定的事务：
-假设事务ID为1234567890 KILL 1234567890;
然后重新执行被终止的事务即可解决死锁问题。
七、小编有话说
了解如何查询和管理MySQL中的事务对于数据库的性能优化和故障排查非常重要，通过合理地使用这些查询语句，你可以更好地监控和管理数据库中的事务，从而提高系统的稳定性和性能，希望本文对你有所帮助，如果你有任何疑问或建议，欢迎留言讨论！