14.6.1.6 AUTO_INCREMENT Handling in InnoDB — MySQL 5.7

14.6.1.6 Обработка AUTO_INCREMENT в InnoDB

InnoDB предоставляет настраиваемый механизм блокировки, который может значительно улучшить масштабируемость и производительность операторов SQL, добавляющих строки в таблицы с AUTO_INCREMENT столбцами. Чтобы использовать механизм AUTO_INCREMENT с таблицей InnoDB, столбец AUTO_INCREMENT должен быть определен как первый или единственный столбец в некотором индексе таким образом, чтобы можно было выполнить эквивалентный индексированному SELECT MAX(ai_col) поиску в таблице для получения максимального значения столбца. Индекс не обязан быть PRIMARY KEY или UNIQUE, но для предотвращения дублирования значений в столбце AUTO_INCREMENT рекомендуется использовать именно эти типы индексов.

В этом разделе описаны режимы блокировки AUTO_INCREMENT, последствия использования различных настроек режима блокировки AUTO_INCREMENT и то, как InnoDB инициализирует счётчик AUTO_INCREMENT.

Режимы блокировки InnoDB AUTO_INCREMENT

В этом разделе описываются режимы блокировки AUTO_INCREMENT, используемые для генерации значений автоинкремента, и как каждый режим блокировки влияет на репликацию. Режим блокировки автоинкремента настраивается при запуске с помощью переменной innodb_autoinc_lock_mode.

В описании настроек innodb_autoinc_lock_mode используются следующие термины:

“INSERT-подобные” операторы

Все операторы, генерирующие новые строки в таблице, включая INSERT, INSERT ... SELECT, REPLACE, REPLACE ... SELECT и LOAD DATA. Включает “простые-вставки”, “массовые-вставки” и “смешанные-режимы” вставки.
“Простые вставки”

Операторы, для которых количество строк, подлежащих вставке, может быть определено заранее (при начальной обработке оператора). Это включает в себя однострочные и многострочные INSERT и REPLACE операторы, не содержащие вложенных подзапросов, но не INSERT ... ON DUPLICATE KEY UPDATE.
“Массовые вставки”

Операторы, для которых количество строк, подлежащих вставке (и количество необходимых значений автоинкремента), не известно заранее. Это включает INSERT ... SELECT, REPLACE ... SELECT и LOAD DATA операторы, но не обычный INSERT. InnoDB присваивает новые значения для столбца AUTO_INCREMENT по одному за раз по мере обработки каждой строки.
“Смешанные вставки”

Это “простые вставки” операторы, которые указывают значение автоинкремента для некоторых (но не всех) новых строк. Пример ниже, где c1 — это AUTO_INCREMENT столбец таблицы t1:
```
INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
```
Другой тип “смешанных вставок” — INSERT ... ON DUPLICATE KEY UPDATE, который в худшем случае является INSERT, за которым следует UPDATE, где выделенное значение для столбца AUTO_INCREMENT может или не может использоваться на фазе обновления.

Существует три возможных значения для переменной innodb_autoinc_lock_mode. Настройки — 0, 1 или 2, соответственно для “традиционного”, “последовательного” или “перемешанного” режима блокировки.

Режим блокировки по умолчанию (“традиционный” режим блокировки)

Традиционный режим блокировки обеспечивает такое же поведение, которое существовало до введения переменной innodb_autoinc_lock_mode. Вариант традиционной блокировки предоставляется для обратной совместимости, тестирования производительности и решения проблем с «смешанными вставками» из-за возможных различий в семантике.

В этом режиме блокировки все операторы “типа INSERT” получают специальную блокировку на уровне таблицы AUTO-INC для вставок в таблицы с AUTO_INCREMENT столбцами. Эта блокировка обычно удерживается до конца оператора (а не до конца транзакции), чтобы гарантировать, что значения автоинкремента назначаются в предсказуемом и повторяемом порядке для заданной последовательности операторов INSERT, и чтобы гарантировать, что значения автоинкремента, назначенные каким-либо оператором, являются последовательными.

