First published June 17, 2026. Updated September 15, 2026 with corrected locking explanations and a local PostgreSQL 18.6 experiment.
For a while at work, I was writing SELECT ... FOR UPDATE regularly. Then I realized I couldn’t explain what it actually locked. Could another transaction still read the row? Update it? When would the lock go away?
That was why I picked up The Internals of PostgreSQL. I wanted to understand a statement I was already relying on.
The first useful distinction was between changing a row’s data and creating a new version of it. PostgreSQL’s MVCC updates create new row versions. The old version can remain visible to another transaction, depending on its snapshot. Saying “UPDATE never updates anything,” as I did in the earlier version of this article, hides that distinction behind a misleading line.
That helped me understand ordinary reads. At Read Committed, each statement gets a new snapshot. At Repeatable Read, successive ordinary reads use the transaction’s snapshot, established by its first non-transaction-control statement. A later read can therefore keep seeing an older version even after another transaction commits an update.
But a locking read needs another explanation. FOR UPDATE locks the rows it returns against conflicting operations. It also acquires a table-level ROW SHARE lock. That name does not mean every row is locked, and it does not stop ordinary reads or unrelated row updates. Saying it “doesn’t lock the table” would be inaccurate too.
I verified the following example on PostgreSQL 18.6 on September 15, 2026. It is a local verification exercise, not a production incident. The table contains only test data, and both sessions connect to the same database.
Run this setup once:
CREATE TABLE lock_demo (
id integer PRIMARY KEY,
status text NOT NULL
);
INSERT INTO lock_demo VALUES (1, 'pending');
In session A, keep the transaction open:
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT * FROM lock_demo WHERE id = 1 FOR UPDATE;
In session B, with autocommit enabled:
SELECT * FROM lock_demo WHERE id = 1;
SET lock_timeout = '1s';
UPDATE lock_demo SET status = 'done' WHERE id = 1;
The ordinary SELECT can return the committed row. The UPDATE conflicts with A’s row lock and cannot proceed while A holds it; the one-second timeout makes that wait visible as a lock-timeout error.
Commit session A, then retry the update in B:
-- Session A
COMMIT;
-- Session B
UPDATE lock_demo SET status = 'done' WHERE id = 1;
SELECT * FROM lock_demo WHERE id = 1;
With A’s transaction finished, B can update the row. This is the difference I needed to see: an ordinary reader can read a visible version while a competing writer waits. Another SELECT ... FOR UPDATE on that row would conflict too.
This example deliberately uses Read Committed and has A lock without modifying the row. It does not demonstrate every concurrent-update case. At Repeatable Read, trying to lock a row changed by another transaction since the snapshot can raise a serialization error. The locking documentation describes that boundary and the different row-lock modes.
For application code, the lock’s lifetime matters as much as the lock mode. It is normally held until the transaction ends, not merely until the SELECT returns. If application code waits on an external call before committing, it can keep a competing writer waiting too. Savepoint rollback can release locks acquired after that savepoint; it is another reason to look at the actual transaction boundary.
MVCC also explained a different issue I had been putting in the same mental bucket. Old row versions occupy space. A transaction retaining an old snapshot can prevent some versions from being reclaimed, but an open transaction does not automatically stop all vacuum work. Standard VACUUM generally makes space reusable within the table; it does not generally shrink the file back down. The vacuum documentation makes that distinction explicit.
Now I have more specific questions when reviewing database code: which rows can this statement lock, what conflicts with it, and where does the transaction end? If AI suggests adding FOR UPDATE, I want it to identify the race that requires the lock and demonstrate the competing transactions. The statement being valid SQL is only part of the review.