Как избежать, чтобы mysql 'Мертвая блокировка нашел при попытке получить блокировку; попытайтесь перезапустить транзакцию'
У меня есть innoDB таблица, которая записывает подключенных пользователей. Это обновляется на каждом обновлении страницы пользователем для отслеживания, которых страниц они идут и их последняя дата доступа на сайт. У меня затем есть крон, который работает каждые 15 минут для УДАЛЕНИЯ старых записей.
Я нашел 'Мертвую блокировку при попытке получить блокировку; попытайтесь перезапустить транзакцию' в течение приблизительно 5 минут вчера вечером, и это, кажется, когда выполнение ВСТАВЛЯЕТ в эту таблицу. Кто-то может предложить, как избежать этой ошибки?
=== РЕДАКТИРОВАНИЕ ===
Вот запросы, которые работают:
Первое Посещение сайта:
INSERT INTO onlineusers SET
ip = 123.456.789.123,
datetime = now(),
userid = 321,
page = '/thispage',
area = 'thisarea',
type = 3
На каждом обновлении страницы:
UPDATE onlineusers SET
ips = 123.456.789.123,
datetime = now(),
userid = 321,
page = '/thispage',
area = 'thisarea',
type = 3
WHERE id = 888
Крон каждые 15 минут:
DELETE FROM onlineusers WHERE datetime <= now() - INTERVAL 900 SECOND
Это затем делает некоторые количества для входа некоторой статистики (т.е.: участники онлайн, посетители онлайн).
4 ответа
Один простой прием, который может помочь с большинством взаимоблокировок, - это сортировка операций в определенном порядке.
Тупиковая ситуация возникает, когда две транзакции пытаются заблокировать две блокировки в противоположных порядках, например:
- соединение 1: блокирует ключ (1), блокирует ключ (2);
- соединение 2: блокирует ключ (2) ), блокирует ключ (1);
Если оба работают одновременно, соединение 1 заблокирует ключ (1), соединение 2 заблокирует ключ (2), и каждое соединение будет ждать, пока другое соединение отпустит ключ -> тупик.
Теперь, если вы изменили свои запросы так, что соединения будут блокировать ключи в том же порядке, то есть:
- соединение 1: блокирует ключ (1), блокирует ключ (2);
- соединение 2: блокирует ключ ( 1 ), ключ блокировки ( 2 );
невозможно будет попасть в тупик.
Итак, вот что я предлагаю:
Убедитесь, что у вас нет других запросов, которые блокируют доступ к более чем одному ключу за раз, за исключением оператора удаления. если вы это сделаете (и я подозреваю, что вы это сделаете), закажите их WHERE in (k1, k2, .. kn) в порядке возрастания.
Исправьте оператор удаления, чтобы он работал в возрастающем порядке:
Замените
DELETE FROM onlineusers WHERE datetime <= now() - INTERVAL 900 SECOND
на
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, должен будет заполнить его.
Для начала может быть стоит попытаться сразу же явно получить блокировку таблицы для оператора удаления. См. ТАБЛИЦЫ БЛОКИРОВКИ и Проблемы блокировки таблиц .
Вы можете попробовать выполнить задание 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);
Разбивка на части менее эффективна, но позволяет избежать необходимо удерживать блокировку диапазона клавиш во время удаления .
Также измените запросы select , чтобы добавить предложение where , исключающее строки старше 900 секунд. Это позволяет избежать зависимости от задания cron и позволяет перенести его на более редкое выполнение.
Теория взаимоблокировок: у меня не очень много опыта работы с MySQL, но вот ... delete будет удерживать блокировку диапазона ключей для datetime, чтобы не допустить совпадения строк с его где предложение не добавляется в середине транзакции, и когда он находит строки для удаления, он будет пытаться получить блокировку на каждой изменяемой странице. Вставка собирается получить блокировку страницы, в которую она вставляется, и затем попытаются получить блокировку ключа. Обычно вставка терпеливо ожидает открытия блокировки этой клавиши, но это приведет к взаимной блокировке, если delete попытается заблокировать ту же страницу, которую использует вставка , потому что delete требует блокировки страницы, а вставка требует блокировки клавиш. Это кажется неправильным для вставок, delete и insert используют диапазоны дат и времени, которые не перекрываются, поэтому, возможно, происходит что-то еще.
http://dev.mysql.com/doc/refman/5.1/en/innodb-next-key-locking.html