概述
死锁在平时工作中总是很常见,我们又不可能总是实时关注着,那么怎么去把发生死锁的一些情况记录下来呢?
1、配置文件
在MySQL 5.6版本中查看死锁,需要执行show engine innodb statusG;命令。
在MySQL 5.6/5.7或MariaDB 10.0/10.1版本中,在my.cnf配置文件里加入:
innodb_print_all_deadlocks=1
就可以把死锁信息打印到错误日志里。
![3fddc3409ac34dd8c327b79aac4ffdc6.png](https://i-blog.csdnimg.cn/blog_migrate/9f7e239cac2f2873b579f8afe4a52faa.jpeg)
2、全局参数
mysql> show variables like '%deadlock%';mysql> set global innodb_print_all_deadlocks=1;
![d770a3bcfca4901a01de050ca6539eee.png](https://i-blog.csdnimg.cn/blog_migrate/0a53857174299787874c82f3409d941b.jpeg)
3、死锁实验
3.1、准备数据
mysql> create table t1(id int,name varchar(20));mysql> insert into t1 values(1,'b');mysql> insert into t1 values(5,'c');mysql> commit;mysql> select * from t1;mysql> create index idx_t on t1(id); --后面update如果条件有索引,锁行,如果没有,锁表
![97c3542a10b2a6ab3ef1403eaa10dcf1.png](https://i-blog.csdnimg.cn/blog_migrate/727a854a793b7b25792634892434b875.jpeg)
3.2、产生死锁
会话1:mysql> select * from t1;mysql> begin;mysql> update t1 set name='b1' where id=1;会话2:mysql> select * from t1;mysql> begin;mysql> update t1 set name='c2' where id=5;会话1:mysql> update t1 set name='c1' where id=5; ----等待会话2:mysql> update t1 set name='b2' where id=1;ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction会话1:mysql> update t1 set name='c1' where id=5; ------此时可以发现会话1执行成功
![435086001c09091bf76ee23e8a83df77.png](https://i-blog.csdnimg.cn/blog_migrate/00a5ac535298c4639dd3d775fd143b83.jpeg)
![47162d0d6a98b2c07ad3552c8a9d2740.png](https://i-blog.csdnimg.cn/blog_migrate/0d2d8806ead490d4ddc672d866da940d.jpeg)
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)
![078748e792178fd61bc8af802983d452.png](https://i-blog.csdnimg.cn/blog_migrate/3c0ded504d6ede73d1b2abe63fea596c.jpeg)
后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注一下~
![2adc755e9749e91d91650ae9a194afc0.gif](https://i-blog.csdnimg.cn/blog_migrate/e63e6e7d60a68cc8314a889f46db2c4c.gif)