Почему автоинкремент MySQL увеличивается при неудачных вставках?

Сотрудник просто сообщил мне о очень странном поведении MySQL.

Предполагая, что у вас есть таблица с полем auto_increment и другое поле, которое установлено на уникальное (например, поле имени пользователя). При попытке вставить строку с именем пользователя, которое уже находится в таблице, вставка терпит неудачу, как и ожидалось. Тем не менее значение auto_increment увеличивается, как можно увидеть, когда вы вставляете действительную новую запись после нескольких неудачных попыток.

Например, когда наша последняя запись выглядит так...

ID: 10
Username: myname

... и мы попробуем пять новых записей с одинаковым значением имени пользователя в нашей следующей вставке, мы создадим новую строку следующим образом:

ID: 16
Username: mynewname

Хотя это не большая проблема сама по себе, кажется, что очень глупый вектор атаки убивает таблицу, наводя ее неудачными запросами на вставку, как указано в Справочном руководстве MySQL:

"Поведение механизма автоматического инкремента не определено, если [...] значение становится больше максимального значения, которое может быть сохранено в указанном целочисленном типе."

Является ли это ожидаемым поведением?

Ответ 1

InnoDB - это транзакционный движок.

Это означает, что в следующем сценарии:

Session A вставляет запись 1
Session B вставляет запись 2
Session A откат

существует либо возможность зазора, либо Session B блокируется до тех пор, пока Session A не вернется или не вернется.

InnoDB дизайнеры (как и большинство других разработчиков транзакционных движков) решили разрешить пробелы.

Из документация:

При обращении к счетчику автоматического нарастания InnoDB используется специальная блокировка AUTO-INC на уровне таблицы, которая сохраняется до конца текущего оператора SQL, а не до конца транзакции. Специальная стратегия блокировки была введена для улучшения concurrency для вставок в таблицу, содержащую столбец AUTO_INCREMENT

...

InnoDB использует счетчик автоинкремента в памяти при работе сервера. Когда сервер остановлен и перезагружен, InnoDB повторно инициализирует счетчик для каждой таблицы для первого INSERT к таблице, как описано выше.

Если вы боитесь обтекания столбца id, сделайте его BIGINT (длиной 8 байт).

Ответ 2

Не зная точную внутренность, я бы сказал, да, автоинкремент ДОЛЖЕН допускать, чтобы пропущенные значения делались с ошибками вставки. Допустим, вы делаете банковскую транзакцию или другие, где вся транзакция и несколько записей идут как все или ничего. Если вы попробуете свою вставку, получите идентификатор, затем отпечатайте все последующие данные с этим идентификатором транзакции и вставьте подробные записи, вы должны обеспечить свою квалифицированную уникальность. Если у вас много людей, которые захлопывают базу данных, им также нужно будет убедиться, что они получают свой собственный идентификатор транзакции, чтобы не конфликтовать с вашими, когда их транзакция будет совершена. Если что-то не срабатывает при первой транзакции, никакого вреда и никаких оборванных элементов ниже по течению.

Ответ 3

Я знаю, что это старая статья, но так как я тоже не мог найти правильный ответ, я действительно нашел способ сделать это. Вы должны обернуть свой запрос в оператор if. Его обычно вставляют запрос или вставляют и дублируют запросы, которые испортили организованный порядок автоматического приращения, поэтому для регулярных вставок используйте:

$check_email_address = //select query here\\

if ( $check_email_address == false ) {
    your query inside of here
}

а вместо INSERT AND ON DUPLICATE используйте UPDATE SET WHERE QUERY в или вне оператора if, и не имеет значения ЗАМЕНИТЬ В КВАЛИФИКАЦИЮ strong > и, похоже, работает