How to query the latest transaction ID in MySQL

Written in front: Sometimes you may need to view the latest transaction ID in order to make some business logic judgments (for example, using the transaction ID changes and the time difference before and after to 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 -- Current maximum 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 -- Executing SHOW ENGINE INNODB STATUS in this session does not generate a transaction, 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 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 and not yet completed, please pay attention, it may cause a large number of lock waits 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 the three tables INFORMATION_SCHEMA.INNODB_TRX, INNODB_LOCKS, and INNODB_LOCK_WAITS. Through this information, you can quickly find which transactions are blocking other transactions.

First query the INNODB_TRX table to see what transactions there are

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX\G
*************************** 1. row ***************************
 trx_id: 17778 -- Current transaction ID
 trx_state: LOCK WAIT -- in 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 -- This means that the lock affects 2 rows of records trx_mysql_thread_id: 657 -- The 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 to see what locks are there.

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 ranges: gap lock: gap lock, or next-key lock (record lock + gap lock)
lock_table: `test`.`trx_fee`
lock_index: PRIMARY -- which index is the lock loaded on 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 -- transaction ID requesting the lock (waiting party)
requested_lock_id: 17778:82:3:6 -- requested lock ID
blocking_trx_id: 17773 -- The transaction ID that is blocking the lock (current holder, waiting to be released)
blocking_lock_id: 17773:82:3:6 -- ID of the lock being held

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

3. Use the features of the percona branch to view the current latest transaction ID. This feature has been introduced since version 5.6.11-60.3. Execute the following two commands to view it

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

Finally, let me explain the source of the problem. A friend discussed the problem with me and said that in the Java connection pool, he found that the transaction IDs of two transactions were the same. The SQL code for the test was:

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 code cannot be wrapped, and the sleep period in the middle cannot be too long. That is, when the simulation is fast enough, check whether the IDs of the two transactions have changed. It can be found that if the time is short enough, the transaction IDs queried twice are the same and have not changed. You can also try it in your own environment.

The above is the details of how to query the current latest transaction ID in MySQL. For more information about MySQL query transaction ID, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:

How does MySQL implement ACID transactions?
Why MySQL should avoid large transactions and how to solve them
Analysis of the new features of MySQL 8.0 - transactional data dictionary and atomic DDL
Detailed explanation of the implementation principle of MySQL transactions and Spring isolation levels
MySQL transaction concepts and usage in-depth explanation
Example of viewing and modifying MySQL transaction isolation level
Mysql database advanced usage of views, transactions, indexes, self-connections, user management example analysis
Summary of MySql index, lock, and transaction knowledge points
MySql knowledge points: transaction, index, lock principle and usage analysis
How MySQL uses transactions

<<: Detailed explanation of the whole process and steps of installing clion on Ubuntu16.04

>>: VMware virtual machine to establish HTTP service steps analysis