MySQL FAQ series: How to view the current latest transaction ID

MySQL FAQ series: How to view the current latest transaction ID

Written in the front: In some cases, you may need to check the current latest transaction ID in order to make some business logic judgments (for example, use the transaction ID change and the time difference before and after, and count the response time of each transaction, etc.).

Generally, there are two ways to view the current transaction ID:

1. Execute SHOW ENGINE INNODB STATUS to view transaction related information

====================================
150303 17:16:11 INNODB MONITOR OUTPUT
====================================
Per second averages calculated from the last 15 seconds
...
------------
TRANSACTIONS
Trx id counter 3359877657 - The current largest transaction 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 is executed in this session, no transaction will occur, so the transaction ID is 0
MySQL thread id 4692367, OS thread handle 0x51103940, query id 677284426 xx.173ops.com 10.xxx yejr init
SHOW/*! 50000 ENGINE*/INNODB STATUS
---TRANSACTION 3359877640, not started --Inactive transaction, not yet started
mysql tables in use 1, locked 0
MySQL thread id 4678384, OS thread handle 0x41a57940, query id 677284427 xx.173ops.com 10.xxx 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.xxx yejr cleaning up

---TRANSACTION 3359877635, ACTIVE 1358 sec, thread declared inside InnoDB 5000 - Active long transaction, running for 1358 seconds has not ended, it should be noted that it may cause a large number of lock waits to occur
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.xxx yejr query end
insert into t_live_room ...

2. Check INFORMATION_SCHEMA . INNODB_TRX , INNODB_LOCKS , INNODB_LOCK_WAITS three tables, through these information can quickly find out which transactions are blocking other transactions

First query the INNODB_TRX table to see what transactions are there

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX\G
*************************** 1. row ******************** *******
 trx_id: 17778 - current transaction ID
 trx_state: LOCK WAIT - in a lock waiting state, that is, waiting for other sessions to release lock resources
 trx_started: 2015-03-04 10:40:26
 trx_requested_lock_id: 17778:82:3:6 - the lock to be requested
 trx_wait_started: 2015-03-04 10:40:26
 trx_weight: 2-to the effect that the lock affects 2 rows of records
 trx_mysql_thread_id: 657 - thread ID in processlist
 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

Look at the INNODB_LOCKS table again to see what locks are available

mysql> select * from information_schema.INNODB_LOCKS\G
*************************** 1. row ******************** *******
lock_id: 17778:82:3:6 --Current lock ID
lock_trx_id: 17778 --The transaction ID corresponding to the lock
lock_mode: X - lock type, exclusive lock X
lock_type: RECORD-lock range, record lock: record lock, other lock range: gap lock: gap lock, or next-key lock (record lock + gap lock)
lock_table: `test`.`trx_fee`
lock_index: PRIMARY - load the lock on which index
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

Finally, look at the INNODB_LOCK_WAITS table to see which locks are currently waiting

mysql> select * from information_schema.INNODB_LOCK_WAITS\G
*************************** 1. row ******************** *******
requesting_trx_id: 17778 --The transaction ID (waiting party) that requested the lock
requested_lock_id: 17778:82:3:6 - requested lock ID
blocking_trx_id: 17773 - The transaction ID that blocked the lock (current holder, to be released)
blocking_lock_id: 17773:82:3:6 - the ID of the lock held

For the description of the usage of the tables related to InnoDB in INFORMATION_SCHEMA, please refer to the manual: 21.29 INFORMATION_SCHEMA Tables for InnoDB

3. Use the feature of percona branch to view the current latest transaction ID. This feature has been introduced since version 5.6.11-60.3, and you can view it by executing the following two commands

mysqladmin ext | grep Innodb_max_trx_id
or
mysql> show global status like'Innodb_max_trx_id';

Finally, explain the source of the problem is actually like this. A friend discussed the problem with me and said that in the java connection pool, the transaction ID of two transactions was found to be the same. The SQL code tested:

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;

This string of codes cannot be folded, and the sleep in the middle cannot be too large, that is, if the simulation is fast enough, check whether the ID of the second transaction has changed. It can be found that if the time is short enough, the transaction IDs in the two queries are the same, and there is no change. You can also try it in your own environment.

Reference: https://cloud.tencent.com/developer/article/1005041 MySQL FAQ series: How to check the current latest transaction ID-Cloud + Community-Tencent Cloud