Как избежать взаимоблокировок СУБД MySQL 'при попытке получить блокировку; попробуйте перезапустить сделки

у меня есть таблица innoDB, которая записывает онлайн-пользователей. Он обновляется при каждом обновлении страницы пользователем, чтобы отслеживать, на каких страницах они находятся, и их последнюю дату доступа к сайту. Затем у меня есть cron, который запускается каждые 15 минут для удаления старых записей.



я получил "тупик, найденный при попытке получить блокировку; попробуйте перезапустить транзакцию" около 5 минут прошлой ночью, и, похоже, это происходит при запуске вставок в эту таблицу. Может кто-нибудь подсказать, как этого избежать ошибка?



= = = EDIT ===



вот запросы, которые выполняются:



первый визит на сайт:



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


Cron каждые 15 минут:



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


затем он делает некоторые подсчеты для регистрации некоторых статистических данных (т. е.: участники онлайн, посетители онлайн).

1136   6  

6 ответов:

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

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

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

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

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

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

будет невозможно получить тупик.

Так вот что я предлагаю:

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

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

изменить 

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 попытки на эту конкретную ошибку, прежде чем сдаваться).

229  
2014-10-14 21:59:05

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

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

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

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

55  
2013-08-07 15:17:53

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

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

7  
2018-03-22 19:28:51

вы можете попробовать, что 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 используют диапазоны datetime, которые не перекрываются, поэтому, возможно, что-то еще происходит на.

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

5  
2010-03-11 18:26:47

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

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

1  
2017-01-19 19:32:26

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

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

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

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

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

0  
2018-08-04 16:29:44

Comments

    Ничего не найдено.
Click here to cancel reply.