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.