mysql left join 锁表_MySQL锁表相关问题查找思路

默认使用INNODB存储引擎，查询当前是否有锁表情况

select * from information_schema.INNODB_LOCK_WAITS;

select * from information_schema.INNODB_LOCKS;

查看事务等待状况

SELECT

r.trx_id waiting_trx_id,

r.trx_mysql_thread_id waiting_thread,

r.trx_query waiting_query,

b.trx_id blocking_trx_id,

b.trx_mysql_thread_id blocking_thread,

b.trx_query blocking_query

FROM

information_schema.innodb_lock_waits w

INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id

INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

查看更具体的事务等待状况

SELECT

b.trx_state,

e.state,

e.time,

d.state AS block_state,

d.time AS block_time,

a.requesting_trx_id,

a.requested_lock_id,

b.trx_query,

b.trx_mysql_thread_id,

a.blocking_trx_id,

a.blocking_lock_id,

c.trx_query AS block_trx_query,

c.trx_mysql_thread_id AS block_trx_mysql_tread_id

FROM

information_schema.INNODB_LOCK_WAITS a

LEFT JOIN information_schema.INNODB_TRX b ON a.requesting_trx_id = b.trx_id

LEFT JOIN information_schema.INNODB_TRX c ON a.blocking_trx_id = c.trx_id

LEFT JOIN information_schema.PROCESSLIST d ON c.trx_mysql_thread_id = d.id

LEFT JOIN information_schema.PROCESSLIST e ON b.trx_mysql_thread_id = e.id

ORDER BY

a.requesting_trx_id;

开启 general log 查询锁表事务内语句

SHOW VARIABLES LIKE 'general_log%';

set GLOBAL general_log = on; #开启

set GLOBAL general_log = off; #关闭

set global log_output='table'; #设置输出方式为表

-- 根据上述查询出来的thread_id

SELECT * from mysql.general_log WHERE thread_id = 441;

注意：general log会记录所有的sql语句，调试过程中，找到自己需要的sql后及时关闭，否则会造出空间不足。

查看未关闭的事务(

MySQL 5.6及以上)

SELECT

a.trx_id,

a.trx_state,

a.trx_started,

a.trx_query,

b.ID,

b.USER,

b.DB,

b.COMMAND,

b.TIME,

b.STATE,

b.INFO,

c.PROCESSLIST_USER,

c.PROCESSLIST_HOST,

c.PROCESSLIST_DB,

d.SQL_TEXT

FROM

information_schema.INNODB_TRX a

LEFT JOIN information_schema.PROCESSLIST b ON a.trx_mysql_thread_id = b.id

AND b.COMMAND = 'Sleep'

LEFT JOIN PERFORMANCE_SCHEMA.threads c ON b.id = c.PROCESSLIST_ID

LEFT JOIN PERFORMANCE_SCHEMA.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;