В случае репликации на основе операторов это означает, что при репликации оператора SQL на сервере реплики используются те же значения для столбца автоинкремента, что и на исходном сервере. Результат выполнения нескольких операторов INSERT детерминирован, и реплика воспроизводит те же данные, что и на источнике. Если значения автоинкремента, сгенерированные несколькими операторами INSERT, были переплетены, результат выполнения двух одновременных операторов INSERT был бы недетерминированным и не мог бы надёжно распространяться на сервер реплики с использованием репликации на основе операторов.

Чтобы прояснить это, рассмотрим пример, использующий эту таблицу:
```
CREATE TABLE t1 (
  c1 INT(11) NOT NULL AUTO_INCREMENT,
  c2 VARCHAR(10) DEFAULT NULL,
  PRIMARY KEY (c1)
) ENGINE=InnoDB;
```
Предположим, что выполняются две транзакции, каждая из которых вставляет строки в таблицу со столбцом AUTO_INCREMENT. Одна транзакция использует оператор INSERT ... SELECT, который вставляет 1000 строк, а другая использует простой оператор INSERT, который вставляет одну строку:
```
Tx1: INSERT INTO t1 (c2) SELECT 1000 rows from another table ...
Tx2: INSERT INTO t1 (c2) VALUES ('xxx');
```
InnoDB не может заранее определить, сколько строк извлекается из SELECT в операторе INSERT в Tx1, и оно назначает значения автоинкремента по одному, по мере выполнения оператора. С блокировкой на уровне таблицы, удерживаемой до конца оператора, только один оператор INSERT, относящийся к таблице t1, может выполняться одновременно, и генерация номеров автоинкремента различными операторами не переплетается. Значения автоинкремента, сгенерированные оператором Tx1 INSERT ... SELECT, являются последовательными, а (единственное) значение автоинкремента, используемое оператором INSERT в Tx2, меньше или больше всех использованных для Tx1, в зависимости от того, какой оператор выполняется первым.

До тех пор, пока операторы SQL выполняются в том же порядке при повторном воспроизведении из двоичного журнала (при использовании репликации на основе операторов или в сценариях восстановления), результаты остаются такими же, как и при первом запуске Tx1 и Tx2. Таким образом, блокировки на уровне таблицы, удерживаемые до конца оператора, делают операторы INSERT с использованием автоинкремента безопасными для использования с репликацией на основе операторов. Однако эти блокировки на уровне таблицы ограничивают конкуретность и масштабируемость, когда несколько транзакций выполняют операторы вставки одновременно.

В предыдущем примере, если бы не было блокировки на уровне таблицы, значение столбца автоинкремента, используемого для INSERT в Tx2, зависело бы именно от того, когда оператор был выполнен. Если INSERT Tx2 выполняется, когда INSERT Tx1 выполняется (а не до начала или после завершения), конкретные значения автоинкремента, назначенные двумя операторами INSERT, являются недетерминированными и могут отличаться при каждом запуске.

В режиме блокировки последовательный InnoDB может избежать использования блокировок на уровне таблицы AUTO-INC для операторов “простой вставки”, где количество строк известно заранее, и при этом сохранить детерминированное выполнение и безопасность для репликации на основе операторов.

Если вы не используете двоичный журнал для повторного воспроизведения операторов SQL в рамках восстановления или репликации, можно использовать режим блокировки перемежающийся, чтобы полностью исключить использование блокировок на уровне таблицы AUTO-INC, для ещё большей конкуретности и производительности, ценой допустимости пропусков в номерах автоинкремента, назначенных оператором, и потенциальным переплетением номеров, назначаемых одновременно выполняющимися операторами.
Режим блокировки последовательный (“последовательный” режим блокировки)

