How `AUTO_INCREMENT` works on MySQL

InnoDB, one of the most common storage engine MySQL uses, supports three different lock modes for AUTO_INCREMENT – traditional, consecutive and interleaved.

The lock modes are named this way because it has something to do with SBR – statement based replication. If the mysql primary inserted two rows with user_id 1 and 2, you would want the replica to have 1 and 2 be assosicated with the correct user row instead of interleaved.

This is the most straightforward one. It acquires a table-level lock for inserts. The lock is held until the end of the insert statement you are running. Since transactions will be replicated in the same order (or as if in the same order) as they are executed, "traditional" lock mode works for SBR setup. But as you can tell, it's also slow.

This is the default InnoDB lock mode until MySQL 8.0. It still uses table-level lock for inserts but not always. For example, if MySQL knows the number of rows will be inserted (e.g. insert one row), it can just allocate ids to the transaction and move on. Practically it means MySQL only needs to hold a lock for the duration of the allocation (not until the end of the insert statement). What if MySQL allocates the ids to some transaction and it later got rolled back? These ids are simply lost. MySQL doesn't guarantee that there are no gaps in an auto_increment column.

Leave a Comment