Write skew observed under serializable isolation
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t253201psql -h localhost -U postgresBuilt from patchset v2 (message #2), September 20, 2026 at 06:06 AM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t253201_2 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t253201_2 && git checkout t253201_2Patchset v2 (message #2) is on t253201_2
Hi hackers,
I found that a query using a Tid Range Scan can allow write skew under
`SERIALIZABLE`.
Setup:
CREATE TABLE trs (id int, v int);
INSERT INTO trs VALUES (1, 0), (2, 0);
Then run the following in two sessions, interleaved as shown.
Session 1:
BEGIN ISOLATION LEVEL SERIALIZABLE;
SET enable_seqscan = off;
SELECT sum(v) FROM trs WHERE ctid >= '(0,0)' AND ctid <= '(0,100)';
-- 0 -- implies session 2 has not committed yet
Session 2:
BEGIN ISOLATION LEVEL SERIALIZABLE;
SET enable_seqscan = off;
SELECT sum(v) FROM trs WHERE ctid >= '(0,0)' AND ctid <= '(0,100)';
-- 0 -- implies session 1 has not committed yet
EXPLAIN shows a Tid Range Scan for both reads. Now:
-- session 1
UPDATE trs SET v = 1 WHERE ctid = '(0,1)';
-- session 2
UPDATE trs SET v = 1 WHERE ctid = '(0,2)';
-- session 1
COMMIT;
-- session 2
COMMIT; -- succeeds
Both transactions commit, and the final sum is 2:
SELECT sum(v) FROM trs;
-- 2
This result is not possible in any serial order: since both transactions
read the full range, whichever transaction ran second would have observed a
sum of 1 in its initial select instead of 0.
As a control, the same schedule using a sequential scan causes the second
commit to fail with:
ERROR: could not serialize access due to read/write dependencies among
transactions
(To run with seqscan, make sure to first do both SET enable_tidscan = off
and SET enable_seqscan = on.)
I reproduced this on current master. Tid Range Scans were introduced in
PostgreSQL 14, and the behavior appears present in all branches since then.
Best,
Jacob Brazeal
Let me give a similar example that also makes the final sum query incorrect
under serializable isolation.
Same setup:
CREATE TABLE trs (id int, v int);
INSERT INTO trs VALUES (1, 0), (2, 0);
Session 1:
BEGIN ISOLATION LEVEL SERIALIZABLE;
SET enable_seqscan = off;
SELECT sum(v) FROM trs WHERE ctid >= '(0,0)' AND ctid <= '(0,100)';
-- 0
Session 2:
BEGIN ISOLATION LEVEL SERIALIZABLE;
SET enable_seqscan = off;
SELECT sum(v) FROM trs WHERE ctid >= '(0,0)' AND ctid <= '(0,100)';
-- 0
-- session 1
UPDATE trs SET v = 1 + (SELECT sum(v) FROM trs
WHERE ctid >= '(0,0)' AND ctid <= '(0,100)')
WHERE ctid = '(0,1)';
-- session 2
UPDATE trs SET v = 1 + (SELECT sum(v) FROM trs
WHERE ctid >= '(0,0)' AND ctid <= '(0,100)')
WHERE ctid = '(0,2)';
-- session 1
COMMIT;
-- session 2
COMMIT; -- succeeds
Both transactions commit, and the final sum is 2:
SELECT sum(v) FROM trs;
-- 2
Neither serial order produces 2; both produce 3. The serial run is shown at
the end of this mail.
The range read is a Tid Range Scan standalone and as the InitPlan of the
UPDATE:
Update on trs
InitPlan expr_1
-> Aggregate
-> Tid Range Scan on trs trs_1
TID Cond: ((ctid >= '(0,0)'::tid) AND (ctid <=
'(0,100)'::tid))
-> Tid Scan on trs
TID Cond: (ctid = '(0,1)'::tid)
As a control, force the same reads through a sequential scan by setting
SET enable_tidscan = off;
in both sessions instead of enable_seqscan. (Note that merely leaving
enable_seqscan on is not enough: the planner still prefers a Tid Range Scan
for this qual, so a control written that way is not a control.) The
identical
schedule then fails at the second commit with
ERROR: could not serialize access due to read/write dependencies among
transactions
and leaves sum = 1. A Tid Scan control aborts correctly as well, since
heap_fetch() takes PredicateLockTID on each visible tuple it returns.
The cause appears to be the scan-type test in heap_beginscan(), in
src/backend/access/heap/heapam.c:
if (scan->rs_base.rs_flags & (SO_TYPE_SEQSCAN | SO_TYPE_SAMPLESCAN))
{
Assert(snapshot);
PredicateLockRelation(relation, snapshot);
}
SO_TYPE_TIDRANGESCAN is not in the list, so a TID range scan takes no SIREAD
lock at all. The comment just above already describes the rule that covers
this case -- "in a heap scan there is nothing more fine-grained to lock" --
and a TID range scan is a heap scan with no index involved. The conflict-out
edge is still recorded by heap_prepare_pagescan(); what is missing is the
conflict-in edge, so a later writer's CheckForSerializableConflictIn() finds
no read to conflict with and no cycle can be detected.
As mentioned previously, I reproduced this on current master. Tid Range
Scans were introduced in
PostgreSQL 14, the test above is unchanged on master, and the behavior
appears
present in all branches since then.
Finally, for reference, here are the same statements run serially --
session 1
start to finish, then session 2 -- which is what a serializable execution is
required to be equivalent to. Starting again from the setup above:
-- session 1
BEGIN ISOLATION LEVEL SERIALIZABLE;
SET enable_seqscan = off;
SELECT sum(v) FROM trs WHERE ctid >= '(0,0)' AND ctid <= '(0,100)';
-- 0
UPDATE trs SET v = 1 + (SELECT sum(v) FROM trs
WHERE ctid >= '(0,0)' AND ctid <= '(0,100)')
WHERE ctid = '(0,1)';
COMMIT;
-- session 2
BEGIN ISOLATION LEVEL SERIALIZABLE;
SET enable_seqscan = off;
SELECT sum(v) FROM trs WHERE ctid >= '(0,0)' AND ctid <= '(0,100)';
-- 1 <-- sees session 1's write, so it stores 2 rather than 1
UPDATE trs SET v = 1 + (SELECT sum(v) FROM trs
WHERE ctid >= '(0,0)' AND ctid <= '(0,100)')
WHERE ctid = '(0,2)';
COMMIT;
SELECT sum(v) FROM trs;
-- 3
Running session 2 first gives 3 as well, by symmetry. The interleaved result
of 2 is therefore not equivalent to any serial order.
Best,
Jacob Brazeal
On Sun, Jul 26, 2026 at 11:05 PM Jacob Brazeal <jacob.brazeal@gmail.com>
wrote:
Show quoted text
A candidate patch to fix is attached.
On Mon, 27 Jul 2026 at 07:58, Jacob Brazeal <jacob.brazeal@gmail.com> wrote:
Hi hackers,
I found that a query using a Tid Range Scan can allow write skew under `SERIALIZABLE`.
Good find!
On Mon, 27 Jul 2026 at 08:05, Jacob Brazeal <jacob.brazeal@gmail.com> wrote:
A candidate patch to fix is attached.
I think this patch is taking locks too aggressively; we shouldn't take
a predlock on the whole relation when we're only looking at a small
portion of the table using TID ranges. I think the more appropriate
place to add the predicate locks is in heap_set_tidrange(), and have
Assert(PageIsPredicateLocked(...)) for the returned TID in
heap_getnextslot_tidrange() to make sure we don't regress.
Kind regards,
Matthias van de Meent.
[cc'ing hackers this time, oops!]
Hi Matthias,
Thanks for looking at this.
I think this patch is taking locks too aggressively; we shouldn't take
a predlock on the whole relation when we're only looking at a small
portion of the table using TID ranges. I think the more appropriate
place to add the predicate locks is in heap_set_tidrange(), and have
Assert(PageIsPredicateLocked(...)) for the returned TID in
heap_getnextslot_tidrange() to make sure we don't regress.
I tried the page-level approach, but I don't think heap page locks are
sufficient here. heap_insert() checks only for relation-level predicate
locks; heap page locks are treated as consolidated tuple locks and do not
cover gaps. Thus an insert can land within the scanned TID range, even on a
page covered by a page lock, without conflicting with the reader.
There is also a problem for ranges extending beyond the current end of the
relation. heap_set_tidrange() limits the scan using the relation's current
size, so it cannot lock pages subsequently added by an insert even when
those pages fall within the requested TID range.
The proposed assertion in heap_getnextslot_tidrange() also does not appear
safe after predicate-lock promotion. PageIsPredicateLocked() intentionally
checks only for a page-level lock, not a covering relation-level lock,
while acquiring enough page locks can promote them to a relation lock.
It seems that using page locks here would require some additional notion of
heap gap locking, or corresponding changes to the insert conflict checks.
With the existing predicate-locking scheme, I think the relation lock is
needed to cover inserts as well as updates.
Best,
Jacob