How to view the latest transaction ID in msyql

You may need to review the current transaction ID to make some business logic decisions (such as using transaction ID changes and time difference, and counting the response duration for each transaction).

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

1. Perform show ENGINE INNODB STATUS to view transaction related information

=====================================
150303 17:16:11 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated to the last seconds
...
------------
Transactions
Trx ID Counter 3359877657--current maximum transaction ID
Purge 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--performs show ENGINE INNODB STATUS in this session, does not generate transactions, so the transaction ID is 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--inactive transactions, 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.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-Active long transactions, running 1358 seconds has not yet ended, to be noticed, may cause a large number of lock waiting 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.x.x.x yejr query End
Insert INTO T_live_room ...



2, view information_schema. Innodb_trx, Innodb_locks, innodb_lock_waits three tables, which quickly discovers which transactions are blocking other transactions

#先查询 Innodb_trx table to see what's in it

Mysql> SELECT * from INFORMATION_SCHEMA. Innodb_trx\g
1. Row ***************************
trx_id:17778--Current transaction ID
Trx_state:lock wait--in the lock wait state, which is waiting for other sessions to release the lock resource
trx_started:2015-03-04 10:40:26
Trx_requested_lock_id:17778:82:3:6--The lock you want to request
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--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



#再看 innodb_locks the table and see what locks there are.

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 for this lock
LOCK_MODE:X--lock type, exclusive lock X
Lock_type:record-lock range, record lock: Records lock, other lock range: Gap Lock: Gap locks, or Next-key lock (record lock + clearance Lock)
Lock_table: ' Test '. ' Trx_fee '
Lock_index:primary-the lock on which index is loaded
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 table to see what locks are currently waiting

Mysql> SELECT * from INFORMATION_SCHEMA. Innodb_lock_waits\g
1. Row ***************************
requesting_trx_id:17778--The transaction ID of the request lock (Waiting Party)
Requested_lock_id:17778:82:3:6--Request Lock ID
blocking_trx_id:17773--The transaction ID that blocked the lock (currently in a current, pending release)
Blocking_lock_id:17773:82:3:6--Holding the lock ID


3, take advantage of the characteristics of the Percona branch, view the current latest transaction ID, the feature is introduced from the 5.6.11-60.3 version, and the following 2 commands are available to view

Mysqladmin Ext | grep innodb_max_trx_id
Or
Mysql> show global status like ' innodb_max_trx_id ';

Finally, the source of the problem is this, a friend and I discussed the problem, said in the Java connection pool, found 2 transactions of the transaction ID is the same, the test of the SQL code:

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 can not be folded, the middle of the sleep stop can not be too large, that is, simulation fast enough, check the ID of the transaction 2 times whether there is a change. Can be found that time is short enough, 2 queries to the transaction ID is the same, and did not change. You can also try it in your own environment.