Случай несколько специфичен. Имеется баннерная система, выдает баннеры, например по 2 шт, при выдаче увеличивает счетчики показов для каждого баннера. Не забывает перемешивать баннеры shuffle-ом перед выдачей (делает это ДО подсчета показов).
При мало-мальски большой нагрузке (10rps) уже проскакивали дедлоки, пара штук в час примерно.
Делаем запрос show engine innodb status; смотрим секцию "LATEST DETECTED DEADLOCK"
Видим там две поссорившиеся транзакции:
*** (1) TRANSACTION:в итоге вторую mysql откатил.
TRANSACTION 26A4ED2EF, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 5361085, OS thread handle 0x7f6f31c76700, query id 71605993 blabla
INSERT INTO stat (`banner_id`,`dt`,`views`) VALUES (1283, CURDATE(), 1), (1282, CURDATE(), 1) ON DUPLICATE KEY UPDATE views = views + 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1079 page no 358 n bits 376 index `main_key` of table blabla trx id 26A4ED2EF lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 26A4ED2F0, ACTIVE 0 sec inserting, thread declared inside InnoDB 497
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 5360789, OS thread handle 0x7f6f0fc30700, query id 71605995 blabla
INSERT INTO stat (`banner_id`,`dt`,`views`) VALUES (1282, CURDATE(), 1), (1283, CURDATE(), 1) ON DUPLICATE KEY UPDATE views = views + 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1079 page no 358 n bits 376 index `main_key` of table bla trx id 26A4ED2F0 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1079 page no 358 n bits 376 index `main_key` of table bla trx id 26A4ED2F0 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (2)
вспоминаем, что banner_id и dt - уникальный ключ, обращаем внимание, что в обоих запросах обновляется по 2 ряда, причем в первом запросе сначала идет баннер 1283 потом 1282. Во втором же запросе сначала баннер 1282 потом 1283.
Вспоминаем про shuffle ДО подсчета показов. При желании материмся. Делаем выводы.
Выхода два: можно подсчитывать показы ДО shuffle, а можно делать отдельные запросы на подсчет для каждого баннера (по-моему так надежнее).
