Предотвращение взаимоблокировки MySQL при обновлении общего доступа до эксклюзивной блокировки
Я использую MySQL 5.5. Я заметил специфический тупик, возникающий в параллельном сценарии, и я не думаю, что этот тупик должен возникать.
Воспроизведите так, используя две клиентские сессии mysql, запущенные одновременно:
MySQL сеанс 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 сессия 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 сеанс 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 сессия 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 на ту, которая не демонстрирует такое поведение
Есть ли другие варианты, которые мы не рассматриваем?
2 ответа
Это давняя ошибка, о которой вы можете прочитать больше:
Это проблема блокировки таблицы на уровне MySQL.
Внутри InnoDB проверка ограничения FOREIGN KEY может читать (или, с помощью предложения ON UPDATE или ON DELETE, записывать) родительские или дочерние таблицы.
Обычно доступ к таблице регулируется следующими блокировками: 1. Блокировка метаданных MySQL 2. Блокировка таблицы InnoDB 3. Блокировка записи InnoDB
Все эти блокировки удерживаются до конца транзакции.
Блокировки таблиц и записей InnoDB пропускаются в определенных режимах, но не во время проверок внешнего ключа. Тупик вызван тем, что MySQL получает блокировку метаданных только для таблиц, которые явно упоминаются в инструкциях SQL.
Я предполагаю, что обходным путем может быть доступ к дочерним (или родительским) таблицам в начале транзакции, до проблемной операции FOREIGN KEY.
Прочитайте обсуждение и ответ
Причина обновления родительской строки не была указана, но я предполагаю, что это связано с некоторой де-нормализацией, основанной на этой последовательности из вопроса:
-- session 1
begin;
insert into child (id, parent_id) values (10, 1);
...
select id from parent where id = 1 for update;
Например, заказ (родительская таблица) имеет сумму столбца, которая поддерживается как сумма сумм всех строк заказа (дочерняя таблица).
Кажется, логика поддержки родительских данных закодирована в самом приложении (с явными операторами обновления), что имеет следующие последствия:
Если вставка в child выполняется во многих разных местах, то логика приложения в клиенте должна быть обновлена во всех этих местах, чтобы сохранить целостность. Это дублирование кода.
Даже если это делается только в одном месте, сервер не может выяснить тот факт, что родительская таблица должна обновляться при добавлении дочернего элемента.
Вместо этого, пожалуйста, рассмотрите следующий вариант:
Определите триггеры для дочерней таблицы, которые обновляют родительскую таблицу по мере необходимости.
Это имеет следующие последствия:
Во-первых, логика поддержки родительской таблицы больше не (возможно) дублируется, как в самом триггере.
Во-вторых, и это важная часть, сервер MySQL теперь знает, что родительский элемент таблицы обновляется всякий раз, когда вставляется дочерняя запись, и из-за этого берется правильная блокировка (исключающая, а не общая).
Протестировано с 8.0, см. Ниже.
Что касается озабоченности по поводу пропускной способности параллелизма,
различные транзакции, работающие с разными родительскими строками, будут выполняться параллельно, так как исключительные блокировки выполняются для родительских (разных) строк, а не для родительской таблицы.
транзакции, работающие одновременно в одной и той же родительской строке, действительно будут сериализованы... что на самом деле является ожидаемым результатом, поскольку они все равно завершаются с одной и той же записью.
Сериализация транзакций, которые гарантированно будут успешными, должна обеспечивать лучшую пропускную способность (насколько это касается рабочей нагрузки приложения), что при сбое некоторых транзакций происходит только повторная попытка их выполнения.
Очевидно, что триггеры обновления и удаления также необходимы для обновления родительского элемента, в зависимости от логики приложения.
Настроить
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