How to Use FOR UPDATE for Pessimistic Locking in MySQL

This article explains how to implement pessimistic locking in MySQL using the FOR UPDATE clause. You will learn the fundamental concepts of pessimistic locking, see how the FOR UPDATE statement works within database transactions, and explore practical SQL examples designed to prevent data inconsistency in high-concurrency environments.

Understanding Pessimistic Locking

Pessimistic locking is a concurrency control method that assumes conflicts between transactions are highly likely. To prevent multiple transactions from modifying the same data simultaneously, this approach locks the target database rows as soon as they are read. Until the locking transaction is completed (either committed or rolled back), no other transaction can modify or lock those specific rows.

In MySQL, pessimistic locking is primarily achieved using the FOR UPDATE clause inside an InnoDB transaction.

How FOR UPDATE Works in MySQL

When you append FOR UPDATE to a SELECT query, MySQL locks the retrieved rows. Any other transaction attempting to perform a write operation (UPDATE, DELETE) or another locked read (SELECT ... FOR UPDATE) on these rows will be forced to wait until the original transaction finishes.

To use FOR UPDATE correctly, you must run it inside a transaction block.

Step-by-Step SQL Example

Consider an inventory management scenario where you need to deduct stock for an item. To prevent two customers from purchasing the last item simultaneously, you can use pessimistic locking.

-- Step 1: Start a transaction
START TRANSACTION;

-- Step 2: Select and lock the row
SELECT stock_count 
FROM inventory 
WHERE product_id = 101 
FOR UPDATE;

-- Step 3: Perform the business logic and update the row
UPDATE inventory 
SET stock_count = stock_count - 1 
WHERE product_id = 101;

-- Step 4: Commit the transaction to release the lock
COMMIT;

While this transaction is active, if another user executes a similar transaction for product_id = 101, their SELECT ... FOR UPDATE query will pause and wait for the first transaction to COMMIT or ROLLBACK.

Crucial Implementation Details

To ensure FOR UPDATE behaves efficiently and safely, keep the following MySQL-specific behaviors in mind:

1. Use Indexes to Avoid Table Locking

InnoDB uses row-level locking. However, if your WHERE clause does not use an index, MySQL cannot identify the exact rows to lock and may resort to locking the entire table. Always ensure the columns in your WHERE clause are indexed.

2. Handling Lock Timeouts

If a transaction waits too long for a lock to release, MySQL throws a lock wait timeout error: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

You can configure this timeout duration (in seconds) using the system variable:

SET innodb_lock_wait_timeout = 5;

3. Using NOWAIT and SKIP LOCKED (MySQL 8.0+)

In modern versions of MySQL, you can modify the locking behavior if a row is already locked: