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

Question

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

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

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

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

ID: 10

Username: myname

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

ID: 16

Username: mynewname

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

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

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

727 4

mysql innodb auto-increment

4 ответов:

Comments

Ничего не найдено.

Quassnoi · Accepted Answer · 2010-05-07 14:02:44

InnoDB это транзакционный двигатель.

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

Session A вставляет запись 1

Session B вставляет запись 2

Session A откат

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

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

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

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

...

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

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

DRapp · Accepted Answer · 2010-05-07 13:55:08

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

DavidA · Accepted Answer · 2018-06-05 12:49:36

Старый пост, но это может помочь людям, Возможно, вам придется установить innodb_autoinc_lock_mode в 0 или 2 .
Системные переменные, принимающие числовое значение, могут быть заданы как --var_name=value в командной строке или как var_name=value в файлах опций.
Формат параметров командной строки:
--innodb-autoinc-lock-mode=0 
Или Откройте консоль MySQL.ini и добавить следующую строку:
innodb_autoinc_lock_mode=0

ThinkkSo · Accepted Answer · 2014-06-24 10:01:20

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

if ( $check_email_address == false ) {
    your query inside of here
}
И вместо вставить и на дубликат Используйте a обновить запрос SET WHERE В или вне утверждения if не имеет значения и A заменить в запрос также, кажется, работает