Ребят, подскажите пожалуйста.
Почему, если несколько параллельных потоков будут выполнять такие транзакции, то номера employers.number будут инкрементировать последовательно:
start transaction;

SELECT max(number)+1 as cnt
FROM employers
FOR UPDATE;

insert into employers(...) values(cnt+1, ...);

commit;



НО!!!
Если убрать FOR UPDATE из поиска максимального номера, то параллельные транзакции пишут номера криво?

Call зарезервированно

Ответ в твоем вопросе, потому что FOR UPDATE, почитай что он делает

AUTO INCREMENT  делает то, что ты навелосипедил в своей транзакции если что

Ну, это ни совсем я.
Я просто пытаюсь исправить чужой косяк.

Объяснять всю логик не буду. Но смысл в том, что есть поле, которому нельзя задать auto increment.
Да, бывают такие ситуации.
Например, если есть таблица обращений. Назовем ее appeals.
В этой таблице есть поле id и оно под auto increment'ом.
Третьим полем этой таблицы, которое я хочу обозначить является поле create. Как ни странно, там хранится дата создания обращения.
Так же имеется мое "любимое поле" number. Условие состоит в том, что поле number должно увеличиваться в рамках текущего года.
Т.е. надо чтобы в рамках 2020 года number инкрементировалось относительно максимального number в этом году.

Я нашел 2 варианта решения проблемы:
1. Создать новую таблицу, напирмер number_year_max. И в нех ранить это максимальное число.
2. Делать все это в одной транзакции.
Мне понравился второй вариант.
Но только мне не ясно, почему оно работает. Уровень изоляции у меня  repeatable read.

Работает потому что FOR UPDATE получает эксклюзивную блокировку, параллельная транзакция не сможет считать MAX(number) пока текущая транзакция ее блокирует

Но вообще, ты так вроде пол таблицы блокируешь

Что есть не очень хорошо

Ну и твой вариант медленный. И/или требует отдельного индексы

Что, каждый новый год оно на ноль сбрасывается?

Условие состоит в том, что поле number должно увеличиваться в рамках текущего года.

В рамках года это как?

Это почему? 1 запись блокирует

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

А хотя MAX(number), какие там индексы хз, я чет думал что это pk)

Потому что и то и то неправильно

Согласен.

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

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

Да, читал...
Интересный подход.
Тогда +1 метод к решению задачи!

Ты имеешь ввиду вычитать из колонки авто-инкремента?