mysql - 具有主键的同一查询的两个实例上的 MySQL InnoDB 死锁

Question

在我的 MySQL 5.7 数据库中，从同一代码路径执行的同一查询的两个实例，每个实例都在自己的事务中，彼此不确定地死锁。该问题仅在应用程序服务器负载过重时才会出现。

查询正在select通过其主键更新要更新的行for update。关于这个主题的各种SO问题都有答案，包括确保索引不需要扫描额外的行；就我而言，我从只锁定要更新的行开始。

据我所知，问题在于其中一个查询持有共享锁并希望升级为持有独占锁，但在另一个查询不再持有相同的共享锁之前它不能拥有它。令人沮丧的是：

1. 只有一个执行此查询的代码路径
2. 此查询是新事务中的第一条语句
3. 大多数时候它工作

是否有避免一开始就获得共享锁的策略？这是在将共享锁升级为独占锁时避免 MySQL 死锁中讨论的臭名昭著的MySQL/InnoDB 错误的另一种表现吗？

2018-08-27T15:15:09.933324Z 706140 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.
2018-08-27T15:15:09.933349Z 706140 [Note] InnoDB: 
*** (1) TRANSACTION:

TRANSACTION 247857773771, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 9 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 705535, OS thread handle 47877150750464, query id 28046975289 ip-1-2-3-4.ec2.internal 1.2.3.4 db statistics
SELECT `users`.* FROM `users` WHERE `users`.`id` = 1234 LIMIT 1 FOR UPDATE
2018-08-27T15:15:09.933385Z 706140 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 13710 page no 446120 n bits 240 index PRIMARY of table `db`.`users` trx id 247857773771 lock_mode X locks rec but not gap waiting
Record lock, heap no 168 PHYSICAL RECORD: n_fields 16; compact format; info bits 0
0: len 8; hex 800000002a4fe9f3; asc *O ;;
1: len 6; hex 0039b24546b7; asc 9 EF ;;
2: len 7; hex 44001901341059; asc D 4 Y;;
3: len 8; hex 3336303938313532; asc 36098152;;
4: len 8; hex 8000000000000011; asc ;;
5: len 5; hex 999c84079b; asc ;;
6: len 5; hex 99a0b4e144; asc D;;
7: len 3; hex 8f6876; asc hv;;
8: len 1; hex 46; asc F;;
9: len 27; hex 3133343431363a31332c3133333230353a312c3133343531363a31; asc 134416:13,133205:1,134516:1;;
10: len 19; hex 3133343431363a313a323031382d30382d3236; asc 134416:1:2018-08-26;;
11: len 5; hex 99a098e2a7; asc ;;
12: len 1; hex 80; asc ;;
13: len 1; hex 80; asc ;;
14: SQL NULL;
15: len 30; hex 3133343431367c323031382d30382d32362031303a30343a3036202d3034; asc 134416|2018-08-26 10:04:06 -04; (total 131 bytes);

2018-08-27T15:15:09.933943Z 706140 [Note] InnoDB: *** (2) TRANSACTION:

TRANSACTION 247857773774, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
9 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 706140, OS thread handle 47853941884672, query id 28046975369 ip-10-20-30-40.ec2.internal 10.20.30.40 db statistics
SELECT `users`.* FROM `users` WHERE `users`.`id` = 1234 LIMIT 1 FOR UPDATE
2018-08-27T15:15:09.933983Z 706140 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 13710 page no 446120 n bits 240 index PRIMARY of table `db`.`users` trx id 247857773774 lock mode S locks rec but not gap
Record lock, heap no 168 PHYSICAL RECORD: n_fields 16; compact format; info bits 0
0: len 8; hex 800000002a4fe9f3; asc *O ;;
1: len 6; hex 0039b24546b7; asc 9 EF ;;
2: len 7; hex 44001901341059; asc D 4 Y;;
3: len 8; hex 3336303938313532; asc 36098152;;
4: len 8; hex 8000000000000011; asc ;;
5: len 5; hex 999c84079b; asc ;;
6: len 5; hex 99a0b4e144; asc D;;
7: len 3; hex 8f6876; asc hv;;
8: len 1; hex 46; asc F;;
9: len 27; hex 3133343431363a31332c3133333230353a312c3133343531363a31; asc 134416:13,133205:1,134516:1;;
10: len 19; hex 3133343431363a313a323031382d30382d3236; asc 134416:1:2018-08-26;;
11: len 5; hex 99a098e2a7; asc ;;
12: len 1; hex 80; asc ;;
13: len 1; hex 80; asc ;;
14: SQL NULL;
15: len 30; hex 3133343431367c323031382d30382d32362031303a30343a3036202d3034; asc 134416|2018-08-26 10:04:06 -04; (total 131 bytes);

2018-08-27T15:15:09.934539Z 706140 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 13710 page no 446120 n bits 240 index PRIMARY of table `db`.`users` trx id 247857773774 lock_mode X locks rec but not gap waiting
Record lock, heap no 168 PHYSICAL RECORD: n_fields 16; compact format; info bits 0
0: len 8; hex 800000002a4fe9f3; asc *O ;;
1: len 6; hex 0039b24546b7; asc 9 EF ;;
2: len 7; hex 44001901341059; asc D 4 Y;;
3: len 8; hex 3336303938313532; asc 36098152;;
4: len 8; hex 8000000000000011; asc ;;
5: len 5; hex 999c84079b; asc ;;
6: len 5; hex 99a0b4e144; asc D;;
7: len 3; hex 8f6876; asc hv;;
8: len 1; hex 46; asc F;;
9: len 27; hex 3133343431363a31332c3133333230353a312c3133343531363a31; asc 134416:13,133205:1,134516:1;;
10: len 19; hex 3133343431363a313a323031382d30382d3236; asc 134416:1:2018-08-26;;
11: len 5; hex 99a098e2a7; asc ;;
12: len 1; hex 80; asc ;;
13: len 1; hex 80; asc ;;
14: SQL NULL;
15: len 30; hex 3133343431367c323031382d30382d32362031303a30343a3036202d3034; asc 134416|2018-08-26 10:04:06 -04; (total 131 bytes);

2018-08-27T15:15:09.935108Z 706140 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2)

标签： mysqlinnodbdeadlockamazon-rdsmysql-5.7