Mysql: 一个死锁场景的解决
update对资源抢占的死锁
业务场景
文件系统里面修改文件的时候，会对上级文件的修改时间进行修改。由于会同时修改多个文件，所以，将他们的父目录收集起来放在set里面。最后执行update。注意，所有的操作都在一个事务里面。
问题原因
最后对修改时间的update操作是无序的（set），而我们在使用update语句去更新的时候会锁定一行数据，如果事务1锁定了路径A的文件，然后去写路径B的文件，而事务2锁定了B，等待写入路径A就形成了死锁。
复现
CREATE TABLE IF NOT EXISTS `file`
(
`id` int(11) NOT NULL AUTO_INCREMENT,
`file_parent` varchar(128) NOT NULL,
`file_name` varchar(6) DEFAULT NULL,
`create_time` datetime(6) NOT NULL,
`modify_time` datetime(6) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `index_path` (`file_parent`, `file_name`)
) ENGINE = InnoDB
AUTO_INCREMENT = 5
DEFAULT CHARSET = utf8mb4;
INSERT INTO cache.file
(file_parent, file_name, create_time, modify_time)
VALUES('/', 'root', now(), now());
INSERT INTO cache.file
(file_parent, file_name, create_time, modify_time)
VALUES('/root', 'sub', now(), now());
事务1：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update file set modify_time = now() where file_parent = '/';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update file set modify_tiime = now() where file_parent = '/root';
ERROR 1054 (42S22): Unknown column 'modify_tiime' in 'field list'
mysql> update file set modify_time = now() where file_parent = '/root';
Query OK, 1 row affected (25.33 sec)
Rows matched: 1 Changed: 1 Warnings: 0
事务2：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update file set modify_time = now() where file_parent = '/root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update file set modify_time = now() where file_parent ='/';
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
mysql>
复现的时候需要把死锁的记录打开：
mysql> show variables like "%innodb_print_all_deadlocks%";
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_print_all_deadlocks | OFF |
+----------------------------+-------+
1 row in set (0.01 sec)
mysql> set global innodb_print_all_deadlocks=1
->
-> ;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like "%innodb_print_all_deadlocks%";
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_print_all_deadlocks | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
数据库日志：
------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-12-27 12:25:21 0x7f9ff84fa700
*** (1) TRANSACTION:
TRANSACTION 64536, ACTIVE 1164 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 9, OS thread handle 140325444028160, query id 79 localhost root updating
update file set modify_time = now() where file_parent = '/root'
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 47 page no 5 n bits 312 index index_path of table `cache`.`file` trx id 64536 lock_mode X
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 1; hex 2f; asc /;;
1: len 4; hex 726f6f74; asc root;;
2: len 4; hex 80000008; asc ;;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 47 page no 5 n bits 312 index index_path of table `cache`.`file` trx id 64536 lock_mode X waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 5; hex 2f726f6f74; asc /root;;
1: len 3; hex 737562; asc sub;;
2: len 4; hex 80000007; asc ;;
*** (2) TRANSACTION:
TRANSACTION 64537, ACTIVE 14 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 13, OS thread handle 140325442389760, query id 80 localhost root updating
update file set modify_time = now() where file_parent ='/'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 47 page no 5 n bits 312 index index_path of table `cache`.`file` trx id 64537 lock_mode X
Record 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 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 5; hex 2f726f6f74; asc /root;;
1: len 3; hex 737562; asc sub;;
2: len 4; hex 80000007; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 47 page no 5 n bits 312 index index_path of table `cache`.`file` trx id 64537 lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 1; hex 2f; asc /;;
1: len 4; hex 726f6f74; asc root;;
2: len 4; hex 80000008; asc ;;
*** WE ROLL BACK TRANSACTION (2)
第一个事务持有锁第二个事务持有的锁冲突了。互相等待造成死锁。这是一个典型的资源无序操作造成的死锁。
如何解决
将无序的操作改为有序即可。