Это режим блокировки по умолчанию. В этом режиме “массовые вставки” используют специальную блокировку на уровне таблицы AUTO-INC и удерживают её до конца оператора. Это относится ко всем операторам INSERT ... SELECT, REPLACE ... SELECT и LOAD DATA. Одновременно может выполняться только один оператор, удерживающий блокировку AUTO-INC. Если исходная таблица операции массовой вставки отличается от целевой таблицы, блокировка AUTO-INC на целевой таблице берется после того, как будет взята общая блокировка на первой строке, выбранной из исходной таблицы. Если исходная и целевая таблицы операции массовой вставки совпадают, блокировка AUTO-INC берется после того, как будут взяты общие блокировки на всех выбранных строках.

“Простые вставки” (для которых количество строк, подлежащих вставке, известно заранее) избегают блокировок на уровне таблицы AUTO-INC, получая необходимое количество значений автоинкремента под управлением мьютекса (лёгкой блокировки), который удерживается только в течение процесса выделения, а не до завершения оператора. Блокировка на уровне таблицы AUTO-INC используется только в том случае, если другая транзакция удерживает блокировку AUTO-INC. Если другая транзакция удерживает блокировку AUTO-INC, “простая вставка” ожидает блокировку AUTO-INC, как если бы это была “массовая вставка”.

Этот режим блокировки гарантирует, что при наличии операторов INSERT, где количество строк неизвестно заранее (и где номера автоинкремента назначаются по мере выполнения оператора), все номера автоинкремента, назначенные любым оператором “INSERT-подобный”, являются последовательными, и операции безопасны для репликации на основе операторов.

Проще говоря, этот режим блокировки значительно улучшает масштабируемость, будучи безопасным для использования с репликацией на основе операторов. Кроме того, как и в режиме “традиционной” блокировки, номера автоинкремента, назначенные любым данным оператором, являются последовательными. Семантика не изменяется по сравнению с режимом “традиционной” для любого оператора, использующего автоинкремент, за одним важным исключением.

Исключением является “смешанные вставки”, где пользователь предоставляет явные значения для столбца AUTO_INCREMENT для некоторых, но не для всех, строк в “простой вставке” с несколькими строками. Для таких вставок InnoDB выделяет больше номеров автоинкремента, чем количество строк, подлежащих вставке. Однако все автоматически назначенные значения генерируются последовательно (и, следовательно, выше) значения автоинкремента, сгенерированного последним выполненным предыдущим оператором. «Излишние» числа теряются.
Режим перемежающейся блокировки (“перемежающийся” режим блокировки)

В этом режиме блокировки ни один оператор “INSERT-подобный” не использует блокировку на уровне таблицы AUTO-INC, и несколько операторов могут выполняться одновременно. Это самый быстрый и масштабируемый режим блокировки, но он не безопасен при использовании репликации на основе операторов или сценариев восстановления, когда операторы SQL воспроизводятся из двоичного журнала.

В этом режиме блокировки гарантируются уникальность и монотонное возрастание значений автоинкремента по всем одновременно выполняющимся операторам “INSERT-подобный”. Однако, поскольку несколько операторов могут одновременно генерировать числа (то есть, выделение чисел перемежается между операторами), значения, сгенерированные для строк, вставленных каждым оператором, могут быть не последовательными.

Если выполняются только операторы “простые вставки”, где количество строк, подлежащих вставке, известно заранее, в номерах, сгенерированных для одного оператора, нет пробелов, за исключением “смешанных вставок”. Однако, когда выполняются “массовые вставки”, в значениях автоинкремента, назначенных любым данным оператором, могут быть пробелы.

Последствия использования режима блокировки AUTO_INCREMENT в InnoDB

Использование автоинкремента с репликацией

Если вы используете репликацию на основе операторов, установите innodb_autoinc_lock_mode в 0 или 1 и используйте то же значение на источнике и его репликах. Значения автоинкремента не гарантируются одинаковыми на репликах и на источнике, если используется innodb_autoinc_lock_mode = 2 («“интерлированный”») или при конфигурации, где источник и реплики не используют один и тот же режим блокировки.

Если вы используете репликацию на основе строк или смешанный формат репликации, все режимы блокировки автоинкремента безопасны, так как репликация на основе строк не чувствительна к порядку выполнения SQL-запросов (а смешанный формат использует репликацию на основе строк для любых запросов, которые небезопасны для репликации на основе операторов).
«Потерянные» значения автоинкремента и разрывы последовательности

