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

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

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

Переменная системы innodb_autoinc_lock_mode может быть удалена в будущих версиях. Подробности см. в MDEV-19577.

Традиционный режим блокировки

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

В этом режиме InnoDB удерживает блокировку на уровне таблицы для всех инструкций INSERT до завершения инструкции.

Режим блокировки последовательности

Когда innodb_autoinc_lock_mode установлено в 1, InnoDB использует режим блокировки последовательности.

В этом режиме InnoDB удерживает блокировку на уровне таблицы для всех массовых инструкций INSERT (таких как LOAD DATA или INSERT ... SELECT) до конца инструкции. Для простых инструкций INSERT блокировка на уровне таблицы не используется. Вместо этого используется облегчённый мьютекс, который масштабируется значительно лучше. Это значение по умолчанию.

Режим перекрёстной блокировки

Когда innodb_autoinc_lock_mode установлено в 2, InnoDB использует режим перекрёстной блокировки.

В этом режиме InnoDB вообще не удерживает блокировки на уровне таблицы. Это самый быстрый и масштабируемый режим, но он не подходит для репликации на основе инструкций statement-based.

Установка значений AUTO_INCREMENT

Значение AUTO_INCREMENT для таблицы InnoDB можно установить, выполнив инструкцию ALTER TABLE и указав опцию AUTO_INCREMENT таблицы. Например:

ALTER TABLE tab AUTO_INCREMENT=100;

Однако, в MariaDB 10.2.3 и ранее InnoDB хранил счётчик AUTO_INCREMENT таблицы в оперативной памяти. В этих версиях при перезапуске сервера счётчик повторно инициализируется с наибольшим найденным в таблице значением. Это означает, что вышеуказанная операция может быть отменена, если сервер перезагрузится до записи каких-либо строк в таблицу.

В MariaDB 10.2.4 и более поздних версиях счётчик AUTO_INCREMENT является постоянным, поэтому это ограничение больше не актуально. Однако постоянство не означает транзакционность. Разрывы могут всё ещё возникать в некоторых случаях, таких как неудачная инструкция INSERT IGNORE, или если пользователь выполняет ROLLBACK или ROLLBACK TO SAVEPOINT.

Например:

CREATE TABLE t1 (pk INT AUTO_INCREMENT PRIMARY KEY, i INT, UNIQUE (i)) ENGINE=InnoDB;

INSERT INTO t1 (i) VALUES (1),(2),(3);
INSERT IGNORE INTO t1 (pk, i) VALUES (100,1);
Query OK, 0 rows affected, 1 warning (0.099 sec)

SELECT * FROM t1;
+----+------+
| pk | i    |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+

SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `i` int(11) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  UNIQUE KEY `i` (`i`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

Если сервер перезагрузится на этом этапе, счётчик AUTO_INCREMENT вернётся к 101, что является постоянным значением, установленным в рамках неудачной инструкции INSERT IGNORE.

# Restart server
SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `i` int(11) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  UNIQUE KEY `i` (`i`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=latin1

См. также

• AUTO_INCREMENT
• Вопросы и ответы по AUTO_INCREMENT
• LAST_INSERT_ID
• Последовательности — альтернатива auto_increment, доступная начиная с MariaDB 10.3