Mysql 自增锁(5.7)

最新推荐文章于 2024-08-18 17:19:51 发布

渔夫数据库笔记

最新推荐文章于 2024-08-18 17:19:51 发布

阅读量1k

点赞数

分类专栏： Mysql 文章标签：自增锁 auto_increment

Mysql 专栏收录该内容

220 篇文章 9 订阅

订阅专栏

##下面内容来自Mysql 5.7 官方文档

AUTO_INCREMENT Handling in InnoDB

InnoDB provides a configurable locking mechanism that can significantly improve scalability and performance of SQL statements that add rows to tables with AUTO_INCREMENT columns. To use the AUTO_INCREMENT mechanism with an InnoDB table, an AUTO_INCREMENT column must be defined as part of an index such that it is possible to perform the equivalent of an indexed SELECT MAX(ai_col) lookup on the table to obtain the maximum column value. Typically, this is achieved by making the column the first column of some table index.

# 可以通过 innodb_autoinc_lock_mode 参数来调整自增锁的实现方式，以此来调整数据库的并发性能。使用了 auto_increment 的列必须创建索引(如果是组合索引，自增列必须是最左前缀)，因为mysql 可能需要通过 select max(自增列) 来确定当前表自增列的最大值(因为mysql 5.7及之前版本表的自增值是保存在内存中的，数据库重启后需要通过 select max(自增列)，来确定当前表的自增值)

This section describes the behavior of AUTO_INCREMENT lock modes, usage implications for different AUTO_INCREMENT lock mode settings, and how InnoDB initializes the AUTO_INCREMENT counter.

InnoDB AUTO_INCREMENT Lock Modes

This section describes the behavior of AUTO_INCREMENT lock modes used to generate auto-increment values, and how each lock mode affects replication. Auto-increment lock modes are configured at startup using the innodb_autoinc_lock_mode configuration parameter.#

The following terms are used in describing innodb_autoinc_lock_mode settings: # 下面是一些理解自增锁会用到的术语

“INSERT-like” statements

All statements that generate new rows in a table, including INSERT, INSERT ... SELECT, REPLACE, REPLACE ... SELECT, and LOAD DATA. Includes “simple-inserts”, “bulk-inserts”, and “mixed-mode” inserts. # "INSERT-like" statements 是指会在表中产生新的记录的语句，包括 insert，insert ... select，replace，replace ... select，load data等等
“Simple inserts”

Statements for which the number of rows to be inserted can be determined in advance (when the statement is initially processed). This includes single-row and multiple-row INSERT and REPLACE statements that do not have a nested subquery, but not INSERT ... ON DUPLICATE KEY UPDATE.# "Simple inserts" 是指在插入前可以确定会插入多少行的语句。比如单行或者多行 insert操作以及不带子查询的 replace 语句。NOTE: INSERT ... ON DUPLICATE KEY UPDATE 不属于"Simple inserts"。
“Bulk inserts”

Statements for which the number of rows to be inserted (and the number of required auto-increment values) is not known in advance. This includes INSERT ... SELECT, REPLACE ... SELECT, and LOAD DATA statements, but not plain INSERT. InnoDB assigns new values for the AUTO_INCREMENT column one at a time as each row is processed. #"Bulk inserts" 是指在插入前不知道具体会插入行数的sql(也就不知道需要申请多少 auto_increment)。比如 insert ... select，replace ... select 以及 load data 语句。每处理一行，innodb会为该行分配一个自增值
“Mixed-mode inserts”

These are “simple insert” statements that specify the auto-increment value for some (but not all) of the new rows. An example follows, where c1 is an AUTO_INCREMENT column of table t1: # 第一类的 "Mixed-mode inserts" 是指一个 simple insert 给某些插入的行指定了自增值，而某些没指定，例子如下：
```
INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
```
Another type of “mixed-mode insert” is INSERT ... ON DUPLICATE KEY UPDATE, which in the worst case is in effect an INSERT followed by a UPDATE, where the allocated value for the AUTO_INCREMENT column may or may not be used during the update phase. # 另一类的"Mixed-mode inserts" 是INSERT ... ON DUPLICATE KEY UPDATE. 最槽糕的情况是申请的自增值在update时不会被使用

There are three possible settings for the innodb_autoinc_lock_mode configuration parameter. The settings are 0, 1, or 2, for“traditional”, “consecutive”, or “interleaved” lock mode, respectively. # innodb_autoinc_lock_mode 参数可以被设置为 0，1，2(Mysql 5.6及5.7 中该参数默认值为1，Mysql 5.8 版本中该参数默认值为2)

