mysql事务阻塞原因探索

– 先保存现场
show engine innodb status;

– 查看是否存在锁等待信息
show status like ‘innodb_row_lock%’;

– 查看锁等待的事务信息
select * from information_schema.INNODB_TRX WHERE trx_state=‘LOCK WAIT’;

– 查看锁等待的事务id，processId, 阻塞它的事务id，阻塞它的processId
select * from sys.innodb_lock_waits;

– 查看阻塞它的processId的信息，可以查找到线程id
select * from performance_schema.threads where processlist_id=8506177;

– 基于线程id可以查询到阻塞sql
select * from performance_schema.events_statements_current where thread_id = 8506218;

– 查看连接的session信息
select * from sys.session ;

select * from information_schema.INNODB_TRX;

show processlist;

– 查询正在running，且sleep的信息，包括历史执行的sql
SELECT
p.ID AS conn_id,
P.USER AS login_user,
P.HOST AS login_host,
p.DB AS database_name,
P.TIME AS trx_sleep_seconds,
TIME_TO_SEC(TIMEDIFF(NOW(),T.trx_started)) AS trx_open_seconds,
T.trx_started,
T.trx_isolation_level,
T.trx_tables_locked,
T.trx_rows_locked,
t.trx_state,
p.COMMAND AS process_state,
(
SELECT GROUP_CONCAT(REPLACE(REPLACE(REPLACE(T1.SQL_TEXT,‘\n’,’ ‘),’\r’,’ ‘),’\t’,’ ') SEPARATOR ';
')
FROM performance_schema.events_statements_history AS T1
INNER JOIN performance_schema.threads AS T2
ON T1.THREAD_ID=T2.THREAD_ID
WHERE T2.PROCESSLIST_ID=P.id
) AS trx_sql_text
FROM information_schema.INNODB_TRX t
INNER JOIN information_schema.PROCESSLIST p
ON t.trx_mysql_thread_id=p.id
WHERE t.trx_state=‘RUNNING’
AND p.COMMAND=‘Sleep’
– AND P.TIME>
ORDER BY T.trx_started ASC ;

– 根据线程id查找历史执行的sql
select * from performance_schema.events_statements_history where thread_id = 8519621;