如何查看mysql中表的锁定情况

dian010418

show engine innodb status可以看到锁信息，但是比较难以读懂，也可以使用innotop工具，它会把show engine innodb status的输出信息图形化展示。

如果是5.5以后的版本，更方便的是查询information_schema，可以用这 2 个脚本：

select count(*) as num_waiting_thread,
       max(timestampdiff(second, x2.trx_wait_started, now())) as max_wait_time,
       x1.trx_mysql_thread_id as blocking_thread,
       p.user as bt_user,
       p.host as bt_host,
       p.command as bt_command,
       p.time as bt_time,
       p.state as bt_state,
       x1.trx_query as bt_query
from information_schema.innodb_lock_waits as w
inner join information_schema.innodb_trx as x1 on x1.trx_id = w.blocking_trx_id
inner join information_schema.innodb_trx as x2 on x2.trx_id = w.requesting_trx_id
left join information_schema.processlist as p on p.id = x1.trx_mysql_thread_id
group by blocking_thread
order by num_waiting_thread desc;



select timestampdiff(second, x2.trx_wait_started, current_timestamp) as wait_time,
       x2.trx_mysql_thread_id as waiting_thread,
       l.lock_table as wt_request_lock,
       x2.trx_query as wt_query,
       x1.trx_mysql_thread_id as blocking_thread,
       p.user as bt_user,
       p.host as bt_host,
       p.command as bt_command,
       p.time as bt_time,
       p.state as bt_state,
       x1.trx_query as bt_query
from information_schema.innodb_lock_waits as w
inner join information_schema.innodb_trx as x1 on x1.trx_id = w.blocking_trx_id
inner join information_schema.innodb_trx as x2 on x2.trx_id = w.requesting_trx_id
inner join information_schema.innodb_locks as l on w.requested_lock_id = l.lock_id
left join information_schema.processlist as p on p.id = x1.trx_mysql_thread_id
order by wait_time desc;