mysql怎么防止死锁

144次阅读

共计 3435 个字符，预计需要花费 9 分钟才能阅读完成。

这篇文章主要介绍“mysql 怎么防止死锁”，在日常操作中，相信很多人在 mysql 怎么防止死锁问题上存在疑惑，丸趣 TV 小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”mysql 怎么防止死锁”的疑惑有所帮助！接下来，请跟着丸趣 TV 小编一起来学习吧！

死锁是事务绕不开的话题，mysql 当然也不例外，本文主要模拟一下 mysql 的死锁，以及应对措施。
首先看一个参数，默认 innodb_print_all_deadlocks 参数是关闭。开启后可以将死锁记录到 error.log 中。否则只能通过 show engine innodb status 查看。
mysql SHOW VARIABLES LIKE INNODB_PRINT_ALL_DEADLOCKS
+—————————-+——-+
| Variable_name | Value |
+—————————-+——-+
| innodb_print_all_deadlocks | OFF |
+—————————-+——-+
1 row in set (0.00 sec)

开启 innodb_print_all_deadlocks, 改参数是全局参数，可以动态调整。
mysql SET GLOBAL innodb_print_all_deadlocks=1;
Query OK, 0 rows affected (0.00 sec)

表 test01 上 c1 是主键，c2 是唯一约束。
mysql show create table test01\G
*************************** 1. row ***************************
Table: test01
Create Table: CREATE TABLE `test01` (
`c1` bigint(20) NOT NULL AUTO_INCREMENT,
`c2` int(11) DEFAULT NULL,
PRIMARY KEY (`c1`),
UNIQUE KEY `uidx_test01_c2` (`c2`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql select * from test01;
+—-+——+
| c1 | c2 |
+—-+——+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+—-+——+
3 rows in set (0.00 sec)
会话 A
mysql begin ;
Query OK, 0 rows affected (0.00 sec)

mysql delete from test01 where c2=2;
Query OK, 1 row affected (0.00 sec)

会话 B
mysql begin;
Query OK, 0 rows affected (0.00 sec)

mysql delete from test01 where c2=2;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

会话 A
mysql insert into test01 select 2,2;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
会话 A 在（2,2）上加了 X 的行锁，会话 B 要删除相同的数据行，那么也要在该行上加 X 的行锁（lock_mode X locks rec but not gap），所以出现了等待（lock_mode X waiting）。后面会话 A 要插入一行（2,2），因为字段 c2 上有唯一索引，插入的时候要检查 duplicate key 的检查，这个过程需要申请 S 的锁，而在得到这个锁之前，它需要等会话 B 先得到会话 A 最开始执行的 X 锁。也就是说，会话 B 要等待会话 A 第一条语句释放 X 锁，会话 A 第二条语句又要等待会话 B 释放 X 锁，两个会话之间形成了等待的闭合回路，形成了死锁。出现死锁后，mysql 会选择一个小事务进行回滚，以解决死锁。
show engine innodb status 查看死锁信息，error.log 中记录的死锁也类似下面：
————————
LATEST DETECTED DEADLOCK
————————
2019-07-18 11:11:32 0x7fdc50298700
*** (1) TRANSACTION:
TRANSACTION 713521, ACTIVE 122 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 4, OS thread handle 140584214165248, query id 144 localhost root updating
delete from test01 where c2=2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 25 page no 4 n bits 72 index uidx_test01_c2 of table `ming`.`test01` trx id 713521 lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 80000002; asc ;;
1: len 8; hex 8000000000000002; asc ;;

*** (2) TRANSACTION:
TRANSACTION 713523, ACTIVE 16 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2
MySQL thread id 3, OS thread handle 140584214431488, query id 146 localhost root executing
insert into test01 select 2,2
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 25 page no 4 n bits 72 index uidx_test01_c2 of table `ming`.`test01` trx id 713523 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 80000002; asc ;;
1: len 8; hex 8000000000000002; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 25 page no 4 n bits 72 index uidx_test01_c2 of table `ming`.`test01` trx id 713523 lock mode S waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 80000002; asc ;;
1: len 8; hex 8000000000000002; asc ;;

针对死锁的举措：
1. 如果死锁较多的话，那么建议开启 innodb_print_all_deadlocks, 因为 show engine innodb status 只显示最近一次的死锁信息。
2. 保持事务小而短，并尽快提价
3. 避免在一个事务里面修改几张表，或者是同一张表修改不同的结果集
4. 可以尝试使用较低的隔离级别，比如 RC。也可以使用锁定读
5. 建立合适的索引
6. 如果应用允许的话，串行化事务

到此，关于“mysql 怎么防止死锁”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注丸趣 TV 网站，丸趣 TV 小编会继续努力为大家带来更多实用的文章！

正文完