Краткое введение в блокировку Gap
Блокировка гэпа — это блокировка промежутка между записями индекса или блокировка промежутка перед первой или после последней записи индекса.
В дополнение к проверке ограничений внешнего ключа и проверке повторяющихся ключей, блокировка пробелов включается для поиска и сканирования индекса, если уровень изоляции транзакции выше Повторяемое чтение.
Этот механизм блокировки помогает предотвратить вставку других транзакций в пробел, пока транзакция считывает диапазон. В результате InnoDB может предотвращать аномалии Phantom-Read , даже если его уровень изоляции транзакций Repeatable Read
(Более подробную информацию об уровнях изоляции ANSI SQL см. в этом замечательном сообщении в блоге).
Как описано выше, блокировка пробелов предотвращает появление аномалий Phantom-Read. Однако, если вы разрабатываете приложение, которое одновременно ВСТАВЛЯЕТ кучу данных, не зная этого механизма блокировки, может возникнуть множество взаимоблокировок.
Проблемный случай, который приведет к взаимоблокировке
Предположим, что вы разрабатываете приложение, которое одновременно выполняет следующий SQL в одной транзакции (и вы не можете использовать INSERT ... ON DUPLICATE KEY UPDATE по некоторым причинам).
SELECT * FROM `blog` WHERE id = ... FOR UPDATE; -- the following query will executed only when -- the row was not found in the first query. INSERT INTO `blog` (id, title, content) VALUES (...);
где схема базы данных примерно такая.
(Для этого примера мы используем SELECT ... FOR UPDATE, но к этому случаю можно применить любые блокирующие запросы на чтение, такие как UPDATE и DELETE).
Почему возникает взаимоблокировка
Если вы успешно нашли строку в первом запросе, это не проблема. Однако, если первый поисковый запрос не смог найти ни одной строки, проблема возникнет, потому что
- если блокирующий оператор чтения использует уникальный индекс с уникальным условием поиска, InnoDB блокирует только найденную запись индекса.
- Однако, если запись не найдена оператором, InnoDB получает (общую) блокировку разрыва, которая покрывает ключ, описанный условием поиска.
В результате взаимоблокировка произойдет в следующем случае (предположим, что существуют записи с идентификаторами 3 и 6).
- Транзакция1:
SELECT * FROM blog WHERE id = 4 FOR UPDATE;и блокирует разрыв между id=3 и id=6 (поскольку не удалось найти запись). - Транзакция 2:
SELECT * FROM blog WHERE id = 5 FOR UPDATE;и блокирует разрыв между id=3 и id=6 (она не будет заблокирована, поскольку эти блокировки пробелов являются общими блокировками). - Транзакция 1:
INSERT INTO blog (id, ...) VALUES (4, ...);, этот оператор будет ждать, потому что транзакция 2 уже зафиксировала разрыв между id=3 и id=6. - Транзакция 2:
INSERT INTO blog (id, ...) VALUES (5, ...);, этот оператор будет ждать, потому что транзакция 1 уже зафиксировала разрыв между id=3 и id=6. - Произошла взаимоблокировка!
Дополнительные сведения о механизмах блокировки в InnoDB см. в документации.
Разрешить (избежать) взаимоблокировки
Итак, что мы можем сделать, чтобы избежать этого тупика?
Самый простой способ — просто повторить неудачную транзакцию. Это самый надежный способ, но иногда эту стоимость повторных попыток нельзя игнорировать, особенно для приложения, требующего высокой производительности обработки.
Одним из способов решения этой проблемы является выполнение следующего SQL в одной транзакции вместо SELECT ... FOR UPDATE, а затем INSERT.
INSERT INTO blog (...) VALUES (...) ON DUPLICATE KEY UPDATE id = id; SELECT * FROM blog WHERE id = ... FOR UPDATE;
Первый запрос пытается ВСТАВИТЬ запись и ничего не делает, если запись дублируется. Этот запрос не получает гэп-блокировку, которая блокирует блокировку запроса на чтение в другой транзакции, если уникальный ключ не дублируется.
(Технически, INSERT запрос получает блокировку пробела, называемую Вставить блокировку намерения).
Таким образом, эта транзакция может выполнять ту же логику, что и «ВЫБЕРИТЕ и ВСТАВЬТЕ, если строка не найдена» без взаимной блокировки.
Заметим, что, к сожалению, эта методика не всегда применима. Например, если на уникальный ключ наложено ограничение внешнего ключа.
Исследуйте тупик
В расследовании причин взаимоблокировки вам поможет Монитор блокировки InnoDB :)
Удачной замковой жизни!
