Меню
Что нового?

Mysql MySQL устранение deadlock

stalxed

Новичок
Есть следующий упрощенный запрос(удалил лишнее):

Код: 
START TRANSACTION

SELECT ol.created_date, ol.orderId
FROM latest_action ol
WHERE ol.orderId = ?
FOR UPDATE

// много не блокирующих селектов

INSERT INTO latest_action (created_date, orderId) VALUES (?, ?)

COMMIT
Конструкция
INSERT INTO latest_action (created_date, orderId) VALUES (?, ?)
используется лишь в 1 участке кода.

И вот по записанным трейсам я вижу, что в этом участке периодически происходят deadlockи.

Дальше стал смотреть MySQL логи.

SHOW ENGINE INNODB STATUS показывает следующие два запроса:
INSERT INTO latest_action (created_date, orderId) VALUES ('2015-11-22 17:33:13', '1642')
INSERT INTO latest_action (created_date, orderId) VALUES ('2015-11-22 17:33:13', '1641')

Правильно ли я понимаю, что поймал GAP блокировку? Или id близко друг с другом - это просто совпадение?
Как бороться с такой блокировкой? Повторить транзакцию? И что это за глупость такая, взаимной блокировки здесь нет, я весь код прочесал, это лишняя "умная" перестраховка MySQL?
 

stalxed

Новичок
Нагуглил.
http://dba.stackexchange.com/questions/101208/why-do-these-concurrent-mysql-inserts-deadlock
Ситуация практически 1 в 1.

Но не могу врубится в чём суть проблемы...

Хм, там следующий текст:
The essence of the problem is that the write lock obtained by SELECT FOR UPDATE does not block other write locks from being obtained on the gap. The two write locks block each other from being upgraded to an insert intent lock, leading to, consecutively, a block, then a deadlock.
Нажмите для раскрытия...
Получается так:
КЛИЕНТ 1: SELECT id FROM table WHERE id = 1 FOR UPDATE
// блокирует только id 1
// клиент 1 получает блокировку
КЛИЕНТ 2: SELECT id FROM table WHERE id = 2 FOR UPDATE
// блокирует только id 2
// клиент 2 получает блокировку
КЛИЕНТ 1: INSERT INTO table (id) ('1')
// не может получить insert intent lock
// ждёт, когда клиент 2 освободит блокировку
КЛИЕНТ 2: INSERT INTO table (id) ('2')
получается dedlock

Такое возможно? Что-то глупо как-то...
 
Последнее редактирование:
shureen

shureen

Милорд Лось Кристофер
Потому что FOR UPDATE блокирует по факту всю таблицу, а не только записи попадающие под условия where
 

stalxed

Новичок
@shureen, а вот и нет.
Donald ball на stackexchange прав, что как раз проблема в том, что FOR UPDATE не блокирует всю таблицу.

Вот таблица:
Код: 
CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL,
  `text` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `test`  ADD PRIMARY KEY (`id`);
ALTER TABLE `test`  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
А вот сам процесс:
1) Первый клиент блокирует запись 101(она не существует):
Код: 
mysql_client1> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql_client1> SELECT `id`, `text` FROM `test` WHERE id = 101 FOR UPDATE;
Empty set (0.00 sec)
2) Второй клиент блокирует запись 100(она не существует):
Код: 
mysql_client2> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql_client2> SELECT `id`, `text` FROM `test` WHERE id = 100 FOR UPDATE;
Empty set (0.00 sec)
3) Первый клиент пытается создать запись 101:
Код: 
mysql_client1> INSERT INTO `test` (`id`, `text`) VALUES ("101", "test 101");
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Ждёт около минуты и вываливает ошибку выше...

Я не понимаю этого поведения MySQL...

Как лучше исправить? Сделать повторную транзакцию?

Ситуация в том, что запись должна быть уникальной, поэтому выполняю SELECT .... FOR UPDATE / INSERT, я думал эта конструкция безопасна в плане параллельного доступа.

IGNORE IF EXISTS доктрина не поддерживает, да и в плане бизнес логики тупая конструкция.
 
Последнее редактирование:
флоппик

флоппик

promotor fidei
Команда форума
Партнер клуба
Блокировки в иннодб устроены немного сложней, чем «блокирует строку по Id» Начиная от того, что режимов блокировок много, заканчивая тем, что он блокирует блоками строк, и например, иногда блокирует пару цифр в автоинкрементом секвенсе в транзакциях заранее. У блокировок на чтение приоритет ниже, чем у блокировок на запись, например — поэтому и возможны два взаимоблокирующих блока при select for update, например.
 

stalxed

Новичок
@флоппик, судя по экспериментам SELECT ... FOR UPDATE - лочит 1 строку на запись.
Тогда как INSERT хочет залочить всю таблицу на запись. Отсюда и deadlock.

