What is Gap Lock in MySQL: Explanation and Examples

In MySQL, a gap lock is a type of lock that prevents other transactions from inserting new rows into a gap between existing index records. It is used to avoid phantom reads and ensure consistent reads during transactions with REPEATABLE READ isolation level.

How It Works

A gap lock in MySQL locks the space between index records, not the records themselves. Imagine a bookshelf where each book is a record. A gap lock is like placing a barrier between two books to stop anyone from adding a new book in that space.

This lock helps prevent other transactions from inserting new rows into the locked gap, which keeps the data stable during a transaction. It is especially useful in the REPEATABLE READ isolation level to avoid phantom reads, where new rows appear in a range query after the initial read.

Gap locks do not block reading existing rows but block inserts into the gaps. They work with InnoDB's next-key locking, which combines record locks and gap locks to protect ranges of rows.

Example

This example shows how a gap lock prevents inserting a new row into a range during a transaction.

sql

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50)
) ENGINE=InnoDB;

INSERT INTO products VALUES (1, 'Pen'), (3, 'Pencil');

-- Transaction 1
START TRANSACTION;
SELECT * FROM products WHERE id BETWEEN 1 AND 3 FOR UPDATE;

-- Transaction 2 (in another session)
INSERT INTO products (id, name) VALUES (2, 'Eraser');

-- Transaction 1 commits
COMMIT;

Output

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

When to Use

Gap locks are used automatically by MySQL InnoDB to maintain data consistency during transactions, especially under the REPEATABLE READ isolation level. You don't manually set gap locks, but understanding them helps when debugging locking issues.

Use gap locks when you want to prevent phantom reads in applications that perform range queries and updates concurrently. For example, in banking systems or inventory management, gap locks ensure no new records appear unexpectedly during a transaction.

Key Points

• Gap locks lock the spaces between index records, not the records themselves.
• They prevent other transactions from inserting rows into locked gaps.
• Gap locks help avoid phantom reads in REPEATABLE READ isolation level.
• They are part of InnoDB's next-key locking mechanism.
• Gap locks can cause lock wait timeouts if transactions conflict.

Key Takeaways

Gap locks prevent inserts into gaps between index records to maintain transaction consistency.

They are used automatically in InnoDB under REPEATABLE READ isolation level to avoid phantom reads.

Gap locks can cause lock wait timeouts if concurrent transactions try to insert in the same gap.

Understanding gap locks helps diagnose locking and concurrency issues in MySQL.

Gap locks are part of next-key locking, combining record and gap locks for range protection.