innodb_autoinc_lock_mode = 0 (“traditional” lock mode)

The traditional lock mode provides the same behavior that existed before the innodb_autoinc_lock_mode configuration parameter was introduced in MySQL 5.1. The traditional lock mode option is provided for backward compatibility, performance testing, and working around issues with “mixed-mode inserts”, due to possible differences in semantics. # 设置为0,自增锁实现方式同未出现该参数时一致，该模式主要是为了向前兼容，性能测试，以及处理语义不一致问题。

In this lock mode, all “INSERT-like” statements obtain a special table-level AUTO-INC lock for inserts into tables with AUTO_INCREMENT columns. This lock is normally held to the end of the statement (not to the end of the transaction) to ensure that auto-increment values are assigned in a predictable and repeatable order for a given sequence of INSERTstatements, and to ensure that auto-increment values assigned by any given statement are consecutive. # 在该种模式下所有的 "INSERT-like" 语句在操作包含auto_increment列的表时持有一种特殊的表锁(AUTO-INC lock)，该锁在语句结束时释放，而不是在事务结束时释放，这样能保证先执行的语句先获得自增值，且每个语句获得的自增值是连续的。

In the case of statement-based replication, this means that when an SQL statement is replicated on a slave server, the same values are used for the auto-increment column as on the master server. The result of execution of multiple INSERTstatements is deterministic, and the slave reproduces the same data as on the master. If auto-increment values generated by multiple INSERT statements were interleaved, the result of two concurrent INSERT statements would be nondeterministic, and could not reliably be propagated to a slave server using statement-based replication. # 如果使用了 SBR 格式的复制，这就意味着该模式下从库上该语句产生的自增值同主库上一致(因为是表级锁，先执行的语句先获得锁，执行完成释放)，当然这有个前提从库上和主库上数据是一致的。

To make this clear, consider an example that uses this table: # 为了解释的更清楚，请看下面的例子
```
CREATE TABLE t1 (
  c1 INT(11) NOT NULL AUTO_INCREMENT,
  c2 VARCHAR(10) DEFAULT NULL,
  PRIMARY KEY (c1)
) ENGINE=InnoDB;
```
Suppose that there are two transactions running, each inserting rows into a table with an AUTO_INCREMENT column. One transaction is using an INSERT ... SELECT statement that inserts 1000 rows, and another is using a simple INSERTstatement that inserts one row: # 假设现在有两个事务同时在执行，一个事务通过 insert ... select 向表中插入 1000 行数据，另一个通过 simple insert 向表中插入一行数据
```
Tx1: INSERT INTO t1 (c2) SELECT 1000 rows from another table ...
Tx2: INSERT INTO t1 (c2) VALUES ('xxx');
```
InnoDB cannot tell in advance how many rows are retrieved from the SELECT in the INSERT statement in Tx1, and it assigns the auto-increment values one at a time as the statement proceeds. With a table-level lock, held to the end of the statement, only one INSERT statement referring to table t1 can execute at a time, and the generation of auto-increment numbers by different statements is not interleaved. The auto-increment value generated by the Tx1 INSERT ... SELECT statement is consecutive, and the (single) auto-increment value used by the INSERT statement in Tx2 is either be smaller or larger than all those used for Tx1, depending on which statement executes first. # 对于第一个事务 innodb 在执行之前无法知道需要分配多少个自增值，它是每处理一行分配一个自增值。通过表级别的自增锁(AUTO-INC lock)，使用表级别的自增锁(语句执行完释放)，对于每张表同时只能有一个insert语句对其进行操作，所以多个语句向同一个表中插入数据不会出现自增值交叉的现象。上面的例子中事务1中插入1000条数据产生的自增值是连续的。而第二个事务中 simple insert 语句产生的自增值要小于或者大于事务1中产生的所有自增值，至于是大于还是小于，这取决于事务1 和事务2 中语句到底哪个先执行(哪个语句先获得 AUTO-INC lock)

As long as the SQL statements execute in the same order when replayed from the binary log (when using statement-based replication, or in recovery scenarios), the results are the same as they were when Tx1 and Tx2 first ran. Thus, table-level locks held until the end of a statement make INSERT statements using auto-increment safe for use with statement-based replication. However, those table-level locks limit concurrency and scalability when multiple transactions are executing insert statements at the same time. # 在 innodb_autoinc_lock_mode =0 模式下能够保证 SBR 复制的正确性，但是该模式会影响表的并发写入，因为是表级别的锁，所以同一时间只能有一个insert 语句对该表操作

