Mastering Pessimistic Locks in MySQL: When and How to Use SELECT … FOR UPDATE

1. Understanding Pessimistic and Optimistic Locks

Pessimistic concurrency control (also called a pessimistic lock, PCC) prevents a transaction from modifying data in a way that would affect other users by acquiring a lock that blocks conflicting operations until the lock is released. It is suited for environments with intense data contention or where the cost of rolling back a transaction exceeds the cost of locking.

2. When FOR UPDATE Takes Effect

The database engine must be InnoDB.

The statement must be executed inside a transaction block (BEGIN … COMMIT).

3. Pessimistic Lock Workflow

Before updating any record, the system tries to acquire an exclusive lock. If the lock acquisition fails, the transaction either waits or throws an exception, depending on the developer’s choice. If the lock succeeds, the record can be modified and the lock is released automatically when the transaction ends.

4. SQL Examples

Assume a table products with columns id (primary key) and name: SELECT * FROM products WHERE id='3' FOR UPDATE; 1. Primary key specified, row exists → row lock.

SELECT * FROM products WHERE id='-1' FOR UPDATE;

2. Primary key specified, row does not exist → no lock.

SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

3. No primary key, query by non‑indexed column → table lock.

SELECT * FROM products WHERE id<>'3' FOR UPDATE;

4. Primary key ambiguous (e.g., using LIKE) → table lock.

SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;

Note: FOR UPDATE works only with InnoDB and only when the statement is inside a transaction block.

5. Test Scenarios

Scenario 1: No transaction, primary key specified, row exists

Result: Lock is ineffective because the statement is not inside a transaction.

Scenario 2: Transaction started, primary key specified, row exists

Transaction opened without commit: a second query on the same primary key blocks.

Transaction opened, second query uses a different primary key: no blocking.

When the first transaction commits or rolls back, the lock is released and the blocked query proceeds.

Scenario 3: Transaction started, primary key specified, row does not exist

Result: Both windows return empty result sets and no blocking occurs because no lock is acquired.

Scenario 4: Transaction started, no primary key, row exists

Window 1 queries a specific order number and succeeds.

Window 2 queries the same order number and blocks, indicating the table (not just the row) is locked.

Window 3 queries a different order number; it also blocks because the whole table is locked.

When Window 1 commits or rolls back, the lock on the table is released, allowing Window 2 (and eventually Window 3) to proceed.

Scenario 5: Transaction started, primary key ambiguous → table lock

Window 1 queries rows with id > 511 and receives three rows.

Window 2 queries the same range and blocks, showing the table is locked.

Window 3 queries a different range ( id > 512) and also blocks.

Only after Window 1 commits does the lock on the table get released; however, Window 2’s pending query may keep the table locked until it finishes, demonstrating that an ambiguous primary‑key query can cause extensive table‑level blocking.

6. Key Takeaways

If the SELECT … FOR UPDATE statement specifies a primary key and returns rows, InnoDB acquires a row‑level lock.

If the primary key is specified but no rows are returned, no lock is taken.

If no primary key is used or the query does not use an indexed column, a table‑level lock is applied.

InnoDB implements row locks by locking index entries; therefore, only index‑based lookups can obtain row‑level locks.

7. Final Summary

Pessimistic locking follows a “lock‑first‑then‑access” strategy to ensure data safety, but it adds overhead to the database and raises the risk of deadlocks. For read‑only data or workloads with low contention, the extra locking cost is unnecessary, and in high‑concurrency scenarios the blocking caused by pessimistic locks can severely degrade system throughput.