mysql 查看当前事务id_MySQL 如何查看当前最新事务ID

在个别时候可能需要查看当前最新的事务ID，以便做一些业务逻辑上的判断(例如利用事务ID变化以及前后时差，统计每次事务的响应时长等用途)。

通常地，我们有两种方法可以查看当前的事务ID：

1、执行SHOW ENGINE INNODB STATUS，查看事务相关信息

=====================================

150303 17:16:11 INNODB MONITOR OUTPUT

=====================================

Per second averages calculated from the last 15 seconds

...

------------

TRANSACTIONS

Trx id counter 3359877657 -- 当前最大事务ID

Purge done for trx's n:o < 3359877468 undo n:o < 0 state: running

History list length 324

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 0, not started -- 该会话中执行SHOW ENGINE INNODB STATUS，不会产生事务，所以事务ID为0

MySQL thread id 4692367, OS thread handle 0x51103940, query id 677284426 xx.173ops.com 10.x.x.x yejr init

SHOW /*!50000 ENGINE*/ INNODB STATUS

---TRANSACTION 3359877640, not started --非活跃事务，还未开始

mysql tables in use 1, locked 0

MySQL thread id 4678384, OS thread handle 0x41a57940, query id 677284427 xx.173ops.com 10.x.x.x yejr System lock

select polinfo0_.Fid as Fid39_0_, ...

---TRANSACTION 3359877652, not started

MySQL thread id 4678383, OS thread handle 0x50866940, query id 677284420 xx.173ops.com 10.x.x.x yejr cleaning up

---TRANSACTION 3359877635, ACTIVE 1358 sec, thread declared inside InnoDB 5000 --活跃长事务，运行了1358秒还未结束，要引起注意，可能会导致大量锁等待发生

mysql tables in use 1, locked 1

1 lock struct(s), heap size 376, 0 row lock(s), undo log entries 1

MySQL thread id 3120717, OS thread handle 0x529b4940, query id 677284351 xx.173ops.com 10.x.x.x yejr query end

insert into t_live_room ...

2、查看INFORMATION_SCHEMA.INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS 三个表，通过这些信息能快速发现哪些事务在阻塞其他事务

#先查询 INNODB_TRX 表，看看都有哪些事务

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX\G

*************************** 1. row ***************************

trx_id: 17778 -- 当前事务ID

trx_state: LOCK WAIT -- 处于锁等待状态，也就是等待其他会话释放锁资源

trx_started: 2015-03-04 10:40:26

trx_requested_lock_id: 17778:82:3:6 -- 欲请求的锁

trx_wait_started: 2015-03-04 10:40:26

trx_weight: 2 -- 大意是该锁影响了2行记录

trx_mysql_thread_id: 657 -- processlist中的线程ID

trx_query: update trx_fee set fee=rand()*1000 where id= 4

trx_operation_state: starting index read

trx_tables_in_use: 1

trx_tables_locked: 1

trx_lock_structs: 2

trx_lock_memory_bytes: 360

trx_rows_locked: 1

trx_rows_modified: 0

trx_concurrency_tickets: 0

trx_isolation_level: REPEATABLE READ

trx_unique_checks: 1

trx_foreign_key_checks: 1

trx_last_foreign_key_error: NULL

trx_adaptive_hash_latched: 0

trx_adaptive_hash_timeout: 10000

trx_is_read_only: 0

trx_autocommit_non_locking: 0

*************************** 2. row ***************************

trx_id: 17773

trx_state: RUNNING

trx_started: 2015-03-04 10:40:23

trx_requested_lock_id: NULL

trx_wait_started: NULL

trx_weight: 10

trx_mysql_thread_id: 656

trx_query: NULL

trx_operation_state: NULL

trx_tables_in_use: 0

trx_tables_locked: 0

trx_lock_structs: 2

trx_lock_memory_bytes: 360

trx_rows_locked: 9

trx_rows_modified: 8

trx_concurrency_tickets: 0

trx_isolation_level: REPEATABLE READ

trx_unique_checks: 1

trx_foreign_key_checks: 1

trx_last_foreign_key_error: NULL

trx_adaptive_hash_latched: 0

trx_adaptive_hash_timeout: 10000

trx_is_read_only: 0

trx_autocommit_non_locking: 0

#再看 INNODB_LOCKS 表，看看都有什么锁

mysql> select * from information_schema.INNODB_LOCKS\G

*************************** 1. row ***************************

lock_id: 17778:82:3:6 --当前锁ID

lock_trx_id: 17778 --该锁对应的事务ID

lock_mode: X -- 锁类型，排它锁X

lock_type: RECORD --锁范围，记录锁：record lock，其他锁范围：间隙锁：gap lock，或者next-key lock(记录锁+间隙锁)

lock_table: `test`.`trx_fee`

lock_index: PRIMARY --加载在哪个索引上的锁

lock_space: 82

lock_page: 3

lock_rec: 6

lock_data: 4

*************************** 2. row ***************************

lock_id: 17773:82:3:6

lock_trx_id: 17773

lock_mode: X

lock_type: RECORD

lock_table: `test`.`trx_fee`

lock_index: PRIMARY

lock_space: 82

lock_page: 3

lock_rec: 6

lock_data: 4

#最后看 INNODB_LOCK_WAITS 表，看看当前都有哪些锁等待

mysql> select * from information_schema.INNODB_LOCK_WAITS\G

*************************** 1. row ***************************

requesting_trx_id: 17778 --请求锁的事务ID(等待方)

requested_lock_id: 17778:82:3:6 -- 请求锁ID

blocking_trx_id: 17773 -- 阻塞该锁的事务ID(当前持有方，待释放)

blocking_lock_id: 17773:82:3:6 -- 持有的锁ID

关于INFORMATION_SCHEMA中和InnoDB有关的表用途描述，可以查看手册：21.29 INFORMATION_SCHEMA Tables for InnoDB

3、利用percona分支的特性，查看当前最新事务ID，该特性从5.6.11-60.3版本开始引入，执行下面的2个命令即可查看

mysqladmin ext | grep Innodb_max_trx_id

或者

mysql> show global status like 'Innodb_max_trx_id';

最后，交代下问题的来源其实是这样的，有位朋友和我讨论问题，说在java连接池中，发现2个事务的事务ID是一样的，测试的SQL代码：

begin;update trx set un=rand() where id=round(rand()*10)+1;select * from information_schema.INNODB_TRX; commit;select sleep(0.01);begin;update trx set un=rand() where id=round(rand()*10)+1;select * from information_schema.INNODB_TRX;commit;

这串代码不能折行，中间的 sleep 停留 不能太大，也就是模拟足够快的情况下，检查2次事务的ID是否有变化。可以发现，时间足够短的话，2次查询到的事务ID是一样的，并没有发生变化。大家也可以在自己的环境下试试。