In the preceding example, if there were no table-level lock, the value of the auto-increment column used for the INSERT in Tx2 depends on precisely when the statement executes. If the INSERT of Tx2 executes while the INSERT of Tx1 is running (rather than before it starts or after it completes), the specific auto-increment values assigned by the two INSERTstatements are nondeterministic, and may vary from run to run. # 如果没有表级别的自增锁，那么如果上面例子中两个sql并发执行(事务2中的sql在事务1中sql开始后结束前的任意时间执行)，可能每次执行两个事务获得的自增值都会不一样。(这种情况下使用SBR 复制是不安全的，可能造成主从数据不一致)

Under the consecutive lock mode, InnoDB can avoid using table-level AUTO-INC locks for “simple insert” statements where the number of rows is known in advance, and still preserve deterministic execution and safety for statement-based replication. # 在 consecutive 模式下(即 innodb_autoinc_lock_mode=1)，对于 "simple insert" innodb 避免使用表级别自增锁，而对于 "Bulk inserts" 及 "Mixed-mode inserts" 等无法预先知道需要处理行数的语句还是使用表级别自增锁，所以 consecutive 模式下使用 SBR隔离级别也是安全的

If you are not using the binary log to replay SQL statements as part of recovery or replication, the interleaved lock mode can be used to eliminate all use of table-level AUTO-INC locks for even greater concurrency and performance, at the cost of permitting gaps in auto-increment numbers assigned by a statement and potentially having the numbers assigned by concurrently executing statements interleaved. # 如果你使用了RBR 复制那么你可以把 innodb_autoinc_lock_mode=2 提高并发性能，代价是允许语句分配的自动递增数字之间存在间隙，并且并发执行的insert 语句可能会出现自增值交叉(模式为0 或者 1时，对于"Bulk inserts"语句来说分配的自增值是连续的，多个并发语句申请的自增值不会出现交叉)
innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)

This is the default lock mode. In this mode, “bulk inserts” use the special AUTO-INC table-level lock and hold it until the end of the statement. This applies to all INSERT ... SELECT, REPLACE ... SELECT, and LOAD DATA statements. Only one statement holding the AUTO-INC lock can execute at a time. If the source table of the bulk insert operation is different from the target table, the AUTO-INC lock on the target table is taken after a shared lock is taken on the first row selected from the source table. If the source and target of the bulk insert operation are the same table, the AUTO-INC lock is taken after shared locks are taken on all selected rows. # Mysql 5.7 中 innodb_autoinc_lock_mode 默认值为1，在该模式下 "Bulk inserts" 操作会持有特殊表级别自增锁。因此同一时间每张表只能有一个"Bulk inserts"操作。对于 insert ... select 类似操作如果插入和查询的是不同表的话，加锁逻辑是这样的，对select 表中第一个记录加上共享锁后，就会对插入表加上表级别的自增锁。如果插入表和查询表是同一张表的话，会对查询表中所有匹配的记录加上共享锁后，再对插入表加上表级别的自增锁。

“Simple inserts” (for which the number of rows to be inserted is known in advance) avoid table-level AUTO-INC locks by obtaining the required number of auto-increment values under the control of a mutex (a light-weight lock) that is only held for the duration of the allocation process, not until the statement completes. No table-level AUTO-INC lock is used unless an AUTO-INC lock is held by another transaction. If another transaction holds an AUTO-INC lock, a “simple insert” waits for the AUTO-INC lock, as if it were a “bulk insert”. # 该模式下 "Simple inserts" 持有的是轻量级互斥量(可以理解为有两种类型的自增锁，1种是表级别自增锁，另一种是轻量级互斥量)。对于轻量级互斥量来说，不是在语句执行的整个阶段持有，而是在分配自增id时持有。对于 "Simple inserts" 来说通常不会使用表级别自增锁，只有在另一个语句持有表级别自增锁时，后面发起的 "Simple inserts" 语句会等待表级别的自增列锁。

This lock mode ensures that, in the presence of INSERT statements where the number of rows is not known in advance (and where auto-increment numbers are assigned as the statement progresses), all auto-increment values assigned by any “INSERT-like” statement are consecutive, and operations are safe for statement-based replication. # 该模式下能够保证 "INSERT-like" 每个语句产生的自增值是连续的， SBR 复制模式下使用该模式是安全的。

