查看innodb锁冲突

1
2
select * from information_schema.innodb_lock_waits;
select * from information_schema.innodb_locks;

查看innodb事务锁冲突情况

1
select blocking_trx_id, count(blocking_trx_id) as countnum from (select a.trx_id,a.trx_state,b.requesting_trx_id,b.blocking_trx_id from information_schema.innodb_lock_waits as b left join information_schema.innodb_trx as a on a.trx_id=b.requesting_trx_id) as t1 group by blocking_trx_id order by countnum desc;

获取到innodb事务锁冲突的原始id

1
select id from information_schema.processlist,information_schema.innodb_trx where trx_mysql_thread_id=id and trx_id in (select blocking_trx_id from (select blocking_trx_id, count(blocking_trx_id) as countnum from (select a.trx_id,a.trx_state,b.requesting_trx_id,b.blocking_trx_id from information_schema.innodb_lock_waits as b left join information_schema.innodb_trx as a on a.trx_id=b.requesting_trx_id) as t1 group by blocking_trx_id order by countnum desc limit 1) c) ;

获取到innodb事务锁冲突的原始id

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select concat('kill ', id, ';') from information_schema.processlist, information_schema.innodb_trx 

where trx_mysql_thread_id= id and trx_id in( select blocking_trx_id from( 

  select blocking_trx_id, count(blocking_trx_id) as countnum from(

   select a.trx_id, a.trx_state, b.requesting_trx_id, b.blocking_trx_id from   information_schema.innodb_lock_waits as b 

left join information_schema.innodb_trx as a on a.trx_id= b.requesting_trx_id

) as t1 group by blocking_trx_id order by countnum desc limit 1) c) ;
 

--执行kill命令

kill 3220448;

查看是否存在元锁冲突

1
2
3
show processlist;

select id,State,command from information_schema.processlist where State= "Waiting for table metadata lock" ;

查看导致元锁冲突的会话详细

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
select * from information_schema.innodb_trx i,

  (select  id, time

 from information_schema.processlist

 where time = (select 

max(time) from

information_schema.processlist

where

state = 'Waiting for table metadata lock'

and info regexp 'alter|optim|repai|lock|drop|creat')) p

  where timestampdiff(second, i.trx_started, now()) > p.time

  and i.trx_mysql_thread_id  not in (connection_id(),p.id);

查看未提交或回滚的错误事务

1
2
3
4
5
6
7
8
9
10
11
SELECT T.PROCESSLIST_ID,T.PROCESSLIST_TIME,E.SQL_TEXT 

FROM PERFORMANCE_SCHEMA.THREADS T,

PERFORMANCE_SCHEMA.EVENTS_STATEMENTS_CURRENT E 

WHERE T.THREAD_ID=E.THREAD_ID AND E.SQL_TEXT LIKE ‘%T1%’;
 

\# SQL案例中假设是在t1表上有MDL锁，则 e.sql_text 近似匹配 t1
 \# 本方法5.5 5.6 5.7 都通用。