BiteSizedChunks.comLearn one small thing at a time.
Course contentsShow
← Databases & SQL
Lesson 997 of 1,9539. MySQL and MariaDB EcosystemsFree lesson

Optimizing Auto-Increment Locking

Configure insertion modes to reduce lock contention on auto-incrementing primary keys.

In this lesson, you will learn how to adjust MySQL/MariaDB innodb_autoinc_lock_mode to minimize performance bottlenecks during high-concurrency inserts.

The Auto-Increment Bottleneck

By default, InnoDB uses a special table-level lock when generating AUTO_INCREMENT values to ensure uniqueness and order. If your application handles a massive volume of concurrent inserts, this lock becomes a point of contention. Threads must wait in line to receive their ID, turning your high-performance database into a serial queue.

Configuring Insertion Modes

MySQL provides three distinct modes for managing these locks, controlled by the innodb_autoinc_lock_mode variable:

  1. Traditional (0): The legacy approach. It uses a table-level lock for all INSERT statements, ensuring predictable sequences but killing concurrency.
  2. Consecutive (1) - The Default: This mode uses a lightweight "mutex" for bulk inserts but a table-level lock for simple, single-row inserts. It balances performance with the guarantee that auto-increment values are gap-free for the same statement.
  3. Interleaved (2): This is the high-performance mode. It removes the table-level lock entirely, allowing multiple statements to generate IDs simultaneously.

Why Choose Interleaved?

If you are running a distributed database or a high-traffic environment where you don't strictly require a gap-free sequence (where a skipped ID is acceptable), setting the mode to 2 significantly reduces lock contention.

To check your current setting, use:

SELECT @@innodb_autoinc_lock_mode;

To change it (typically requires a restart if not dynamically supported by your specific version):

-- In your my.cnf or my.ini file
[mysqld]
innodb_autoinc_lock_mode = 2

Important Caveat

When switching to Interleaved mode, be aware that the auto-increment sequence will not be strictly sequential if you are using statement-based replication. If your application logic relies on every single ID being perfectly sequential without gaps, you must stick with the "Consecutive" (1) mode. If you prioritize raw throughput and can handle gaps in IDs, "Interleaved" (2) is your best tool for optimization.

Key Takeaway: By setting innodb_autoinc_lock_mode to 2, you eliminate table-level auto-increment locking, drastically increasing write throughput at the cost of strict sequential ID guarantees.