MySQL InnoDB Locking: Understanding Row, Gap, and Next-Key Locks to Prevent System Crashes – ITFROMZERO

Table of Contents

Why You Can’t Ignore Locking Mechanisms

Imagine you’re running a flight booking app. There’s only one seat left, but two customers click “Book Now” at the exact same time. Without a locking mechanism, the database might record both as successful. The result is a data disaster and a terrible customer experience.

Back when I was managing an e-commerce system running MySQL 8.0 with traffic around 500 transactions per second, I naively thought using Transactions was enough. However, when traffic spiked during a Flash Sale, the logs started filling up with “Deadlock found” errors. The system slowed down and then came to a complete standstill. Understanding how InnoDB locks data will help you write safer code. Additionally, it significantly optimizes concurrency for your system.

A 5-Minute Practical Test

To see the locking mechanism in action, open two terminal windows (Session A and Session B). We’ll create a simple table to test right away:

CREATE TABLE inventory (
    id INT PRIMARY KEY,
    item_name VARCHAR(50),
    stock INT
) ENGINE=InnoDB;

INSERT INTO inventory VALUES (1, 'iPhone 15', 10), (5, 'Samsung S24', 5), (10, 'Sony A7IV', 2);

Now, follow this scenario:

Session A: Start a transaction and hold the row with id=5.

START TRANSACTION;
UPDATE inventory SET stock = stock - 1 WHERE id = 5; 
-- Session A has now taken control of the row with id=5.

Session B: Attempt to modify the row with id=5.

START TRANSACTION;
UPDATE inventory SET stock = stock - 1 WHERE id = 5;
-- Session B will hang (waiting). It must wait for Session A to release the lock before it can proceed.

As soon as you type COMMIT; in Session A, Session B will be released and execute its command immediately. This is a Row Lock—the most basic form of locking.

Decoding the 3 “Powerful” Lock Types in InnoDB

Many developers mistakenly believe MySQL only locks the specific row being modified. The reality is much more complex, especially with the default isolation level, REPEATABLE READ.

1. Record Lock

This type of lock is placed directly on index records. When you run SELECT * FROM table WHERE id = 10 FOR UPDATE;, MySQL locks the row with ID 10. No one can modify or delete this row until you end the transaction.

2. Gap Lock

A Gap Lock doesn’t lock a specific row; instead, it locks the gap between index records. This is often the most confusing concept.

For example, our inventory table has IDs: 1, 5, 10. The gap between 1 and 5 is (2, 3, 4). The gap between 5 and 10 is (6, 7, 8, 9).

If you run: SELECT * FROM inventory WHERE id BETWEEN 2 AND 4 FOR UPDATE;, even though IDs 2, 3, and 4 don’t exist, MySQL still locks that entire range. The purpose is to prevent another session from INSERTing a new row with ID = 3. This mechanism eliminates the “Phantom Read” phenomenon.

3. Next-Key Lock

A Next-Key Lock is a combination of a Record Lock and a Gap Lock. It locks the record itself along with the gap preceding it.

In practice, InnoDB often uses Next-Key Locks during index scans to ensure data doesn’t change unexpectedly. This explains why updating a non-existent row can sometimes cause someone else’s insert command to hang.

Warning: Don’t Lose Your Index While Locking

This was a painful lesson for me. InnoDB only performs row-level locking if you use an index in your WHERE clause.

Imagine running an update on a column that isn’t indexed:

-- Assuming item_name is not indexed
UPDATE inventory SET stock = 0 WHERE item_name = 'iPhone 15';

In this case, MySQL is forced to perform a Full Table Scan. As a result, it locks every row in the table! On a large database, this action is equivalent to pulling the server’s plug, forcing every other transaction to wait in an endless queue.

How to Check Lock Status?

If the system suddenly feels sluggish, I usually use the following commands to track down the “culprit” holding the locks:

SELECT * FROM information_schema.innodb_trx;
-- Or view a detailed engine report
SHOW ENGINE INNODB STATUS;

4 Golden Rules for Optimizing Concurrency

After handling numerous incidents on large-scale data systems, I’ve distilled these non-negotiable principles:

Keep transactions extremely short: Never call third-party APIs or perform heavy image processing inside a transaction. Do the calculations first, open the transaction, update, and commit immediately.
Always stick to indexes: Every UPDATE or DELETE statement should involve a Primary Key or an Index to avoid accidentally locking the entire table.
Maintain consistent operation order: If Transaction A modifies row 1 then row 2, Transaction B must follow that same order. If B modifies them in reverse (2 then 1), a deadlock is guaranteed to occur.
Choose the right Isolation Level: For features that don’t require 100% precision, like view counts, consider using READ COMMITTED. This reduces Gap Locking and significantly speeds up processing.

Conclusion

Mastering MySQL locking is like understanding traffic laws when driving. It might seem complicated at first, but once you’ve grasped it, you’ll be confident in designing high-load systems without worrying about data corruption or system freezes.

If you’re struggling with slow queries, check out my post on the Slow Query Log for a more comprehensive look at database optimization!