Во всех режимах блокировки (0, 1 и 2), если транзакция, сгенерировавшая значения автоинкремента, откатывается, эти значения автоинкремента «теряются». После генерации значения для столбца автоинкремента, оно не может быть отменено, независимо от того, завершен ли оператор типа «INSERT-подобный» и независимо от того, откатывается ли содержащая транзакция. Такие потерянные значения не переиспользуются. Таким образом, могут быть разрывы в значениях, хранящихся в столбце AUTO_INCREMENT таблицы.
Указание NULL или 0 для столбца AUTO_INCREMENT

Во всех режимах блокировки (0, 1 и 2), если пользователь указывает NULL или 0 для столбца AUTO_INCREMENT в операторе INSERT, InnoDB обрабатывает строку так, как если бы значение не было указано, и генерирует для него новое значение.
Присвоение отрицательного значения столбцу AUTO_INCREMENT

Во всех режимах блокировки (0, 1 и 2), поведение механизма автоинкремента неопределено, если вы присваиваете отрицательное значение столбцу AUTO_INCREMENT.
Если значение AUTO_INCREMENT становится больше максимального целого для заданного целочисленного типа

Во всех режимах блокировки (0, 1 и 2), поведение механизма автоинкремента неопределено, если значение становится больше максимального целого, которое может быть сохранено в указанном целочисленном типе.
Разрывы в значениях автоинкремента для «массовых вставок»

При innodb_autoinc_lock_mode установленном в 0 («традиционный») или 1 («последовательный»), значения автоинкремента, сгенерированные данным оператором, являются последовательными, без разрывов, потому что блокировка на уровне таблицы AUTO-INC удерживается до конца оператора, и только один такой оператор может выполняться одновременно.

При innodb_autoinc_lock_mode установленном в 2 («интерлированный»), могут быть разрывы в значениях автоинкремента, сгенерированных «массовыми вставками», но только если одновременно выполняются операторы типа «INSERT-подобные».

Для режимов блокировки 1 или 2 разрывы могут возникать между последовательными операторами, потому что для массовых вставок точное количество значений автоинкремента, требуемых каждым оператором, может быть неизвестно, и возможна переоценка.
Значения автоинкремента, назначенные «смешанными вставками»

Рассмотрим «смешанную вставку», где «простая вставка» указывает значение автоинкремента для некоторых (но не всех) получившихся строк. Такой оператор ведет себя по-разному в режимах блокировки 0, 1 и 2. Например, предположим, что c1 это столбец AUTO_INCREMENT таблицы t1, и что последнее автоматически сгенерированное порядковое число равно 100.
```
mysql> CREATE TABLE t1 (
    -> c1 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    -> c2 CHAR(1)
    -> ) ENGINE = INNODB;
```
Теперь рассмотрим следующий оператор «смешанной вставки»:
```
mysql> INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
```
При innodb_autoinc_lock_mode установленном в 0 («традиционный»), четыре новые строки будут:
```
mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
| 101 | b    |
|   5 | c    |
| 102 | d    |
+-----+------+
```
Следующее доступное значение автоинкремента равно 103, потому что значения автоинкремента выделяются по одному, а не все сразу в начале выполнения оператора. Этот результат справедлив независимо от того, существуют ли одновременно выполняющиеся операторы типа «INSERT-подобные» (любого типа).

При innodb_autoinc_lock_mode установленном в 1 («последовательный»), четыре новые строки также:
```
mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
| 101 | b    |
|   5 | c    |
| 102 | d    |
+-----+------+
```
Однако в этом случае следующее доступное значение автоинкремента равно 105, а не 103, потому что четыре значения автоинкремента выделяются во время обработки оператора, но используются только два. Этот результат справедлив независимо от того, существуют ли одновременно выполняющиеся операторы типа «INSERT-подобные» (любого типа).

