mysql+查询被锁住_Mysql被锁查询 - hebad的个人页面 - OSCHINA - 中文开源技术交流社区...

先查询 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