原文:http://imysql.com/2015/03/25/mysql-faq-how-to-fetch-latest-trxid.shtml
- 执行 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 ...
- 查看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
1): kill trx_mysql_thread_id; 2): use information_schema; select * from processlist where id in ( trx_mysql_thread_id ); 3): show engine innodb status\G
- 再看 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
- 先查询 INNODB_TRX 表,看看都有哪些事务