При innodb_autoinc_lock_mode установленном в 2 («интерлированный»), четыре новые строки:
```
mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
|   x | b    |
|   5 | c    |
|   y | d    |
+-----+------+
```
Значения x и y уникальны и больше, чем любые ранее сгенерированные строки. Однако конкретные значения x и y зависят от количества значений автоинкремента, генерируемых одновременно выполняемыми операторами.

Наконец, рассмотрим следующий оператор, выпущенный, когда последнее сгенерированное порядковое число равно 100:
```
mysql> INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (101,'c'), (NULL,'d');
```
При любом значении innodb_autoinc_lock_mode этот оператор генерирует ошибку дублирования ключа 23000 (Can't write; duplicate key in table), потому что 101 выделяется для строки (NULL, 'b'), а вставка строки (101, 'c') терпит неудачу.
Изменение значений столбца AUTO_INCREMENT посреди последовательности операторов INSERT

Во всех режимах блокировки (0, 1 и 2), изменение значения столбца AUTO_INCREMENT посреди последовательности операторов INSERT может привести к ошибкам «Дубликат записи». Например, если вы выполняете операцию UPDATE, которая изменяет значение столбца AUTO_INCREMENT на значение, большее текущего максимального значения автоинкремента, последующие операторы INSERT, которые не указывают неиспользуемое значение автоинкремента, могут столкнуться с ошибками «Дубликат записи». Это поведение продемонстрировано в следующем примере.
```
mysql> CREATE TABLE t1 (
    -> c1 INT NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (c1)
    ->  ) ENGINE = InnoDB;

mysql> INSERT INTO t1 VALUES(0), (0), (3);

mysql> SELECT c1 FROM t1;
+----+
| c1 |
+----+
|  1 |
|  2 |
|  3 |
+----+

mysql> UPDATE t1 SET c1 = 4 WHERE c1 = 1;

mysql> SELECT c1 FROM t1;
+----+
| c1 |
+----+
|  2 |
|  3 |
|  4 |
+----+

mysql> INSERT INTO t1 VALUES(0);
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'
```

Инициализация счётчика InnoDB AUTO_INCREMENT

В этом разделе описывается, как InnoDB инициализирует счётчики AUTO_INCREMENT.

Если вы указываете столбец AUTO_INCREMENT для таблицы InnoDB, обработчик таблицы в словаре данных InnoDB содержит специальный счётчик, называемый счётчиком автоинкремента, который используется для присвоения новых значений столбцу. Этот счётчик хранится только в оперативной памяти, а не на диске.

Для инициализации счётчика автоинкремента после перезапуска сервера InnoDB выполняет эквивалент следующего оператора при первом вставлении в таблицу, содержащую столбец AUTO_INCREMENT.

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

InnoDB увеличивает значение, полученное оператором, и присваивает его столбцу и счётчику автоинкремента для таблицы. По умолчанию значение увеличивается на 1. Это значение по умолчанию можно изменить параметром конфигурации auto_increment_increment.

Если таблица пуста, InnoDB использует значение 1. Это значение по умолчанию можно изменить параметром конфигурации auto_increment_offset.

Если оператор SHOW TABLE STATUS проверяет таблицу до инициализации счётчика автоинкремента, InnoDB инициализирует, но не увеличивает значение. Значение сохраняется для использования при последующих вставках. Эта инициализация использует обычное эксклюзивное блокирующее чтение таблицы, и блокировка действует до конца транзакции. InnoDB выполняет ту же процедуру для инициализации счётчика автоинкремента для вновь созданной таблицы.

После инициализации счётчика автоинкремента, если вы не явно не указываете значение для столбца AUTO_INCREMENT, InnoDB увеличивает счётчик и присваивает новое значение столбцу. Если вы вставляете строку, явно задавая значение столбца, и это значение больше текущего значения счётчика, счётчик устанавливается в указанное значение столбца.

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

Перезапуск сервера также отменяет действие опции таблицы AUTO_INCREMENT = N в операторах CREATE TABLE и ALTER TABLE, которые можно использовать с таблицами InnoDB для установки начального значения счётчика или изменения текущего значения счётчика.

Примечания

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