Вот как мне избежать этого? Или не избегать, а повторять транзакцию несколько раз?
 
флоппик

флоппик

promotor fidei
Команда форума
Партнер клуба
3) Первый клиент пытается создать запись 101:
Нажмите для раскрытия...
Потому что ты передаешь явно ид, который может быть в прозвольном месте таблицы. Отсюда фуллскан, отсюда и локи на всю таблицу. Пользуйся нормально блокировкой автоинкрементного секвенса, и получай его внутри транзакции через Last_insert_id()
 

stalxed

Новичок
@флоппик, добавил автоинкремент, не передаю id для него.
Но добавил столбец с внешним ключом, т.е. в таблице должно быть только 1 значение с уникальным внешним ключом.

Первый клиент:
Код: 
mysql_client1> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql_client1> SELECT `id`, `text` FROM `test2` WHERE test_id = 1 FOR UPDATE;
Empty set (0.00 sec)

// здесь работает второй клиент

mysql_client1> INSERT INTO `test`.`test2` (`id`, `test_id`, `text`) VALUES (NULL, '1', 'dssdsd');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>
Второй клиент:

Код: 
mysql_client2> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql_client2> SELECT `id`, `text` FROM `test2` WHERE test_id = 2 FOR UPDATE;
Empty set (0.00 sec)
Такая же ситуация...
 

stalxed

Новичок
Блин, неужели делать повторное выполнение транзакции, потом админы меня будут лишний раз пинать, мол что тут в логе за deadlock проверь нет ли багов....
 
grigori

grigori

( ͡° ͜ʖ ͡°)
Команда форума
Когда запись не найдена, заблокировать ее невозможно, поэтому вместо записи блокируется диапазон.

Например, в первой консоли
Код: 
mysql> INSERT INTO `test` (`id`, `text`) VALUES (10, "test 101");
Query OK, 1 row affected (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT `id`, `text` FROM `test` WHERE id = 1 FOR UPDATE;
Empty set (0.00 sec)
Во второй:
Код: 
mysql> INSERT INTO `test` (`id`, `text`) VALUES (11, "test 101");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `test` (`id`, `text`) VALUES (9, "test 101");
...висим
 

stalxed

Новичок
@grigori, спасибо, я более менее понял, из-за чего возникает баг.
Продолжаю читать, гуглить, изучать.
Но вот какие есть способы исправления?
 

stalxed

Новичок
grigori написал(а):
я не телепат, задачу не знаю, скорее всего алгоритмы нужны другие
Нажмите для раскрытия...
В том то и дело, что это не задача, а банальный One-To-One, Unidirectional relationship.
Поле orderId уникальное, чтобы One-To-One не превратился в Many-To-One.
И перед добавлением записи выполняю SELECT ... FOR UPDATE.
 
grigori

grigori

( ͡° ͜ʖ ͡°)
Команда форума
гарантия уникальности обеспечивается через unique index,
нарушение уникальности в общем случае называется race condition, и UK его отлавливает,
возникает оно обычно из-за неправильного алгоритма, реже - из-за реальной многопоточности логики,
исправлять здесь надо алгоритм в целом
 

stalxed

Новичок
@grigori, я изменил немного бизнес логику, т.е.

Было:
  1. START TRANSACTION;
  2. Select ... FOR UPDATE
  3. Множество неблокирующих селектов, для сбора статистики
  4. INSERT ...
  5. COMMIT;

Стало:
  1. START TRANSACTION;
  2. Множество неблокирующих селектов, для сбора статистики
  3. Select ... FOR UPDATE
  4. INSERT ...
  5. COMMIT;
В теории значительно должны уменьшиться шансы на diedlock. Остается только ждать сообщений в логах, мониторингах...

В таких ситуациях значит просто необходимо уменьшать шансы на diedlock?
Альтернатива полностью лочить таблицу мне явно не по душе.
Т.е. это всё не идеальные решения, а балансировка между молотом и наковальней?
 
grigori

grigori

( ͡° ͜ʖ ͡°)
Команда форума
@stalxed, после того, как 3 человека тебе сказали, что нужны другие инструменты и алгоритмы, и написали какие именно, ты повторяешь свои слова 4й раз. Ты ждешь, что мы подтвердим, что уменьшать проблему - нормально, и все мы с этим живем? нет, мы используем подходящие инструменты и полностью избавляемся от нее
 
Последнее редактирование:
fixxxer

fixxxer

К.О.
Партнер клуба
Я один не понял, зачем тут вообще select for update?
Может, ТС плохо объяснил, но, похоже, тут явно уместнее insert on duplicate key update или insert ignore.

Касаемо самого select for update - можно попробовать снизить уровень изоляции транзакции до READ COMMITTED, в этом случае вроде негде полочиться.
 
Войдите или зарегистрируйтесь для ответа.
Сверху