Understanding MySQL Auto‑Increment Locks and Their Behaviors

1 Auto‑Increment Lock Overview

Auto‑increment lock is a table‑level lock used for columns defined with AUTO_INCREMENT. When one transaction inserts a row, another transaction attempting an INSERT is blocked until the lock is released, ensuring sequential primary‑key IDs.

1.1 Definition

Auto‑increment lock guarantees that rows inserted by concurrent transactions receive consecutive IDs.

1.2 Insertion Methods

Method

Explanation

Simple Inserts

Statements where the number of rows can be estimated (regular INSERT / REPLACE), excluding INSERT … ON DUPLICATE KEY UPDATE.

Bulk Inserts

Statements where the row count cannot be estimated (e.g., INSERT … SELECT, LOAD DATA).

Mixed‑mode Insert

Statements that contain a mix of rows with explicit auto‑increment values and rows without (e.g., INSERT INTO t(id, name) VALUES (1,'a'),(NULL,'b')).

1.3 Auto‑Increment Lock Modes

Mode

Explanation

innodb_autoinc_lock_mode

Traditional

Acquires a table‑level auto‑inc lock for the whole statement; released after the statement finishes.

0

Consecutive

Uses the auto‑inc lock for bulk inserts, but a lightweight mutex for simple inserts.

1

Mixed

Never uses the table‑level auto‑inc lock; always uses the lightweight mutex.

2

1.4 Auto‑Increment ID Issues

1.4.1 Initialization

Before MySQL 8.0 the auto‑increment value was kept only in memory and lost on restart; from 8.0 onward it is persisted to the redo log.

1.4.2 Continuity

None of the three modes guarantee continuous IDs unless the isolation level is set to SERIALIZABLE. Unique‑index conflicts and transaction rollbacks can also cause gaps.

1.4.3 Upper Limit

If a table has no explicit primary key, an implicit ROW_ID is used with a range of [0, 2^32‑1]. Reaching the limit results in Duplicate entry '4294967295' for key 'PRIMARY'. Using BIGINT mitigates this.

2 Practical Experiments

2.1 Simple Insert

CREATE TABLE user_test(
    id BIGINT(20) NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) UNIQUE,
    age TINYINT(3) DEFAULT 18,
    PRIMARY KEY(id),
    INDEX(name)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

Insert multiple rows without specifying IDs; IDs are allocated consecutively (1, 2, 3).

INSERT INTO user_test(name) VALUES ("qinqshui"), ("yunyan"), ("heitie");
SELECT * FROM user_test;

When a unique‑index conflict occurs, the next auto‑increment value jumps, breaking continuity.

# Conflict example
INSERT INTO user_test(name) VALUES ("qinqshui");
INSERT INTO user_test(name) VALUES ("hanlin");
SELECT * FROM user_test;

2.2 Bulk Insert

DROP TABLE user_test;
CREATE TABLE user_test(
    id BIGINT(20) NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) UNIQUE,
    age TINYINT(3) DEFAULT 18,
    PRIMARY KEY(id),
    INDEX(name)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

CREATE TABLE student(
    name VARCHAR(40) UNIQUE
) ENGINE=INNODB;
INSERT INTO student(name) VALUES ("zhang3"), ("li4"), ("wang5");
INSERT INTO user_test(name) SELECT name FROM student;
SELECT * FROM user_test;

Bulk inserts acquire the auto‑inc lock for each row, leading to non‑continuous IDs under concurrency.

2.3 Mixed Insert

DROP TABLE user_test;
CREATE TABLE user_test(
    id BIGINT(20) NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) UNIQUE,
    age TINYINT(3) DEFAULT 18,
    PRIMARY KEY(id),
    INDEX(name)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

INSERT INTO user_test(id, name) VALUES (1, "qingshui");
INSERT INTO user_test(id, name) VALUES (50, "yunyan"), (NULL, "heitie"), (100, "hanlin"), (NULL, "tianqi");
SELECT * FROM user_test;

Explicit IDs update the internal maximum auto‑increment value; subsequent NULL rows continue from the highest allocated value, causing gaps.

2.4 INSERT … ON DUPLICATE KEY UPDATE

# One update, two inserts
INSERT INTO user_test(name) VALUES ("qingshui"), ("longgu"), ("renjie") ON DUPLICATE KEY UPDATE age=28;
SELECT * FROM user_test;

Even when a duplicate key is detected, the auto‑increment counter is incremented, so IDs may be skipped.

3 Source‑Code Analysis

3.1 Relevant Data Structures

struct dict_table_t {
  table_id_t id;               // table id
  char *name;                  // table name
  unsigned space:32;           // tablespace
  dict_col_t *cols;            // column array
  const char *col_names;       // column names
  lock_t *autoinc_lock;       // auto‑inc lock object
  ib_mutex_t autoinc_mutex;    // mutex for auto‑inc
  ib_uint64_t autoinc;         // current auto‑inc counter
  ulong n_waiting_or_granted_auto_inc_locks;
  const trx_t *autoinc_trx;   // transaction holding the lock
  ulint n_rec_locks;
};

struct trx_t {
  trx_lock_t lock;               // lock info
  ulint isolation_level;        // isolation level
  trx_id_t id;                  // transaction id
  read_view_t *read_view;       // read view
  ulint n_autoinc_rows;        // rows affected by auto‑inc in current statement
  ib_vector_t *autoinc_locks;   // held auto‑inc locks
};

