An UPDATE or DELETE is not the indivisible operation it appears to be. Postgres rewrites both as a SELECT followed by a modification of the rows that select returned, which means the locking step sits at the end of the statement rather than the start. The mental model that fits the observed behaviour:
UPDATE= select, lock, update using the data from that first selectDELETE= select, lock, delete using the data from that first select
Consequences for READ COMMITTED
Because of that rewriting, writes are exposed to READ COMMITTED semantics exactly like plain selects — the isolation level is not limited to read-only statements.
The website example from the Postgres documentation shows the sequence:
create table website (
website_id bigint PRIMARY KEY,
hits bigint default 0 not null
);
insert into website(website_id, hits) values (1, 9);
insert into website(website_id, hits) values (2, 10);
BEGIN;
UPDATE website SET hits = hits 1;
-- run from another session: DELETE FROM website WHERE hits = 10;
-- Output: DELETE 0
COMMIT;
Why the DELETE removes nothing
The DELETE of zero rows follows from the order in which the two phases happen. It first selects committed rows, which picks up website_id 2 only — the row with ID 1 is invisible to it. It then tries to lock that row, and since the UPDATE transaction is still open, it blocks there waiting for the lock.
That lock is held until the whole transaction finishes, and once it is released the modified data becomes visible. The DELETE therefore re-evaluates its WHERE clause against the updated row — hits is now 11 — and skips it.
