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

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

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

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

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

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

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

Однако не уверен, что реализация 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 в первой. Значит решает проблему

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

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

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

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

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

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

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

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