Simply put, this lock mode significantly improves scalability while being safe for use with statement-based replication. Further, as with “traditional” lock mode, auto-increment numbers assigned by any given statement are consecutive. There is no change in semantics compared to “traditional” mode for any statement that uses auto-increment, with one important exception.

The exception is for “mixed-mode inserts”, where the user provides explicit values for an AUTO_INCREMENT column for some, but not all, rows in a multiple-row “simple insert”. For such inserts, InnoDB allocates more auto-increment values than the number of rows to be inserted. However, all values automatically assigned are consecutively generated (and thus higher than) the auto-increment value generated by the most recently executed previous statement. “Excess”numbers are lost. # 简单来说，该模式中自增锁模式能够显著提高并发性，同时也能保证 SBR 复制模式下数据安全。并且每个 insert 语句申请到的自增值都是自增连续，但是语句之间申请的自增值可能出现gap(bulk insert 同 simple insert 之间)。
innodb_autoinc_lock_mode = 2 (“interleaved” lock mode)

In this lock mode, no “INSERT-like” statements use the table-level AUTO-INC lock, and multiple statements can execute at the same time. This is the fastest and most scalable lock mode, but it is not safe when using statement-based replication or recovery scenarios when SQL statements are replayed from the binary log. # 在interleaved 模式下 "INSERT-like" 语句自增锁都使用轻量级互斥量。这时最快并发度最高的模式(针对 0 和 1来说)。如果使用了 SBR 模式的复制，或者是用 statement 格式的binlog 来恢复数据库， interleaved 这种模式是不安全的。

In this lock mode, auto-increment values are guaranteed to be unique and monotonically increasing across all concurrently executing “INSERT-like” statements. However, because multiple statements can be generating numbers at the same time (that is, allocation of numbers is interleaved across statements), the values generated for the rows inserted by any given statement may not be consecutive. # 在 interleaved 模式下能够保证多个并发 "INSERT-like" 语句申请的自增值是唯一的，但是无法保证一个语句申请到的自增值是连续的.

If the only statements executing are “simple inserts” where the number of rows to be inserted is known ahead of time, there are no gaps in the numbers generated for a single statement, except for “mixed-mode inserts”. However, when “bulk inserts” are executed, there may be gaps in the auto-increment values assigned by any given statement. # 如果并发执行的都是"Simple inserts" 语句，那么每个语句申请到的自增值是连续的(该种类型的语句在执行前就知道需要申请多少个自增值)。如果并发执行的是 "Bulk inserts" 语句则每个语句申请到的自增值有可能是不连续的。 NOTE:在innodb_autoinc_lock_mode值为0和非0(1或者2)时"Bulk insert"操作自增值分配策略也是不同的，在该参数为0时，是每处理一行分配一个自增值，所以不会产生间隙。而在该参数为非0时，"Bulk insert"语句执行过程中第一次申请自增值会分配一个，第一次分配的用完以后，第二次申请分配2个，第二次申请的用完以后，同一个语句第三次申请分配4个。依此类推，同一个语句去申请自增 id，每次申请到的自增 id 个数都是上一次的两倍

InnoDB AUTO_INCREMENT Lock Mode Usage Implications

Using auto-increment with replication

If you are using statement-based replication, set innodb_autoinc_lock_mode to 0 or 1 and use the same value on the master and its slaves. Auto-increment values are not ensured to be the same on the slaves as on the master if you use innodb_autoinc_lock_mode = 2 (“interleaved”) or configurations where the master and slaves do not use the same lock mode. # 如果使用了 SBR 复制模式，需要把主从实例 innodb_autoinc_lock_mode 都设置成 0或者1. 如果你设置为2，则有可能出现主从实例上生成的自增值不一致的情况。

If you are using row-based or mixed-format replication, all of the auto-increment lock modes are safe, since row-based replication is not sensitive to the order of execution of the SQL statements (and the mixed format uses row-based replication for any statements that are unsafe for statement-based replication). # 如果你主库binlog 使用的是row或者 mixed格式，则 innodb_autoinc_lock_mode 设置成 0,1,2 都是安全的。
“Lost” auto-increment values and sequence gaps

