MySQL InnoDB Deadlock на двух экземплярах одного запроса с первичным ключом
Два экземпляра одного и того же запроса, выполняемые по одному и тому же пути кода, каждый в своей собственной транзакции, недетерминированы взаимоблокировками друг с другом в моей базе данных MySQL 5.7. Эта проблема наблюдается только тогда, когда серверы приложений находятся под большой нагрузкой.
Запросы selectв строке для обновления по первичному ключу for update, На различные вопросы SO по этой теме есть ответы, которые включают в себя проверку того, что индексы таковы, что не нужно сканировать лишние строки; в моем случае я начинаю с удержания блокировки на обновляемой строке.
Насколько я могу судить, проблема в том, что один из запросов удерживает общую блокировку и хочет перерасти в удержание монопольной блокировки, но он не может иметь ее, пока другой запрос больше не удерживает ту же общую блокировку. Расстраивающие вещи таковы:
- Существует только один путь кода, который выполняет этот запрос
- Этот запрос является первым оператором внутри новой транзакции
- Большую часть времени это работает
Существуют ли стратегии, позволяющие избежать получения общей блокировки? Является ли это еще одним проявлением печально известной ошибки MySQL / InnoDB, обсуждаемой в статье "Избегание тупиковой ситуации MySQL при обновлении разделяемой до эксклюзивной блокировки"?
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)