Possible G2-item at SERIALIZABLE
Dear Postgres maintainers,
I think I may have found a violation of Serializability in PostgreSQL
18.4 (Debian 18.4-1.pgdg13+1); the version from the PostgreSQL repo.
## Schema
I create three tables, `txn0`, `txn1`, and `txn2`. Each has identical
structure: an integer primary key `id`, a secondary key `sk` (not used
here), and a text `val`.
create table if not exists txn0 (
id int not null primary key,
sk int not null,
val text
);
## Transactions
All transactions run at SERIALIZABLE isolation, using JDBC transactions
or explicit BEGIN/COMMIT; both seem to produce the problem. Transactions
are randomly generated, and perform a mix of either appends or reads by
primary key. Reads are simple:
select (val) from txn1 where id = ?;
We append unique integers to `val` using `INSERT ... ON CONFLICT` and
the `CONCAT` function, like so:
insert into txn1 as t (id, sk, val) values (?, ?, ?) on conflict (id) do
update set val = CONCAT(t.val, ',', ?) where t.id = ?;
## Phenomenon
Consider this test run:
https://s3.amazonaws.com/jepsen.io/analyses/postgres-18.4/g2-item.zip
This test performed two transactions concurrently, using separate clients.
T1: [[:r 491 [1 2 3]] [:r 597 [2 3 4]] [:append 633 3] [:r 632 nil]]
T2: [[:append 632 2] [:r 630 [1 2 3 4]] [:r 633 nil] [:r 632 [2]]]
T1 performed two unrelated reads, then appended 3 to id 633, and read id
632, finding nothing. T2 appended 2 to id 632, performed an unrelated
read, then read key 633, finding nothing, then read its own append to 632.
The problem is that there is no way these transactions could execute in
(apparent) total order, because neither observed the other's write. Each
has a read-write anti-dependency on the other. This is one of the
canonical "dangerous structures" that Postgres' SSI is supposed to prevent.
Here are the exact SQL statements and their responses, as observed by
the client, ordered by the time their responses arrived.
T2: ["insert into txn2 as t (id, sk, val) values (?, ?, ?) on conflict
(id) do update set val = CONCAT(t.val, ',', ?) where t.id = ?" 632 632
"2" "2" 632] -> [#:next.jdbc{:update-count 1}]
T2: ["select (val) from txn0 where id = ? " 630] -> [{:val "1,2,3,4"}]
T2: ["select (val) from txn0 where id = ? " 633] -> []
T1: ["select (val) from txn1 where id = ? " 491] -> [{:val "1,2,3"}]
T1: ["select (val) from txn2 where id = ? " 597] -> [{:val "2,3,4"}]
T2: ["select (val) from txn2 where id = ? " 632] -> [{:val "2"}]
T1: ["insert into txn0 as t (id, sk, val) values (?, ?, ?) on conflict
(id) do update set val = CONCAT(t.val, ',', ?) where t.id = ?" 633 633
"3" "3" 633] -> [#:next.jdbc{:update-count 1}]
T1: ["select (val) from txn2 where id = ? " 632] -> []
For detailed timing and process information, search for these strings in
`jepsen.log`.
## Manual Repro
I've tried these same commands, and several other anomalies, at the psql
shell, but I can't seem to reproduce it there--perhaps the order is
slightly different due to request-response delay, or I'm getting the
BEGIN/COMMIT timing (which is missing from this log) wrong, or maybe it
depends on other transactions (e.g. for the update vs insert state), or
it's a probabilistic bug. I am able to reproduce it at much slower
timings--e.g. with 100-millisecond delays between statements in each
transaction. However, of the examples I've investigated by hand, each
has involved COMMITs executed at just about the same time. That might be
a hint?
## Test
My test runs in Clojure, a Lisp on the JVM. I use the Postgres JDBC
driver, org.postgresql/postgresql "42.7.11", and next.jdbc "1.3.1093".
The test itself lives at:
https://github.com/jepsen-io/postgres.
With commit 6c2bcc3f43085d3b0f21a5d78ba2b0e0e559ea8f, you can run:
lein run test-all -n n1 -w append --time-limit 30 --concurrency 30 --
log-sql --isolation serializable --max-writes-per-key 4 --leave-db-
running --key-types primary --upsert-types on-conflict --test-count 20
--nemesis none
On my machine, this spits out roughly one G2-item anomaly every 20 seconds.
Its transactions are generated by jepsen.sql, which lives here:
https://github.com/jepsen-io/sql
This is essentially the same test I ran which found a Serializability
bug in 12.3: https://jepsen.io/analyses/postgresql-12.3. I've just
broken it up into two libraries and added a few complications.
The workload which produces these results is:
https://github.com/jepsen-io/sql/
blob/6e34b76e2ac6a1c3edb5a9cab1c835eda4ca4c5e/src/jepsen/sql/append.clj.
I am relatively confident that isolation levels and transactions are
being used correctly, because when I adjust the isolation level from
Serializable to (e.g.) Read Committed, I see vastly different anomalies.
I can also reproduce the problem both using JDBC's transaction calls,
and with explicit BEGIN; SET TRANSACTION ISOLATION LEVEL
SERIALIZABLE; ...; COMMIT;, which makes me think that it's not a bug in
the way next.jdbc or the JDBC driver handle transactions.
## System Information
This is a basically stock Debian 13 LXC container.
$ uname -a
Linux n1 6.17.0-29-generic #29~24.04.1-Ubuntu SMP PREEMPT_DYNAMIC Mon
May 11 10:30:58 UTC 2 x86_64 GNU/Linux
The host machine runs Linux Mint 22.3 Zena. It's a Threadripper 7980X,
if that's relevant--happy to provide additional hardware details.
I install `postgresql-common` from the usual Debian repos, then run `/
usr/share/postgresql-common/pgdg/apt.postgresql.org.sh` to add
Postgres's official repos, then `apt install postgresql-18 postgresql-
client-18`, which as of 2026-05-22, installs Postgresql 18.4 (Debian
18.4-1.pgdg13+1). I leave the configuration files essentially stock,
except for enabling network access and setting `autovacuum_naptime = 5s`.
--
If y'all have any luck reproducing this, I'd love to hear about it!
Yours truly,
--Kyle
On 22 May 2026, at 21:44, Kyle Kingsbury <aphyr@aphyr.com> wrote:
If y'all have any luck reproducing this, I'd love to hear about it!
It looks like I've reproduced something similar with savepoints on HEAD and
REL_18_STABLE. I can't tell if it's really all what you observe. But, probably, part of it.
If that error is raised inside a subtransaction, ROLLBACK TO SAVEPOINT swallows it
and the transaction is free to COMMIT, defeating serializable isolation.
I did not try beyond 18, but I think it always has been there.
PFA attached isolation tester and hand-wavy fix. But I suspect there are more G-items
around.
Full Jepsen test you proposed is currently tried by Nik's machines(in cc).
Best regards, Andrey Borodin.
Attachments:
v1-0001-Add-isolation-test-SSI-serialization-failure-swal.patchapplication/octet-stream; name=v1-0001-Add-isolation-test-SSI-serialization-failure-swal.patch; x-unix-mode=0644Download+79-1
v1-0002-Doom-serializable-transaction-before-raising-seri.patchapplication/octet-stream; name=v1-0002-Doom-serializable-transaction-before-raising-seri.patch; x-unix-mode=0644Download+11-3
Hello Kyle!
On 22 May 2026, at 21:44, Kyle Kingsbury <aphyr@aphyr.com> wrote:
I am relatively confident that isolation levels and transactions are
being used correctly, because when I adjust the isolation level from
Serializable to (e.g.) Read Committed, I see vastly different anomalies.
I can also reproduce the problem both using JDBC's transaction calls,
and with explicit BEGIN; SET TRANSACTION ISOLATION LEVEL
SERIALIZABLE; ...; COMMIT;, which makes me think that it's not a bug in
the way next.jdbc or the JDBC driver handle transactions.
I agree the multi-statement path looks correct, and I don't think next.jdbc or the
driver are at fault either. But I think there's a narrower issue that both of your
checks leave untouched: single-operation transactions are never wrapped in
a transaction at all.
In append's invoke!, a transaction is only opened when it has more than one op [0]https://github.com/jepsen-io/sql/blob/6e34b76e2ac6a1c3edb5a9cab1c835eda4ca4c5e/src/jepsen/sql/append.clj#L155-L160.
Could you confirm whether you still observe G2-item anomalies with a server
configured default_transaction_isolation = 'serializable' (so that the
single-statement operations are certainly Serializable)? If they persist under that
setting I'll dig further...
Best regards, Andrey Borodin.
On 5/31/26 08:19, Andrey Borodin wrote:
Could you confirm whether you still observe G2-item anomalies with a server
configured default_transaction_isolation = 'serializable' (so that the
single-statement operations are certainly Serializable)? If they persist under that
setting I'll dig further...
<sigh>
Yes, this was it, thank you. I've been reworking a whole bunch of things
to make these tests portable between DBs and totally lost the
session-wide transaction isolation. This behavior disappears with:
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Thank you kindly, Andrey. :-)
--Kyle
On 1 Jun 2026, at 06:49, Kyle Kingsbury <aphyr@jepsen.io> wrote:
This behavior disappears
Glad that was it. There's a separate case I'd still like your eyes on: savepoints.
README-SSI says predicate locks must survive a subtransaction rollback, because the
subxact's reads still affect what the top transaction writes. By that same reasoning,
shouldn't a serialization failure raised while checking such a read DooM the top
transaction, rather than being swallowed by ROLLBACK TO SAVEPOINT?
Best regards, Andrey Borodin.
On 1 Jun 2026, at 11:18, Andrey Borodin <x4mmm@yandex-team.ru> wrote:
There's a separate case
Two more SSI false-negatives in the same area, found by Nik's machines
and Mark-bot while triaging original report.
INSERT ... ON CONFLICT reads the conflicting ("arbiter") row to decide
what to do, but doesn't take an SIREAD predicate lock on it. So when
the statement ultimately writes no tuple, that read leaves no trace for
SSI and a concurrent modification of the same row can produce a
non-serializable all-commit result:
* ON CONFLICT DO UPDATE ... WHERE <false> — the no-op update branch; the
conflict row is observed but not updated.
* ON CONFLICT DO NOTHING with a concurrent DELETE of the conflict row.
Replacing the ON CONFLICT with a plain SELECT of the same row aborts
correctly, which is what convinced me the schedules are genuinely
non-serializable and the gap is in the ON CONFLICT probe path.
Both come from the same place - check_exclusion_or_unique_constraint() in
execIndexing.c finds the arbiter tuple but never calls PredicateLockTID().
Adding that lock there fixes both.
There's also one more false negative, but it is in -DTEST_SUMMARIZE_SERIAL
and IMO worth working only when we deal with what we have in production
cases.
Best regards, Andrey Borodin.
Attachments:
0001-Add-isolation-tests-for-ON-CONFLICT-no-op-SSI-false-.patchapplication/octet-stream; name=0001-Add-isolation-tests-for-ON-CONFLICT-no-op-SSI-false-.patch; x-unix-mode=0644Download+224-1
0002-Predicate-lock-the-conflicting-row-in-INSERT-.-ON-CO.patchapplication/octet-stream; name=0002-Predicate-lock-the-conflicting-row-in-INSERT-.-ON-CO.patch; x-unix-mode=0644Download+32-1
+ /*
+ * Mark ourselves doomed before raising the error. Otherwise a
+ * subtransaction abort (ROLLBACK TO SAVEPOINT) could swallow this
+ * error and let the transaction commit anyway, defeating SSI.
+ */
+ MySerializableXact->flags |= SXACT_FLAG_DOOMED;
LWLockRelease(SerializableXactHashLock);
ereport(ERROR,
(errcode(ERRCODE_T_R_SERIALIZATION_FAILURE),I wonder if it would make sense to introduce a "DoomMyselfAndRaiseSerializationFailure" helper for this, while fixing all occurrences we can find? That would make it more explicit, and also less repeated.
PFA attached isolation tester and hand-wavy fix. But I suspect there are more G-items
around.
There are at least 2 more reproducible with the isolation tester in CheckForSerializableConflictOut ("conflict out to old pivot %u", and "conflict out to old committed transaction %u" directly below it). Probably "Canceled on conflict out to old pivot." also should have the same changes?
On 2 Jun 2026, at 03:00, Zsolt Parragi <zsolt.parragi@percona.com> wrote:
+ /* + * Mark ourselves doomed before raising the error. Otherwise a + * subtransaction abort (ROLLBACK TO SAVEPOINT) could swallow this + * error and let the transaction commit anyway, defeating SSI. + */ + MySerializableXact->flags |= SXACT_FLAG_DOOMED; LWLockRelease(SerializableXactHashLock); ereport(ERROR, (errcode(ERRCODE_T_R_SERIALIZATION_FAILURE),I wonder if it would make sense to introduce a
"DoomMyselfAndRaiseSerializationFailure" helper for this, while fixing
all occurrences we can find? That would make it more explicit, and
also less repeated.PFA attached isolation tester and hand-wavy fix. But I suspect there are more G-items
around.There are at least 2 more reproducible with the isolation tester in
CheckForSerializableConflictOut ("conflict out to old pivot %u", and
"conflict out to old committed transaction %u" directly below it).
Probably "Canceled on conflict out to old pivot." also should have the
same changes?
Thanks for looking into this!
I agree fix needs some refinement and can be improved.
However, let's settle on the bug first.
Do you think that recovering serialization error with ROLLBACK TO SAVEPOINT is a bug?
Honestly, I'm not entirely convinced myself. AFAIU SSI docs do not describe this case
clearly.
Best regards, Andrey Borodin.
Hello
AFAIU SSI docs do not describe this case clearly.
I agree on that part.
Do you think that recovering serialization error with ROLLBACK TO SAVEPOINT is a bug?
It does seem like a bug to me.
s1 reads row 2, writes row 1, commits first
s2 writes row 2, then reads row 1 inside a savepoint which gets rolled back
s1 --> s2 (s1 read row 2 before s2 wrote it)
s2 --> s1 (s2 read row 1 before it saw s1's write)
Serialization is defined over what each transaction observed ("SSI is based on the observation"), and whether those observations can be arranged into one consistent order. I think a rolled-back read still matters because we can't roll back the fact that the transaction observed state.
Also, if we try to use the rolled back read for something later, like this:
SAVEPOINT s;
SELECT (balance >= 100) AS do_bonus FROM accounts WHERE id = 1 \gset
ROLLBACK TO SAVEPOINT s;
\if :do_bonus
UPDATE accounts SET bonus = bonus + 10 WHERE id = 2;
\endif
COMMIT;
it will abort on master, the questionable behavior only happens if the read is unused. Based on this, I think even an unused read should correctly abort for consistency.
I think we can also argue for this based on the point about locking from README-SSI:
"Because reads in a subtransaction may cause that subtransaction
to roll back, thereby affecting what is written by the top level
transaction, predicate locks must survive a subtransaction rollback."
On 3 Jun 2026, at 04:03, Zsolt Parragi <zsolt.parragi@percona.com> wrote:
even an unused read should
correctly abort for consistency
Yes, unused read is still a read. That makes sense.
Let's work towards fixes. I agree with your idea about introducing helpers
and tracking all cases where we need to DooM. Would you like to propose a
next patch version where you fix all know cases?
Best regards, Andrey Borodin.