InnoDB Lock System: Types, Modes, Structures, and Compatibility

1 Lock System

In MySQL InnoDB, slow SQL statements or deadlocks are common, so a deep understanding of lock mechanisms under DDL and DML is essential.

Lock Classification

By granularity: table lock and row lock

By compatibility: shared lock and exclusive lock

1.1 Lock Modes

1.1 Intent Locks

i. Intent Shared Lock (LOCK_IS)

Table‑level lock required before acquiring a row‑level shared lock.

ii. Intent Exclusive Lock (LOCK_IX)

Table‑level lock required before acquiring a row‑level exclusive lock.

Typical statements: SELECT ... FOR SHARE acquires an intent shared lock first; SELECT ... FOR UPDATE acquires an intent exclusive lock first.

1.2 Shared Lock (LOCK_S)

Used to prevent other transactions from modifying rows that have been read, allowing concurrent reads.

SELECT … IN SHARE MODE

In SERIALIZABLE isolation level, ordinary SELECT acquires LOCK_S.

INSERT/UPDATE that encounter a duplicate key also acquire LOCK_S.

1.3 Exclusive Lock (LOCK_X)

Used by UPDATE, DELETE, or SELECT … FOR UPDATE to ensure exclusive access; only the holder can read or modify the row.

1.4 Auto‑Increment Lock (LOCK_AUTO_INC)

When a table has an AUTO_INCREMENT column, InnoDB obtains an AUTO_INC table lock to serialize ID generation; controlled by innodb_autoinc_lock_mode.

1.2 Row‑Lock Types

1.2.1 Record Lock (LOCK_REC_NOT_GAP)

Locks a specific row record; used in RC and RR isolation levels.

1.2.2 Gap Lock (LOCK_GAP)

Locks a range between index records without locking the records themselves; used mainly in RR isolation level; can be disabled via innodb_locks_unsafe_for_binlog.

1.2.3 Next‑Key (Ordinary) Lock (LOCK_ORDINARY)

Combination of record lock and gap lock; in RR isolation level, NEXT‑KEY LOCK prevents phantom reads.

(‑∞, 100]

(100, 101]

(101, 230]

(230, +∞)

1.2.4 Insert‑Intention Lock (LOCK_INSERT_INTENTION)

A type of GAP lock that allows concurrent inserts into the same gap without waiting.

2 Source Code Implementation

Overview of lock‑related structures:

/**
 * Lock system structure
 */
struct lock_sys_t{
  // mutex
  ib_mutex_t mutex;
  // hash table for record locks (key = spaceid + page no)
  hash_table_t* rec_hash;
  ib_mutex_t wait_mutex;
  srv_slot_t* waiting_threads;
  ibool rollback_complete;
  ulint n_lock_max_wait_time;
  os_event_t timeout_event;
  bool timeout_thread_active;
};

The main member is a hash table that manages globally active lock objects.

2.2 Lock Structure

/**
 * Lock object structure
 */
struct lock_t{
  trx_t* trx;                     // owning transaction
  UT_LIST_NODE_T(lock_t) trx_locks; // transaction's lock list
  ulint type_mode;                // lock mode and type
  hash_node_t hash;               // hash chain node for record lock
  dict_index_t* index;           // index for record lock
  union{
    lock_table_t tab_lock;        // table lock
    lock_rec_t rec_lock;         // row lock
  } un_member;
};

type_mode

is a 32‑bit field storing mode, type, wait flag, etc.

Lock Modes

enum lock_mode{
  LOCK_IS = 0,   // intent shared
  LOCK_IX,       // intent exclusive
  LOCK_S,        // shared
  LOCK_X,        // exclusive
  LOCK_AUTO_INC  // auto‑increment
};

Lock Types and Masks

#define LOCK_MODE_MASK 0xFUL
#define LOCK_TABLE 16
#define LOCK_REC 32
#define LOCK_TYPE_MASK 0xF0UL
#define LOCK_WAIT 256
#define LOCK_ORDINARY 0
#define LOCK_GAP 512
#define LOCK_REC_NOT_GAP 1024
#define LOCK_INSERT_INTENTION 2048
#define LOCK_CONV_BY_OTHER 4096

Bits 0‑3 represent the mode, bit 4 indicates a table lock, bit 8 indicates a wait flag, and bits 9‑31 indicate the lock type.

Lock Mode Determination

enum lock_mode lock_get_mode(const lock_t* lock){
  return static_cast<enum lock_mode>(lock->type_mode & LOCK_MODE_MASK);
}

Lock Type Determination

ulint lock_get_type_low(const lock_t* lock){
  return lock->type_mode & LOCK_TYPE_MASK;
}

Table locks and record locks share the same lock_t structure; row locks are managed per page using a bitmap.

2.3 Lock Compatibility and Strength

Compatibility matrix defines which lock modes can coexist; strength matrix defines when a stronger or equal lock already held makes a new lock unnecessary.

static const byte lock_strength_matrix[5][5] = {
  /* IS  IX   S    X    AI */
  { TRUE, FALSE, FALSE, FALSE, FALSE}, // IS
  { TRUE, TRUE,  FALSE, FALSE, FALSE}, // IX
  { TRUE, FALSE, TRUE,  FALSE, FALSE}, // S
  { TRUE, TRUE,  TRUE,  TRUE,  TRUE }, // X
  { FALSE,FALSE, FALSE, FALSE, TRUE }  // AI
};

static const byte lock_compatibility_matrix[5][5] = {
  /* IS  IX   S    X    AI */
  { TRUE, TRUE,  TRUE,  FALSE, TRUE }, // IS
  { TRUE, TRUE,  FALSE, FALSE, TRUE }, // IX
  { TRUE, FALSE, TRUE,  FALSE, FALSE}, // S
  { FALSE,FALSE, FALSE, FALSE, FALSE}, // X
  { TRUE, TRUE,  FALSE, FALSE, FALSE}  // AI
};

When a lock is requested, InnoDB first checks if the transaction already holds an equal or stronger lock; otherwise it checks compatibility to decide whether to wait.

3 References

Official MySQL documentation: InnoDB Locking (https://dev.mysql.com/doc/refman/5.6/en/innodb-locking.html)

InnoDB Transaction Lock System Introduction (https://www.bookstack.cn/read/aliyun-rds-core/4adfb6141be60032.md#9zcjpa)