Как избежать обнаружения тупика MySQL при попытке получить блокировку; попробуйте перезапустить транзакцию '

Один простой прием, который может помочь с большинством тупиковых ситуаций, - это сортировка операций в определенном порядке.

Вы получаете тупик, когда две транзакции пытаются заблокировать две блокировки в противоположных ордерах, то есть:

• соединение 1: ключ для замков (1), ключ для замков (2);
• соединение 2: ключ для замков (2), ключ для замков (1);

Если оба работают одновременно, соединение 1 заблокирует ключ (1), соединение 2 заблокирует ключ (2), и каждое соединение будет ждать, пока другое соединение отпустит ключ - ›тупик.

Теперь, если вы изменили свои запросы так, чтобы соединения блокировали ключи в том же порядке, то есть:

• соединение 1: ключ для замков (1), ключ для замков (2);
• соединение 2: блокирует ключ (1), блокирует ключ (2);

зайти в тупик будет невозможно.

Вот что я предлагаю:

1. Убедитесь, что у вас нет других запросов, которые блокируют доступ к более чем одному ключу одновременно, за исключением оператора удаления. если вы это сделаете (и я подозреваю, что вы это сделаете), закажите их WHERE in (k1, k2, .. kn) в порядке возрастания.

2. Исправьте оператор удаления, чтобы он работал в порядке возрастания:

Изменять

DELETE FROM onlineusers 
WHERE datetime <= now() - INTERVAL 900 SECOND

To

DELETE FROM onlineusers 
WHERE id IN (
    SELECT id FROM onlineusers
    WHERE datetime <= now() - INTERVAL 900 SECOND 
    ORDER BY id
) u;

Также следует иметь в виду, что документация MySQL предполагает, что в случае тупиковой ситуации клиент должен автоматически повторить попытку. вы можете добавить эту логику в свой клиентский код. (Скажем, 3 попытки исправить эту конкретную ошибку, прежде чем отказаться).

Тупиковая ситуация возникает, когда две транзакции ждут друг друга, чтобы получить блокировку. Пример:

• Tx 1: заблокировать A, затем B
• Tx 2: заблокировать B, затем A

Есть множество вопросов и ответов о тупиках. Каждый раз, когда вы вставляете / обновляете / или удаляете строку, устанавливается блокировка. Чтобы избежать взаимоблокировки, вы должны убедиться, что параллельные транзакции не обновляют строку в порядке, который может привести к взаимоблокировке. Вообще говоря, старайтесь получить блокировку всегда в одном и том же порядке даже в разных транзакциях (например, сначала всегда таблица A, затем таблица B).

Другой причиной тупика в базе данных может быть отсутствие индексов. Когда строка вставляется / обновляется / удаляется, базе данных необходимо проверить реляционные ограничения, то есть убедиться, что отношения согласованы. Для этого базе данных необходимо проверить внешние ключи в связанных таблицах. Это может привести к получению другой блокировки, кроме измененной строки. Убедитесь, что в этом случае всегда есть индекс для внешних ключей (и, конечно, первичных ключей), иначе это может привести к блокировке таблицы вместо блокировки строки. Если происходит блокировка таблицы, конкуренция за блокировку выше и вероятность тупиковой ситуации увеличивается.

Вероятно, что оператор удаления повлияет на большую часть всех строк в таблице. В конечном итоге это может привести к блокировке таблицы при удалении. Удержание блокировки (в данном случае блокировки строк или страниц) и получение дополнительных блокировок всегда сопряжены с риском взаимоблокировки. Однако я не могу объяснить, почему оператор вставки приводит к эскалации блокировки - это может быть связано с разделением / добавлением страницы, но кто-то, лучше знающий MySQL, должен будет это заполнить.

Для начала, возможно, стоит попытаться сразу же явно получить блокировку таблицы для оператора удаления. См. ТАБЛИЦЫ БЛОКИРОВКИ и Проблемы с блокировкой таблиц.

Если кто-то все еще борется с этой проблемой:

Я столкнулся с аналогичной проблемой, когда на сервер одновременно поступало 2 запроса. Не было такой ситуации, как показано ниже:

