Почему в MySQL 8.0 при последовательной записи через пользовательскую функцию в автоинкрементном id образуются пропуски?

Question

[Иван Мельников]

Есть функция, через которую приложение пишет в базу на сервере MySQL 8.0:

CREATE function `insert_photo` (_link char(36), _material_id mediumint unsigned, _img mediumtext) returns mediumint unsigned
BEGIN
declare _id mediumint unsigned;
declare l binary(16);
set l = UNHEX(replace(_link, '-', ''));
insert ignore into Photo(link, material_id, img) value(l, _material_id, from_base64(_img));
select id into _id from Photo where link = l;
return _id;
END

Функция принимает данные для записи в базу и возвращает автоинкрементный id сделанной записи. Приложение пишет последовательно, то есть функция для следующей записи не вызывается пока не будет получен id предыдущей записи. Проблема в следующем. Я заметил, что в таблице в автоинкрементном id есть немало пропусков – при 10 000 записей id увеличивается на 15 000. Вообще не понятно откуда в данном случае могут взяться пропуски. Стал смотреть детальнее и обнаружил, что примерно для половины передаваемых данных функция вызывалась дважды. Для условных 10 000 единиц данных 5 000 из них были записаны при первом вызове функции, остальные 5 000 – только со второй попытки. Я сначала подумал, что по каким-то причинам возвращаемый функцией id не доходит до приложения (сбои в сети) и приложение повторно передает эти данные функции и получает id со второго раза. Но тут есть сразу два возражения. Во-первых, связь между сервером MySQL 8.0 и приложением надежная, которая в принципе не может давать таких сбоев. А, во-вторых, в базе нет ни одной записи, которая была бы сделана с 3-й или большей попытки. Прям магия какая-то! Кто-нибудь может прояснить причину подобного поведения MySQL 8.0?

Answer 1

[Rsa97]

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

Comments

[Иван Мельников]

Функция вставляет ровно одну строку.
Все данные из приложения записались в базу без ошибки, вот только половина с первого раза, а другая половина ровно при втором вызове функции...

[Rsa97]

Иван Мельников, Поскольку чудеса можно не рассматривать, то остаётся, IMHO, два варианта. Либо вы пытаетесь вставить строку с уникальным полем, уже присутствующим в таблице, либо при вставке возникает ошибка, которую вы не обрабатываете.

Answer 2

[Melkij]

А зачем эти извращения? Есть гарантированно конкурентно-безопасный LAST_INSERT_ID()

У вас insert ignore используется. Значит, данные натыкаются на уникальные индексы? auto_increment при этом штатно тикает.
Вообще auto_increment - это гарантированная уникальность. Если вы хотите "без пропусков" - вы взяли не тот инструмент.

Comments

[Иван Мельников]

А зачем эти извращения? Есть гарантированно конкурентно-безопасный LAST_INSERT_ID()

Допустим, что запись сделана, но id до приложения не дошел. Тогда приложение повторно вызывает функцию. Так как данная запись уже есть в таблице, то INSERT IGNORE ничего не делает. И что тогда должна вернуть LAST_INSERT_ID()?

У вас insert ignore используется. Значит, данные натыкаются на уникальные индексы?

Уникальных индексов нет.

Если вы хотите "без пропусков" - вы взяли не тот инструмент.

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

[Melkij]

Уникальных индексов нет.

Так как данная запись уже есть в таблице, то INSERT IGNORE ничего не делает

Так оно точно не работает.
Если строка корректна и не нарушены ограничения (например уникальности) - insert ignore вставит строку.

Результат insert ignore можете проверить через ROW_COUNT(), а поведение LAST_INSERT_ID() вполне описано в мануале.