mysql8的表锁排查

information_schema.innodb_trx ##正在运行的事务信息。

sys.innodb_lock_waits ##处于锁等待的关联事务信息。

performance_schema.threads ##SQL线程及线程号、进程号、OS线程号等信息

# 查询锁的情况

select * from performance_schema.data_locks where object_name ='t_xxx';

SELECT straight_join

dl.THREAD_ID

, est.SQL_TEXT

, dl.OBJECT_SCHEMA

, dl.OBJECT_NAME

, dl.INDEX_NAME

, dl.LOCK_TYPE

, dl.LOCK_MODE

, dl.LOCK_STATUS

, dl.LOCK_DATA

FROM

performance_schema.data_locks dl inner join performance_schema.events_statements_current est on dl.THREAD_ID = est.THREAD_ID

ORDER BY est.TIMER_START,dl.OBJECT_INSTANCE_BEGIN;

# 确认有没有锁等待:

show status like 'innodb_row_lock%&