Understanding InnoDB Row Locking: Fast and Slow Locking Mechanisms

1. Two Locking Logics

Updates and deletes require row locks; reads and inserts may also need them. InnoDB separates row‑lock acquisition into fast and slow paths, choosing fast when conditions are met to improve performance.

2. Acquiring a Token

InnoDB uses a hash table rec_hash to manage all row‑lock structures. The hash table size is 5 × (srv_buf_pool_size / UNIV_PAGE_SIZE). To avoid hash collisions, each bucket holds a linked list of lock structures. A mutex token, stored in the mutexes array (512 entries), protects each bucket during read/write.

Obtaining a token involves: computing a hash from the page number and tablespace ID, taking modulo of the hash‑table bucket count, then modulo 512 to select a mutex, and finally locking that mutex.

3. Fetching the Row‑Lock Structure

After the mutex is held, InnoDB looks up the bucket to find a lock structure whose page matches the target. If found, it is used; otherwise the fast‑lock path cannot be taken.

4. Fast Lock Path Ⅰ

If no existing lock structure is found, InnoDB creates a new one. It first tries to reuse one of the eight pre‑allocated lock structures per transaction; if none are free, it allocates memory (96 bytes for the lock plus a bitmap whose size depends on the number of records in the page).

// lock_size calculation example
static size_t lock_size(const page_t *page) {
  // number of records in the page
  ulint n_recs = page_dir_get_n_heap(page);
  /* Make lock bitmap bigger by a safety margin */
  return (1 + ((n_recs + LOCK_PAGE_BITMAP_MARGIN) / 8));
}

The bitmap is cleared and the bit corresponding to the current record is set to 1.

5. Fast Lock Path Ⅱ

If an existing lock structure is found and none of the slow‑lock conditions apply, the bitmap bit for the current record is simply set to 1 (or left unchanged if already 1), completing the lock without allocating a new structure.

6. Slow‑Lock Conditions

The slow path is taken when any of the following holds: (1) the page already has two or more lock structures; (2) the first lock structure was created by another transaction; (3) the lock mode differs from the requested mode; (4) the bitmap does not have enough space for the record.

7. Summary

Fast locking either creates a new lock structure (when none exists) or reuses an existing one by setting the appropriate bitmap bit, while slow locking is triggered by conflicts, multiple lock structures, mode mismatches, or insufficient bitmap space.