mysql死锁查询语句

概述

死锁在平时工作中总是很常见，我们又不可能总是实时关注着，那么怎么去把发生死锁的一些情况记录下来呢？

1、配置文件

在MySQL 5.6版本中查看死锁，需要执行show engine innodb status\G;命令。

在MySQL 5.6/5.7或MariaDB 10.0/10.1版本中，在my.cnf配置文件里加入：

innodb_print_all_deadlocks=1

就可以把死锁信息打印到错误日志里。

2、全局参数mysql> show variables like ‘%deadlock%‘;mysql> set global innodb_print_all_deadlocks=1;

3、死锁实验

3.1、准备数据

mysql> create table t1(id int,name varchar(20));mysql> ｉｎｓｅｒｔ into t1 values(1,‘b‘);mysql> ｉｎｓｅｒｔ into t1 values(5,‘c‘);mysql> commit;mysql> ｓｅｌｅｃｔ * from t1;mysql> create index idx_t on t1(id); --后面ｕｐｄａｔｅ如果条件有索引,锁行，如果没有，锁表

3.2、产生死锁

会话1：mysql> ｓｅｌｅｃｔ * from t1;mysql> begin;mysql> ｕｐｄａｔｅ t1 set name=‘b1‘ where id=1;会话2：mysql> ｓｅｌｅｃｔ * from t1;mysql> begin;mysql> ｕｐｄａｔｅ t1 set name=‘c2‘ where id=5;会话1：mysql> ｕｐｄａｔｅ t1 set name=‘c1‘ where id=5; ----等待会话2：mysql> ｕｐｄａｔｅ t1 set name=‘b2‘ where id=1;ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction会话1：mysql> ｕｐｄａｔｅ t1 set name=‘c1‘ where id=5; ------此时可以发现会话1执行成功

3.3、查看错误日志

2019-07-10T15:54:20.474062Z 24 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.2019-07-10T15:54:20.474091Z 24 [Note] InnoDB: *** (1) TRANSACTION:TRANSACTION 35509311, ACTIVE 31 sec starting index readmysql tables in use 1, locked 1LOCK WAIT 5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1MySQL thread id 25, OS thread handle 140127543449344, query id 42548 localhost root updatingupdate t1 set name=‘c1‘ where id=52019-07-10T15:54:20.474138Z 24 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 1562 page no 4 n bits 72 index idx_t of table `jpcpdb`.`t1` trx id 35509311 lock_mode X waitingRecord lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000005; asc ;; 1: len 6; hex 000000001705; asc ;;2019-07-10T15:54:20.474242Z 24 [Note] InnoDB: *** (2) TRANSACTION:TRANSACTION 35509312, ACTIVE 19 sec starting index readmysql tables in use 1, locked 14 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1MySQL thread id 24, OS thread handle 140127543981824, query id 42549 localhost root updatingupdate t1 set name=‘b2‘ where id=12019-07-10T15:54:20.474268Z 24 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):RECORD LOCKS space id 1562 page no 4 n bits 72 index idx_t of table `jpcpdb`.`t1` trx id 35509312 lock_mode XRecord lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;;Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000005; asc ;; 1: len 6; hex 000000001705; asc ;;2019-07-10T15:54:20.474536Z 24 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 1562 page no 4 n bits 72 index idx_t of table `jpcpdb`.`t1` trx id 35509312 lock_mode X waitingRecord lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 6; hex 000000001704; asc ;;2019-07-10T15:54:20.474604Z 24 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2)

后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注一下~