Почему автоинкремент 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 > и, похоже, работает