Предотвращение взаимоблокировки MySQL при обновлении до эксклюзивной блокировки

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

Воспроизводите это, используя одновременно два сеанса клиента mysql:

mysql session 1:

create table parent (id int(11) primary key);
insert into parent values (1);
create table child (id int(11) primary key, parent_id int(11), foreign key (parent_id) references parent(id));

begin;
insert into child (id, parent_id) values (10, 1);
-- this will create shared lock on parent(1)

mysql session 2:

begin;
-- try and get exclusive lock on parent row
select id from parent where id = 1 for update;
-- this will block because of shared lock in session 1

mysql session 1:

-- try and get exclusive lock on parent row
select id from parent where id = 1 for update;
-- observe that mysql session 2 transaction has been rolled back

mysql session 2:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Информация, указанная в show engine innodb status, такова:

------------------------
LATEST DETECTED DEADLOCK
------------------------
161207 10:48:56
*** (1) TRANSACTION:
TRANSACTION 107E67, ACTIVE 43 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 13074, OS thread handle 0x7f68eccfe700, query id 5530424 localhost root statistics
select id from parent where id = 1 for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 3714 n bits 72 index `PRIMARY` of table `foo`.`parent` trx id 107E67 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000107e65; asc     ~e;;
 2: len 7; hex 86000001320110; asc     2  ;;

*** (2) TRANSACTION:
TRANSACTION 107E66, ACTIVE 52 sec starting index read
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 12411, OS thread handle 0x7f68ecfac700, query id 5530425 localhost root statistics
select id from parent where id = 1 for update
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 3714 n bits 72 index `PRIMARY` of table `foo`.`parent` trx id 107E66 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000107e65; asc     ~e;;
 2: len 7; hex 86000001320110; asc     2  ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 3714 n bits 72 index `PRIMARY` of table `foo`.`parent` trx id 107E66 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000107e65; asc     ~e;;
 2: len 7; hex 86000001320110; asc     2  ;;

*** WE ROLL BACK TRANSACTION (1)

Вы можете видеть, что транзакция (1) не отображает уже обнаруженные S или X блокировки; он просто заблокирован, пытаясь приобрести эксклюзивный замок. Поскольку нет цикла, в этой ситуации не должно быть тупика, как я понимаю.

Является ли это известной ошибкой MySQL? Попросить других людей? Какие обходные методы были использованы?

Это возможные шаги, которые мы могли бы предпринять:

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

Есть ли другие варианты, которые мы не рассматриваем?

Ответ 1

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

Это проблема в блокировке таблиц на уровне MySQL.

Внутренне внутри InnoDB, проверка ограничения FOREIGN KEY может считываться (или, с предложением ON UPDATE или ON DELETE, напишите) родительские или дочерние таблицы.

Обычно доступ к таблице определяется следующими блокировками: 1. Блокировка метаданных MySQL. 2. Блокировка стола InnoDB 3. Блокировка записи InnoDB

Все эти блокировки сохраняются до конца транзакции.

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

Я предполагаю, что обходным путем может быть доступ к дочернему (или родительскому) таблицы в начале транзакции, перед проблематикой FOREIGN KEY.

Прочитайте обсуждение и ответьте

Ответ 2

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

-- session 1
begin;
insert into child (id, parent_id) values (10, 1);
...
select id from parent where id = 1 for update;

Например, порядок (родительская таблица) имеет количество столбцов, которая сохраняется как сумма сумм всех строк порядка (дочерняя таблицу).

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

Если вставка в дочерний объект выполняется во многих разных местах, то логика приложения в клиенте должна быть обновлена во всех этих местах для поддержания целостности. Это дублирование кода.
Даже если это выполняется только в одном месте, тот факт, что родительская таблица необходимо обновить при добавлении ребенка, невозможно узнать сервер.

Вместо этого рассмотрите следующий вариант:

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

Он имеет следующие значения:

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

Протестировано с помощью 8.0, см. ниже.

Что касается проблемы пропускной способности concurrency,

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

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

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

Настройка

create table parent (
  id int(11) primary key,
  number_of_children int(11));

create table child (
  id int(11) primary key,
  parent_id int(11),
  foreign key (parent_id) references parent(id));

delimiter $$;
create trigger bi_child before insert on child
for each row
begin
  update parent
    set number_of_children = number_of_children + 1
    where id = NEW.parent_id;
end
$$
delimiter ;$$

begin;
insert into parent values (1, 0);
insert into parent values (2, 0);
commit;

Сессия 1

begin;
insert into child values (10, 1);

Сессия 2

begin;
insert into child values (20, 2);

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

Сессия 3

begin;
-- this now blocks, waiting for an X lock on parent row 1.
insert into child values (11, 1);

Сессия 1

-- unlocks session 3
commit;

Сессия 3

фиксации;

Сессия 2

фиксации;

Результаты

select * from parent;
id      number_of_children
1       2
2       1