Почему автоинкремент MySQL увеличивается на неудавшихся вставках?
Коллега просто сделал меня знающий об очень странном поведении MySQL.
Принятие Вас имеет таблицу с auto_increment полем и другим полем, которое установлено на уникальный (например, поле имени пользователя). При попытке вставить строку с именем пользователя это уже находится в таблице сбои вставки, как ожидалось. Все же значение auto_increment увеличено как видно того, когда Вы вставляете допустимую новую запись после нескольких неудачных попыток.
Например, когда наша последняя запись похожа на это...
ID: 10
Username: myname
... и мы пробуем пять новых записей тем же значением имени пользователя на нашей следующей вставке, как которая мы создадим новую строку так:
ID: 16
Username: mynewname
В то время как это не большая проблема сам по себе, на очень глупый вектор атаки походит уничтожать таблицу путем лавинной рассылки его с неудавшимися запросами вставки как состояния MySQL Reference Manual:
"Поведение автоинкрементного механизма не определяется, если [...] значение становится больше, чем максимальное целое число, которое может быть сохранено в указанном целом типе".
Это - ожидаемое поведение?
2 ответа
InnoDB - это транзакционный движок.
Это означает, что в следующем сценарии:
Сессия Aвставляет запись1Сессия Bвставляет запись2Сессия Aоткатывается
, существует либо возможность разрыва, либо сессия B будет блокироваться, пока сессия A не зафиксирует или не откатится.
Разработчики InnoDB (как и большинство других разработчиков транзакционных движков) решили разрешить разрывы.
Из документации:
При доступе к счетчику автоинкремента
InnoDBиспользует специальную блокировку на уровне таблицыAUTO-INC, которая сохраняется до конца текущегоSQLоператора, а не до конца транзакции. Специальная стратегия освобождения блокировки была введена для улучшения параллелизма при вставках в таблицу, содержащуюAUTO_INCREMENTстолбец...
InnoDBиспользует счетчик автоинкремента в памяти до тех пор, пока работает сервер. Когда сервер останавливается и перезапускается,InnoDBповторно инициализирует счетчик для каждой таблицы для первогоINSERTк таблице, как описано ранее.
Если вы боитесь, что столбец id обернется вокруг, сделайте его BIGINT (длиной 8 байт).
Не зная точного внутреннего устройства, я бы сказал, что да, автоприращение ДОЛЖНО допускать пропуск значений для неудачные вставки. Допустим, вы выполняете банковскую транзакцию или другое, где вся транзакция и несколько записей идут как все или ничего. Если вы попробуете вставить, получите идентификатор, затем отметьте все последующие детали этим идентификатором транзакции и вставьте подробные записи, вам необходимо обеспечить свою квалифицированную уникальность. Если у вас есть несколько человек, хлопающих по базе данных, им тоже нужно будет убедиться, что они получат свой собственный идентификатор транзакции, чтобы не конфликтовать с вашим, когда их транзакция будет зафиксирована. Если что-то не удается в первой транзакции, никакого вреда не будет, и никаких зависших элементов ниже по течению.