In all lock modes (0, 1, and 2), if a transaction that generated auto-increment values rolls back, those auto-increment values are “lost”. Once a value is generated for an auto-increment column, it cannot be rolled back, whether or not the“INSERT-like” statement is completed, and whether or not the containing transaction is rolled back. Such lost values are not reused. Thus, there may be gaps in the values stored in an AUTO_INCREMENT column of a table. # 不论innodb_autoinc_lock_mode被设置成那种模式，如果一个已经申请到了自增值的语句被回滚，那么它申请到的自增值会被废弃，不会再被使用，这时自增值就产生了间隙(事务被回滚，但是事务中已经申请的自增值不会被回滚，而是被丢弃，其他语句被丢弃之后的值开始申请)。
Specifying NULL or 0 for the AUTO_INCREMENT column

In all lock modes (0, 1, and 2), if a user specifies NULL or 0 for the AUTO_INCREMENT column in an INSERT, InnoDB treats the row as if the value was not specified and generates a new value for it. # 不论innodb_autoinc_lock_mode设置为何种模式，当你插入时给自增列指定 null 或者 0 值时，都会被当做未指定自增列一样来处理(即自动生成自增值)
Assigning a negative value to the AUTO_INCREMENT column

In all lock modes (0, 1, and 2), the behavior of the auto-increment mechanism is not defined if you assign a negative value to the AUTO_INCREMENT column. # 不论innodb_autoinc_lock_mode设置为何种模式，如果在插入时给自增列指定一个负值，则直接插入指定的负值
If the AUTO_INCREMENT value becomes larger than the maximum integer for the specified integer type

In all lock modes (0, 1, and 2), the behavior of the auto-increment mechanism is not defined if the value becomes larger than the maximum integer that can be stored in the specified integer type. # 不论innodb_autoinc_lock_mode设置为何种模式，如果插入时给自增列指定一个大于表当前自增值的值，则直接插入该值，并且表的自增值变为指定值+1。
Gaps in auto-increment values for “bulk inserts”

With innodb_autoinc_lock_mode set to 0 (“traditional”) or 1 (“consecutive”), the auto-increment values generated by any given statement are consecutive, without gaps, because the table-level AUTO-INC lock is held until the end of the statement, and only one such statement can execute at a time. # 当innodb_autoinc_lock_mode设置为0或者1时，一个 "bulk inserts"语句产生的自增值都是连续的，不会出现间隙。

With innodb_autoinc_lock_mode set to 2 (“interleaved”), there may be gaps in the auto-increment values generated by “bulk inserts,” but only if there are concurrently executing “INSERT-like” statements. # 当innodb_autoinc_lock_mode设置为2时，当执行"bulk inserts"时，如果同时执行了其他"INSERT-like"语句，则"INSERT-like"产生的自增值可能会产生间隙。

For lock modes 1 or 2, gaps may occur between successive statements because for bulk inserts the exact number of auto-increment values required by each statement may not be known and overestimation is possible. #
Auto-increment values assigned by “mixed-mode inserts” # "mixed-mode inserts" 类型插入语句自增值怎么分配？

Consider a “mixed-mode insert,” where a “simple insert” specifies the auto-increment value for some (but not all) resulting rows. Such a statement behaves differently in lock modes 0, 1, and 2. For example, assume c1 is anAUTO_INCREMENT column of table t1, and that the most recent automatically generated sequence number is 100. # 对于 "mixed-mode inserts" 类型插入语句，对于不同的innodb_autoinc_lock_mode设置，分配方式表现的不一样。下面列子中有一张表 c1列为自增主键，并且表最近生成的自增值为100
```
mysql> CREATE TABLE t1 (
    -> c1 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    -> c2 CHAR(1)
    -> ) ENGINE = INNODB;
```
Now, consider the following “mixed-mode insert” statement: # 考虑对于如下 "mixed-mode insert" 语句在不同innodb_autoinc_lock_mode 模式下，会分别怎么分配自增值。
```
mysql> INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
```
With innodb_autoinc_lock_mode set to 0 (“traditional”), the four new rows are: # 当innodb_autoinc_lock_mode参数设置为0时，上面语句执行后，表中如下，并且 show create table 看到下一个可以自增值为103
```
mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
| 101 | b    |
|   5 | c    |
| 102 | d    |
+-----+------+
```
The next available auto-increment value is 103 because the auto-increment values are allocated one at a time, not all at once at the beginning of statement execution. This result is true whether or not there are concurrently executing“INSERT-like” statements (of any type). # 在执行上面的"mixed-mode insert"，不论是否有其他"INSERT-like"语句在并发执行，都能保证类似上面的执行结果。(因为在参数设置为0时，所有"INSERT-like" 语句执行时获取的时表级别的自增锁，直到语句执行完后释放)

