The principle of MySQL auto-increment lock (Auto-Increment Lock)

1. Background and motivation: Why do you need to increase the lock?

In MySQL, self-increase columns (AUTO_INCREMENT) is usually used to generate a primary key or unique identifier of a table, and an incremental integer value is automatically generated every time a new row is inserted. The generation process of self-increment columns must ensure that when multiple transactions are inserted concurrently, the generated values ​​will not conflict, so concurrency control is involved.

In order to ensure that the generation of self-value-added value is thread-safe, the InnoDB storage engine uses Auto-Increment Lock to protect the generation process of self-value-added value. This locking mechanism is used to prevent multiple transactions from simultaneously generating the same self-value-added, while requiring high performance in high concurrency situations.

2. Classification of self-increasing locks

The implementation of self-increasing locking in MySQL is divided into two modes:

• Table-Level Locking: Traditional self-increase locking mode, the entire table is locked when generating self-increase value until the insertion operation is completed.
• Lightweight Mutex: InnoDB subsequently introduces a more efficient way to control the generation of self-value-added values ​​through lightweight mutex locks without locking the entire table.

The policy of self-increasing locking in MySQL can be configured through the following system variables:

• innodb_autoinc_lock_mode: Controls the self-increasing lock mode, there are three values:

  0(Traditional Mode): Use table-level locks to ensure that one auto-added value is inserted at a time.

  1(Continuous mode): Generate self-value-added through mutex locks, and the insertion operation can be performed concurrently.

  2(Lockless mode): Allows pre-allocated self-value-added during batch insertion, and does not use locks.

3. The working mechanism of self-increasing lock

The core goal of self-increment locking in MySQL is to ensure the uniqueness and continuity of self-increment columns during concurrent insertion. The following is the specific mechanism of self-increasing locking：

• Single-line insert: For a single insert operation, the self-increment lock ensures that each transaction acquires a unique self-increment value. Depending on the lock mode, the auto-add value may be locked until the insertion is completed (in traditional mode).
• Batch Insert: For batch inserts (such asINSERT INTO ... SELECTorLOAD DATA), InnoDB will allocate a batch of self-valued value and ensure that the rows inserted by the transaction use a continuous self-valued range.

The specific implementation of self-increasing lockinnodb_autoinc_lock_modeChanges in the settings:

• Traditional mode (innodb_autoinc_lock_mode = 0)：

  • Table-level locks are used when generating self-value-added to ensure that each insertion operation obtains self-value-added in sequence and avoids any conflicts in concurrent scenarios.
  • During insertion, other autoincrement operations on the table will be blocked until the current transaction is completed.

• Continuous mode (innodb_autoinc_lock_mode = 1)：

  • InnoDB uses a lightweight mutex to control self-value generation, only locking the operation of self-value generation, and not locking the entire table. The insertion operation can be performed concurrently, so it has higher performance than traditional modes.
  • After the autovalue-added generation, the mutex is released immediately, allowing other transactions to perform inserts concurrently.

• Lockless mode (innodb_autoinc_lock_mode = 2)：

  • Allows batch insert operations to pre-allocate self-value without using lock mechanisms. Each transaction obtains a continuous set of self-values ​​when it starts inserting, which will not fall back even if the transaction rolls back or fails in the middle.
  • This mode has the best performance when concurrent batch inserts, but may result in self-value-added discontinuity.

4. The underlying principle of self-increasing lock and the implementation of InnoDB

The implementation of self-increasing locks depends on the lock management module of the InnoDB storage engine and the internal mutex mechanism. Next, we analyze how MySQL generates self-value and ensures thread safety from the source code perspective.

4.1 The generation process of self-value-added

The generation of self-value added mainly throughrow_ins_set_autoinc_fields()The function is completed, and the function will decide how to allocate the self-added value based on the status and insertion mode of the current table. In traditional mode, it needs to be locked to ensure that only one transaction can acquire the next self-value.

void row_ins_set_autoinc_fields(
    row_prebuilt_t* prebuilt,  // Table structure    trx_t* trx                // Current transaction) {
    // Check the self-increase field    if (table->autoinc_field) {
        // Generate self-value-added logic        // If lock is required, add lock        mutex_enter(&dict_table_autoinc_mutex);
        
        // Get and update the self-added value        table->autoinc_field->value++;
        
        // Release the mutex lock        mutex_exit(&dict_table_autoinc_mutex);
    }
}

