Как сделать так, чтобы INSERT выполнился по результату SELECT атомарно?

Question

[Евгений Самсонов]

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

Как сделать, чтобы операция прошла атомарно, чтобы несколько процессов одновременно не смогли добавить пересекающиеся периоды?

Answer 1

[Вадим]

Если позволяет тип таблицы, используйте транзакцию

Comments

[Евгений Самсонов]

Думал про транзакции, но поможет ли она в этом случае. Ведь транзакции в обоих процессах увидят, что там пусто, и успешно добавят данные. Решит ли проблему последний уровень Serializable?

[Евгений Самсонов]

Евгений Самсонов, судя по всему, действительно Serializable решит проблему. Транзакции выполняются полностью последовательно на этом уровне

[Вадим]

Должно решить проблему. В любом случае это можно легко проверить установив между выборкой и вставкой таймаут и запустив скрипт пару раз

[Евгений Самсонов]

Вадим, ок, попробую на разных уровнях. Заодно лучше пойму как это работает :)

[Melkij]

Транзакции выполняются полностью последовательно на этом уровне

Утверждение не верно.
На этом уровне результат транзакции должен быть такой же, как при последовательном выполнении этих же транзакций. Это не то же самое, что исполняются последовательно.

Однако не уверен, что реализация serializable в mysql/innodb корректна для этого случая. Напишите как проверите, интересно (но не настолько чтобы ставить mysql).

create table foo (i int);
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
start transaction;
select * from foo where i != 5;
insert into foo values (5);
-- до коммита сделайте со второй консоли такие же запросы начиная с set transaction, затем коммит в обеих
-- при корректной реализации второй коммит разрешён не должен быть.
commit;

[Евгений Самсонов]

Melkij, спасибо, что подготовили запросы, мне осталось только выполнить :) Да, все корректно, во второй консоли зависло при выполнение select, ожидая commit в первой. Значит решает проблему

[Melkij]

ага. А если исполнять команды по очереди в двух консолях:
t1: start transaction
t2: start transaction
t1: select
t2: select
t1: insert
t2: insert
где транзакция2 ждать начнёт?

[Евгений Самсонов]

Melkij, тут в первой транзакции на insert начинает висеть. А во второй при insert deadlock (

[Melkij]

Могло быть хуже. На serializable всегда надо быть готовым повторить транзакцию заново.

возможно ситуацию улучшит если сделать select ... for update. Но я не знаю как в точности serializable работает в mysql. Известно что как блокировочник, а вот где и какие локи при этом берёт - не знаю.

[Евгений Самсонов]

Melkij, да, непростая тема и интересный вопрос. Действительно, можно попробовать словить deadlock и снова запустить транзакцию. Спасибо, буду разбираться

[Melkij]

ну и напоследок, вот так что получится?
t1: start transaction
t2: start transaction
t1: select
t2: select
t1: insert
t1: commit
t2: insert
t2: commit

[Евгений Самсонов]

Melkij, нет, тут тоже самое. Он на insert в первом терминале уже начинает висеть, то есть тут commit никак не получается выполнить)

[Евгений Самсонов]

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

Answer 2

[SagePtr]

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