With innodb_autoinc_lock_mode set to 1 (“consecutive”), the four new rows are also: # 当innodb_autoinc_lock_mode参数设置为1时，上面语句执行后，表中如下，并且show create table 看到下一个可用的自增值为105
```
mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
| 101 | b    |
|   5 | c    |
| 102 | d    |
+-----+------+
```
However, in this case, the next available auto-increment value is 105, not 103 because four auto-increment values are allocated at the time the statement is processed, but only two are used. This result is true whether or not there are concurrently executing “INSERT-like” statements (of any type). # 下一个可用的自增值为105，而不是103，因为一次性申请了4个自增值，但是只使用了两个。不论是否有其他"INSERT-like"语句在并发执行，都能保证类似上面的执行结果。

With innodb_autoinc_lock_mode set to mode 2 (“interleaved”), the four new rows are: # 当innodb_autoinc_lock_mode参数设置为2时，上面语句执行后，表中如下，
```
mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
|   x | b    |
|   5 | c    |
|   y | d    |
+-----+------+
```
The values of x and y are unique and larger than any previously generated rows. However, the specific values of x and y depend on the number of auto-increment values generated by concurrently executing statements. # 这时如果有其他 "INSERT-like" 语句并发执行的时候，相同条件，每次执行上面的 "mixed-mode insert" 于是得到的 x, y 值都有可能不同。(因为innodb_autoinc_lock_mode 参数设置为2时，使用轻量级互斥量自增锁，不是在语句执行的整个阶段持有，而是在分配自增id时持有，分配后释放，再次需要分配的时候再持有)

Finally, consider the following statement, issued when the most-recently generated sequence number is 100: #假设 t1下一个可用的自增值为101，那么执行如下的语句会发生什么现象？
```
mysql> INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (101,'c'), (NULL,'d');
```
With any innodb_autoinc_lock_mode setting, this statement generates a duplicate-key error 23000 (Can't write; duplicate key in table) because 101 is allocated for the row (NULL, 'b') and insertion of the row (101, 'c') fails. # 这时不论innodb_autoinc_lock_mode参数设置为何值，该语句都会报唯一键冲突，因为(NULL, 'b') 被分配的自增值为101，后面又要插入(101, 'c')，所以报该错误
Modifying AUTO_INCREMENT column values in the middle of a sequence of INSERT statements

In all lock modes (0, 1, and 2), modifying an AUTO_INCREMENT column value in the middle of a sequence of INSERTstatements could lead to “Duplicate entry” errors. For example, if you perform an UPDATE operation that changes an AUTO_INCREMENT column value to a value larger than the current maximum auto-increment value, subsequent INSERToperations that do not specify an unused auto-increment value could encounter “Duplicate entry” errors. This behavior is demonstrated in the following example.
```
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 Counter Initialization

This section describes how InnoDB initializes AUTO_INCREMENT counters.

If you specify an AUTO_INCREMENT column for an InnoDB table, the table handle in the InnoDB data dictionary contains a special counter called the auto-increment counter that is used in assigning new values for the column. This counter is stored only in main memory, not on disk.

To initialize an auto-increment counter after a server restart, InnoDB executes the equivalent of the following statement on the first insert into a table containing an AUTO_INCREMENT column.

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

InnoDB increments the value retrieved by the statement and assigns it to the column and to the auto-increment counter for the table. By default, the value is incremented by 1. This default can be overridden by the auto_increment_increment configuration setting.

If the table is empty, InnoDB uses the value 1. This default can be overridden by the auto_increment_offset configuration setting.

If a SHOW TABLE STATUS statement examines the table before the auto-increment counter is initialized, InnoDB initializes but does not increment the value. The value is stored for use by later inserts. This initialization uses a normal exclusive-locking read on the table and the lock lasts to the end of the transaction. InnoDB follows the same procedure for initializing the auto-increment counter for a newly created table.

After the auto-increment counter has been initialized, if you do not explicitly specify a value for an AUTO_INCREMENT column,InnoDB increments the counter and assigns the new value to the column. If you insert a row that explicitly specifies the column value, and the value is greater than the current counter value, the counter is set to the specified column value.

InnoDB uses the in-memory auto-increment counter as long as the server runs. When the server is stopped and restarted, InnoDB reinitializes the counter for each table for the first INSERT to the table, as described earlier.

A server restart also cancels the effect of the AUTO_INCREMENT = N table option in CREATE TABLE and ALTER TABLEstatements, which you can use with InnoDB tables to set the initial counter value or alter the current counter value.