In the above code,mutex_enter()andmutex_exit()It is used to control the mutex generated by self-value-added value. In high concurrency scenarios, lightweight mutex locks can optimize performance better than table-level locks.

4.2 Table-level lock implementation of self-increasing lock

wheninnodb_autoinc_lock_mode = 0InnoDB uses table-level locks to protect the generation of self-value-added values.lock_table()The function locks the entire table, ensuring that only one transaction can perform the insertion operation:

void lock_table(
    dict_table_t* table,   // Table structure    ulint lock_mode,       // Lock type    trx_t* trx             // Current transaction) {
    // In traditional mode, add an AUTO-INC lock to the table    if (lock_mode == LOCK_AUTO_INC) {
        // Locking logic        lock_rec_lock_table();
    }
}

Under the protection of table-level locks, InnoDB ensures that each insertion is generated strictly in order to avoid conflicts. But this approach can also lead to a degradation of concurrency performance, because other transactions must wait before the table lock is released.

4.3 Implementation of lightweight mutex locks

Forinnodb_autoinc_lock_mode = 1andinnodb_autoinc_lock_mode = 2, InnoDB mainly uses mutex locks to protect the value-added generation. The overhead of a mutex lock is much smaller than a table-level lock. The insertion operation only adds a lock when generating a self-added value, and then releases the lock immediately, allowing other transactions to execute concurrently.

The mutex is controlled by InnoDB's internal lock management module, and the relevant code isIn the file:

void mutex_enter(mutex_t* mutex) {
    // Mutex lock enters    os_mutex_enter(mutex);
}

void mutex_exit(mutex_t* mutex) {
    // Mutex exit    os_mutex_exit(mutex);
}

When a transaction requests self-value-added, InnoDB only locks during the self-value-added generation process and releases the lock immediately after generation. This approach significantly improves concurrency performance because most transactions are not blocked.

4.4 Self-value-added caching and allocation

To further improve performance, InnoDB also saves auto-added value in the cache to avoid accessing disks every time it is inserted. For example, when inserted in batches, InnoDB can be allocated a batch of self-value added at one time and then used step by step. Related logic isrow_ins_get_autoinc()Function implementation:

void row_ins_get_autoinc(
    dict_table_t* table,
    ulint num_rows,       // Number of inserted rows    trx_t* trx            // Current transaction) {
    // Get the self-valued cache    autoinc_val = table->autoinc_field->value;
    
    // Assign self-added value to batch inserts    for (i = 0; i < num_rows; i++) {
        autoinc_val++;
        // Update the self-valued table        table->autoinc_field->value = autoinc_val;
    }
}

This caching mechanism allows batch insert operations to obtain a continuous set of auto-added values ​​and further improve performance in high concurrency.

5. The performance of self-increasing locks in transaction isolation level

Self-increasing locks have different performances under different transaction isolation levels:

• Under the REPEATABLE READ isolation level, self-value-added will not affect other transactions before the transaction is committed, so even if the transaction is rolled back, self-value-added will not fall back.
• Under the READ COMMITTED isolation level, each transaction can see the latest self-value increase, so the self-value increase is always incremental.

In addition, no matter what isolation level, once the self-value is assigned to a transaction, even if the transaction is rolled back, the self-value will not be reassigned. This is to avoid different transactions getting the same self-value after rollback.

Summarize:

• The main purpose of self-increasing lock is to ensure that self-increasing value is unique and incremental when concurrent insertion is inserted.
• Three self-increasing lock modes: traditional mode (table lock), continuous mode (lightweight mutex lock) and lock-free mode (batch allocation of self-increasing value), each mode is suitable for different concurrent scenarios.
• The mechanisms of mutex and table-level locks are passed in the source code.mutex_enter()andlock_table()Equal function implementation, the generation of self-valued value isrow_ins_set_autoinc_fields()control.
• The batch insertion and caching mechanism improves the efficiency of self-value generation, especially in high concurrency scenarios, and improves performance by pre-allocating self-value generation.

The flexible mechanism of self-increasing lock allows MySQL to maintain the uniqueness of self-increasing value while handling large-scale concurrent insertions, and can balance performance and consistency through different locking strategies.

This is the article about the principle of using MySQL Auto-Increment Lock. For more related content on MySQL Auto-Increment Lock, please search for my previous articles or continue browsing the following related articles. I hope everyone will support me in the future!