How PostgreSQL SELECT FOR UPDATE Locks Rows

When code in a PostgreSQL web application runs SELECT ... FOR UPDATE, PostgreSQL locks the rows the query selects. Another transaction cannot update or delete those rows until the first transaction commits or rolls back. A transaction is a group of database statements that PostgreSQL treats as one unit.

Use this clause when code needs to read a row and then decide what to do based on its current values, such as checking an account balance before withdrawing funds. A plain SELECT reads rows but does not stop another transaction from changing them. This matters when two requests act on the same data at the same time.

What the row lock blocks

FOR UPDATE holds a lock on each selected row until the transaction ends. Another transaction waits for the lock to be released if it tries to update or delete a locked row. It also waits if it tries to acquire a conflicting row lock. A plain SELECT can still read the row because PostgreSQL uses multiversion concurrency control (MVCC), which lets readers see a committed version of a row without waiting for a writer’s row lock. PostgreSQL describes these behaviors in its documentation on explicit locking.

A plain SELECT therefore does not protect a read-then-write decision. For example, two requests could both read the same inventory count and both act on it before either update commits. Locking the row makes the second request wait until the first finishes.

FOR UPDATE also takes a table-level lock that does not prevent ordinary reads, inserts, or updates to other rows. The row locks provide the protection against conflicting changes to the selected rows.

Watch two sessions contend for one row

Assume accounts contains a row with id = 42 and a balance of 100. In Session A, start a transaction and lock that row:

BEGIN;

SELECT balance
FROM accounts
WHERE id = 42
FOR UPDATE;

The query returns 100 and keeps the row locked. Now, in Session B, run:

BEGIN;

SELECT balance
FROM accounts
WHERE id = 42
FOR UPDATE;

Session B waits because Session A holds a conflicting row lock. While B waits, Session A changes the balance and commits:

UPDATE accounts
SET balance = balance - 10
WHERE id = 42;

COMMIT;

In PostgreSQL’s default READ COMMITTED isolation level, which gives each statement a view of committed data, Session B then continues and returns the updated balance, 90, if the row still matches the filter in its WHERE clause. B holds the lock until its own transaction ends. It can now make a decision using the current balance, update the row, and commit. PostgreSQL documents this wait-and-recheck behavior for concurrent updates under READ COMMITTED.

A plain SELECT in Session B would not wait for Session A’s row lock. It would read the last committed version available to its query, which is 100 if A has not committed yet.

Keep the transaction short

Start the transaction immediately before the locked read, perform the check and update, then commit. For example:

BEGIN;

SELECT balance
FROM accounts
WHERE id = 42
FOR UPDATE;

-- Check the returned balance in application code.

UPDATE accounts
SET balance = balance - 10
WHERE id = 42;

COMMIT;

Keep the application’s work between the read and commit brief. If code waits for user input or calls another service while holding the lock, other requests that need the row remain blocked. When autocommit mode is on, PostgreSQL ends the transaction after the SELECT statement and releases its lock. The lock therefore does not protect a later update in a separate transaction.

For a job queue, FOR UPDATE SKIP LOCKED lets a worker skip rows another worker already locked instead of waiting for them. For operations that should fail rather than wait, FOR UPDATE NOWAIT makes the statement return an error if it cannot obtain the lock. PostgreSQL documents these options in the SELECT reference.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com