Understanding MySQL Locking Mechanisms: From Row Locks to Gap Locks

Overview of MySQL Lock Granularity

MySQL implements three lock granularity levels:

Row‑level locks – used by transactional storage engines such as InnoDB and NDB Cluster.

Table‑level locks – used by non‑transactional engines MyISAM, Memory, CSV.

Page‑level locks – an intermediate level used mainly by BerkeleyDB.

Storage Engines and Their Default Lock Types

Table‑level locking is the default for MyISAM, Memory and CSV. InnoDB and NDB Cluster rely on row‑level locking. BerkeleyDB provides page‑level locking.

MyISAM Table‑Level Lock Types

Read lock – granted only when the target table is not write‑locked and no higher‑priority write lock is pending.

Write lock – blocks both reads and writes on the locked table.

MySQL Lock Queues

MySQL maintains four internal queues to track lock state: lock->read – current read‑lock holders. lock->read_wait – read requests waiting for a lock. lock->write – current write‑lock holder. lock->write_wait – write requests waiting for a lock.

InnoDB Row‑Level Lock Types

Shared lock (S‑lock) – allows a transaction to read a row while preventing other transactions from acquiring an exclusive lock on the same row.

Exclusive lock (X‑lock) – permits a transaction to modify a row and blocks all other transactions from reading or writing it.

Intention shared lock (IS) – indicates that a transaction intends to set shared locks on rows within a table.

Intention exclusive lock (IX) – indicates intent to acquire exclusive locks on rows.

InnoDB Gap (Next‑Key) Locks

InnoDB implements next‑key locking by locking the index record and the gap before it. This prevents phantom rows during repeatable‑read isolation but can block inserts of non‑existent keys within the locked range.

Key side effects:

If a query cannot use an index, InnoDB falls back to a table‑level lock, reducing concurrency.

When an index does not cover all filter predicates, rows outside the result set may be locked because the gap lock covers a range, not a single key.

Different queries that use the same index key but access different rows can lock each other.

MyISAM Lock‑Optimization Recommendations

Break large, complex statements into smaller queries to shorten lock duration.

Create appropriate indexes so reads complete quickly.

Store only required columns and choose compact column types.

Periodically run myisamchk to optimize the MyISAM data file.

Enable concurrent_insert as needed: concurrent_insert=2 – always allow inserts at the end of the file. concurrent_insert=1 – allow end‑of‑file inserts only when there is no free space in the middle. concurrent_insert=0 – disable concurrent inserts.

Adjust read/write priority for read‑heavy workloads: set low_priority_updates=1 to give reads higher priority.

InnoDB Row‑Lock Optimization Recommendations

Ensure every query can use an index; otherwise InnoDB may promote to a table‑level lock.

Design indexes that are as narrow as possible so the locked range is minimal.

Avoid range predicates (e.g., BETWEEN, LIKE 'prefix%') that trigger gap locks unless necessary.

Keep transactions short – commit as soon as the required work is done to reduce lock hold time.

If the application permits, use a lower isolation level (e.g., READ‑COMMITTED) to reduce lock overhead.

Monitoring Lock Contention

MySQL provides status variables for both table‑level and InnoDB row‑level locks.

Table‑Level Lock Variables

SHOW STATUS LIKE 'Table%';

Table_locks_immediate

– number of times a table lock was obtained without waiting. Table_locks_waited – number of times a lock request had to wait.

A healthy ratio is Table_locks_immediate > 5000 × Table_locks_waited. A high Table_locks_waited indicates serious contention.

InnoDB Row‑Lock Variables

SHOW STATUS LIKE 'Innodb_row_lock%';

Innodb_row_lock_current_waits

– current number of waiting lock requests. Innodb_row_lock_time – cumulative lock wait time since server start (ms). Innodb_row_lock_time_avg – average wait time per lock (ms). Innodb_row_lock_time_max – longest single lock wait (ms). Innodb_row_lock_waits – total number of lock wait events.

Key metrics to watch are Innodb_row_lock_time_avg, Innodb_row_lock_waits and Innodb_row_lock_time.

Detailed InnoDB Monitoring

Create a dummy monitor table to enable the InnoDB monitor:

CREATE TABLE innodb_monitor (a INT) ENGINE=InnoDB;

Then run SHOW ENGINE INNODB STATUS (or SHOW INNODB STATUS) to view detailed lock and transaction information logged to the MySQL error log.