Introduction

SELECT ... FOR UPDATE is for situations where you are about to modify rows and need to prevent another transaction from changing those same rows first. It is most useful in read-modify-write workflows where reading without a lock could lead to lost updates, double booking, overselling, or other race conditions.

What It Actually Does

Inside a transaction, SELECT ... FOR UPDATE reads rows and acquires a lock that blocks conflicting updates until the transaction commits or rolls back.

Typical pattern:

1BEGIN;
2
3SELECT balance
4FROM accounts
5WHERE account_id = 42
6FOR UPDATE;
7
8UPDATE accounts
9SET balance = balance - 100
10WHERE account_id = 42;
11
12COMMIT;

The important part is that the row is locked during the read-modify-write sequence. Without that lock, two transactions might both read the same balance and apply conflicting updates.

Use It for Read-Modify-Write Logic

The classic use case is when the application must inspect current row state before deciding what update to make.

Examples:

• checking account balance before debiting funds
• confirming inventory before reserving stock
• marking a job row as claimed by one worker
• preventing two users from booking the same seat

If the decision depends on the current value and must remain valid until the update occurs, row locking is often the right tool.

Example: Inventory Reservation

1BEGIN;
2
3SELECT quantity
4FROM inventory
5WHERE sku = 'ABC-123'
6FOR UPDATE;
7
8UPDATE inventory
9SET quantity = quantity - 1
10WHERE sku = 'ABC-123' AND quantity > 0;
11
12COMMIT;

This prevents two concurrent transactions from both believing the same last item is available.

When You Do Not Need It

You do not need FOR UPDATE for every update.

If one SQL statement can express the change safely by itself, that is often better. For example:

UPDATE inventory
SET quantity = quantity - 1
WHERE sku = 'ABC-123' AND quantity > 0;

Then check the affected row count. If zero rows were updated, no inventory was available.

This approach can be simpler and more scalable because it reduces the time rows stay locked.

So the rule is not “always use FOR UPDATE when updating.” It is “use it when the application must hold a stable view of row state between read and write.”

Transaction Scope Matters

SELECT ... FOR UPDATE only makes sense inside a transaction. If autocommit ends the transaction immediately, the lock may be released before your application does anything useful with it.

That means the surrounding transaction boundaries are just as important as the query itself.

Blocking, Waiting, and Contention

Locking rows is powerful, but it introduces waiting. Another transaction trying to update the same locked row may block until the first transaction finishes.

That is acceptable for correctness-sensitive operations, but it means you should:

• keep the transaction short
• avoid user interaction while locks are held
• lock only the rows you truly need

Long transactions turn correctness protection into a performance bottleneck.

Common Pitfalls

The most common mistake is using SELECT ... FOR UPDATE outside a meaningful transaction. The lock then does not protect the intended code path.

Another issue is locking rows too early and holding them while the application performs slow work such as API calls or user prompts. That increases contention unnecessarily.

People also use it where one atomic UPDATE would have been enough. If a single statement can safely enforce the condition, prefer that simpler path.

Finally, do not assume all databases implement locking semantics identically. The broad idea is portable, but exact behavior depends on the database engine and isolation level.

Summary

• Use SELECT ... FOR UPDATE when a transaction must safely read row state and then update based on that state.
• It is most useful for read-modify-write workflows with concurrency risk.
• Keep the transaction short so row locks do not become a bottleneck.
• Prefer a single atomic UPDATE when that already solves the race cleanly.
• The clause is about correctness under concurrency, not about ordinary reads or every update statement.