在mysql 5.7数据库中，同一查询的两个示例从同一代码路径执行，每个示例在各自的事务中，彼此之间存在非确定性死锁。只有在应用程序服务器负载较重的情况下才能观察到该问题。
这些查询是 select '通过主键更新要更新的行 for update . 关于这个主题的各种问题都有答案，包括确保索引不需要扫描额外的行；在我的例子中，我只在要更新的行上持有一个锁。
据我所知，问题是其中一个查询持有共享锁，并希望升级为持有独占锁，但在另一个查询不再持有相同的共享锁之前，它无法拥有该锁。令人沮丧的是：
只有一个代码路径执行此查询
此查询是新事务中的第一条语句
大多数情况下都是这样
有没有策略可以首先避免获得共享锁？这是在升级shared到exclusive lock时避免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)