mysql 观察锁信息_MySQL查看事务锁信息

MySQL的information_schema库下有四张(实际上是系统视图)：INNODB_LOCKS、INNODB_TRX、INNODB_LOCK_WAITS、PROCESSLIST记录了当前在mysql上的事务，线程，锁的信息。通过下面这条sql语句，可以查询锁与被锁的事务或线程的信息，语句如下：

use information_schema;

select a.trx_mysql_thread_id RL_thread_id, c.trx_mysql_thread_id HL_thread_id,

concat(d.user,'@',d.host,'::',d.db) RL_user_info, concat(e.user,'@',e.host,'::',e.db) HL_user_info,

'============' '============',

a.trx_id RL_trx_id, c.trx_id HL_trx_id,

a.trx_state RL_trx_state, c.trx_state RH_trx_state,

a.trx_operation_state RL_trx_operation_status, c.trx_operation_state HL_trx_operation_status,

a.trx_weight RL_trx_weight, c.trx_weight HL_trx_weight,

a.trx_started RL_trx_started, c.trx_wait_started RH_trx_wait_started,

a.trx_query RL_trx_query, c.trx_query HL_trx_query,

'============' '============',

f.lock_mode RL_lock_mode, g.lock_mode HL_lock_mode,

f.lock_type RL_lock_type, g.lock_type HL_lock_type,

f.lock_index RL_lock_index, g.lock_index HL_lock_index

from innodb_trx a, innodb_lock_waits b, innodb_trx c, processlist d, processlist e, innodb_locks f, innodb_locks g

where a.trx_id=b.requesting_trx_id

and c.trx_id=b.blocking_trx_id

and a.trx_mysql_thread_id=d.id

and c.trx_mysql_thread_id=e.id

and b.requested_lock_id=f.lock_id

and b.blocking_lock_id=g.lock_id \G