T1:
    BEGIN TRANSACTION
    INSERT TABLE A
    INSERT TABLE B
    END TRANSACTION

T2:
    BEGIN TRANSACTION
    INSERT TABLE B
    INSERT TABLE A
    END TRANSACTION

Итак, я был озадачен, почему происходит тупик.

Затем я обнаружил, что между двумя таблицами существует связь между двумя таблицами из-за внешнего ключа. Когда я вставлял запись в дочернюю таблицу, транзакция блокировала строку родительской таблицы. Сразу после этого я пытался обновить родительскую строку, которая вызвала повышение блокировки до ИСКЛЮЧИТЕЛЬНОЙ. Поскольку 2-я параллельная транзакция уже удерживала блокировку SHARED, это приводило к тупиковой ситуации.

См .: https://blog.tekenlight.com/2019/02/21/database-deadlock-mysql.html

Вы можете попробовать запустить это задание delete, сначала вставив ключ каждой удаляемой строки во временную таблицу, например этот псевдокод.

create temporary table deletetemp (userid int);

insert into deletetemp (userid)
  select userid from onlineusers where datetime <= now - interval 900 second;

delete from onlineusers where userid in (select userid from deletetemp);

Такой разрыв менее эффективен, но позволяет избежать необходимости удерживать блокировку диапазона клавиш во время delete.

Кроме того, измените свои select запросы, чтобы добавить предложение where, исключающее строки старше 900 секунд. Это позволяет избежать зависимости от задания cron и позволяет перенести его на более редкое выполнение.

Теория взаимоблокировок: у меня мало опыта работы с MySQL, но вот ... delete будет удерживать блокировку диапазона ключей для datetime, чтобы строки, соответствующие его предложению where, не добавлялись в середине транзакции, и по мере того, как она находит строки для удаления, она будет пытаться получить блокировку на каждой изменяемой странице. insert собирается получить блокировку страницы, в которую он вставляется, а затем затем попытается получить блокировку ключа. Обычно insert будет терпеливо ждать, пока откроется блокировка этой клавиши, но это зайдет в тупик, если delete попытается заблокировать ту же страницу, которую использует insert, потому что delete нужна эта блокировка страницы, а insert нужна эта блокировка. Это кажется неправильным для вставок, delete и insert используют диапазоны даты и времени, которые не перекрываются, поэтому, возможно, происходит что-то еще.

http://dev.mysql.com/doc/refman/5.1/en/innodb-next-key-locking.html

Для Java-программистов, использующих Spring, я избежал этой проблемы, используя аспект АОП, который автоматически повторяет транзакции, которые приводят к временным тупикам.

См. @RetryTransaction Javadoc для получения дополнительной информации.

У меня есть метод, внутренняя часть которого заключена в MySqlTransaction.

Проблема с тупиком обнаружилась у меня, когда я запустил тот же метод параллельно с ним самим.

Не было проблем с запуском одного экземпляра метода.

Когда я удалил MySqlTransaction, я смог без проблем запустить этот метод параллельно с самим собой.

Просто делюсь своим опытом, я ничего не защищаю.

cron опасно. Если один экземпляр cron не завершится до истечения следующего срока, они, вероятно, будут сражаться друг с другом.

Было бы лучше иметь постоянно работающее задание, которое удаляло бы некоторые строки, некоторые засыпало, а затем повторяло.

Кроме того, INDEX(datetime) очень важен для предотвращения тупиковых ситуаций.

Но если тест datetime включает, скажем, более 20% таблицы, DELETE выполнит сканирование таблицы. Чанки меньшего размера удаляются чаще - это временное решение.

Еще одна причина использования меньших блоков - блокировка меньшего количества строк.

Нижняя линия:

• INDEX(datetime)
• Постоянно запущенная задача - удалить, поспать минутку, повторить.
• Чтобы убедиться, что вышеуказанная задача не умерла, создайте задание cron, единственная цель которого - перезапустить его в случае сбоя.

Другие методы удаления: http://mysql.rjweb.org/doc.php/deletebig