class Discrete_interval {
private:
  ulonglong interval_min;       // inclusive lower bound
  ulonglong interval_values;    // number of IDs in the interval
  ulonglong interval_max;       // exclusive upper bound
public:
  Discrete_interval *next;      // next interval
};

3.2 Execution Flow

3.2.1 Overall Sequence Diagram

(Diagram omitted – shows interaction between mutex, auto‑inc lock, and ID allocation.)

3.2.2 Auto‑Increment Initialization

if (prebuilt->table != NULL && !prebuilt->table->ibd_file_missing &&
    table->found_next_number_field != NULL) {
  dict_table_autoinc_lock(prebuilt->table);
  if (dict_table_autoinc_read(prebuilt->table) == 0) {
    innobase_initialize_autoinc();
  }
  dict_table_autoinc_unlock(prebuilt->table);
}

void ha_innobase::innobase_initialize_autoinc() {
  ulonglong auto_inc;
  const Field *field = table->found_next_number_field;
  const char *col_name = field->field_name;
  // SELECT MAX(col_name) FROM TABLE;
  err = row_search_max_autoinc(index, col_name, &read_auto_inc);
  // compute next auto_inc based on max value and column limits
  dict_table_autoinc_initialize(prebuilt->table, auto_inc);
}

3.2.3 Simple vs Bulk Insert Flow

Simple insert: estimate row count → acquire mutex → allocate a range of IDs → release mutex.

Bulk insert: unknown row count → acquire auto‑inc lock for each row → allocate IDs one by one.

Mixed insert: explicit IDs bypass allocation; NULL rows still trigger mutex‑protected allocation.

3.3 Auto‑Increment Allocation Functions

int handler::update_auto_increment() {
  if ((nr = table->next_number_field->val_int()) != 0 ||
      (table->auto_increment_field_not_null &&
       thd->variables.sql_mode & MODE_NO_AUTO_VALUE_ON_ZERO)) {
    adjust_next_insert_id_after_explicit_value(nr);
    insert_id_for_cur_row = 0;
    return 0;
  }
  if ((nr = next_insert_id) >= auto_inc_interval_for_cur_row.maximum()) {
    // fetch more IDs
    get_auto_increment(...);
  }
  set_next_insert_id(compute_next_insert_id(nr, variables));
  return 0;
}

void ha_innobase::get_auto_increment() {
  dberr_t error = innobase_get_autoinc(&autoinc);
  if (error != DB_SUCCESS) return;
  // update dict_table_t with new max value
  dict_table_autoinc_update_if_greater(prebuilt->table, next_value);
}

3.4 Lock Acquisition Logic

dberr_t ha_innobase::innobase_lock_autoinc(void) {
  switch (innobase_autoinc_lock_mode) {
    case AUTOINC_NO_LOCKING:
      dict_table_autoinc_lock(prebuilt->table);
      break;
    case AUTOINC_NEW_STYLE_LOCKING:
      if (thd_sql_command(user_thd) == SQLCOM_INSERT ||
          thd_sql_command(user_thd) == SQLCOM_REPLACE) {
        dict_table_autoinc_lock(prebuilt->table);
        if (prebuilt->table->n_waiting_or_granted_auto_inc_locks) {
          dict_table_autoinc_unlock(prebuilt->table);
        } else {
          break;
        }
      }
    case AUTOINC_OLD_STYLE_LOCKING:
      error = row_lock_table_autoinc_for_mysql(prebuilt);
      if (error == DB_SUCCESS) dict_table_autoinc_lock(prebuilt->table);
      break;
    default:
      ut_error;
  }
  return error;
}

3.5 INSERT … ON DUPLICATE KEY UPDATE Handling

if (error == DB_SUCCESS && table->next_number_field &&
    new_row == table->record[0] &&
    thd_sql_command(user_thd) == SQLCOM_INSERT &&
    trx->duplicates) {
  ulonglong auto_inc = table->next_number_field->val_int();
  ulonglong col_max_value = innobase_get_int_col_max_value(table->next_number_field);
  if (auto_inc <= col_max_value && auto_inc != 0) {
    auto_inc = innobase_next_autoinc(auto_inc, 1,
                                    prebuilt->autoinc_increment,
                                    prebuilt->autoinc_offset,
                                    col_max_value);
    error = innobase_set_max_autoinc(auto_inc);
  }
}

4 Problems with Auto‑Increment IDs

Exposing raw auto‑increment IDs can reveal data volume and facilitate scraping. Mitigation strategies include:

ID Obfuscation : XOR the ID with a random number and append a Luhn check digit.

Snowflake Algorithm : Generate time‑based, roughly increasing IDs with a distributed service.

Mapping Method : Store a mapping between internal IDs and external hashed values (e.g., via Redis).

5 References

MySQL Documentation – InnoDB Auto‑Increment Lock Modes: https://dev.mysql.com/doc/refman/5.6/en/innodb-auto-increment-handling.html#innodb-auto-increment-lock-modes

InnoDB Mutex Implementation: https://blog.csdn.net/yuanrxdu/article/details/41170381

Understanding MySQL Auto‑Increment IDs: https://mp.weixin.qq.com/s/7HKNkpBuPotJ9bcAOCSA6w