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;
转载于:https://blog.csdn.net/lvyanqin2013/article/details/128253570