Horton

Re: Potential G2-item cycles under serializable isolation

Peter Geoghegan <pg@bowt.ie>

View thread View on postgr.es
From: Peter Geoghegan <pg@bowt.ie>
To: Kyle Kingsbury <aphyr@jepsen.io>
Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Date: 2020-06-01T03:04:14Z
Lists: pgsql-bugs 
Hi Kyle,

On Sun, May 31, 2020 at 7:25 PM Kyle Kingsbury <aphyr@jepsen.io> wrote:
> Our reads are of the form:
>
>    select (val) from txn0 where id = $1
>
> And our writes are of the form:
>
>    insert into txn1 as t (id, sk, val) values ($1, $2, $3) on conflict (id) do
> update set val = CONCAT(t.val, ',', $4) where t.id = $5

ON CONFLICT DO UPDATE has some novel behaviors in READ COMMITTED mode,
but they're not supposed to affect SERIALIZABLE mode.

> where $1 and $5 are the key, and $2, $3, and $4 are the element we'd like to
> append to the list.
>
> You can try these transactions for yourself using Jepsen f47eb25. You'll need a
> Jepsen environment; see
> https://github.com/jepsen-io/jepsen#setting-up-a-jepsen-environment for details.
>
>    cd jepsen/stolon
>
>    lein run test-all -w append --nemesis none --max-writes-per-key 8 --node n1
> --just-postgres --concurrency 50 -r 1000

We generally like to produce tests for SSI, ON CONFLICT DO UPDATE, and
anything else involving concurrent behavior using something called
isolation tester:

https://github.com/postgres/postgres/tree/master/src/test/isolation

We may end up writing an isolation test for the issue you reported as
part of an eventual fix. You might find it helpful to review some of
the existing tests.

> Which typically produces, after about a minute, anomalies like the following:
>
> G2-item #1
> Let:
>    T1 = {:type :ok, :f :txn, :value [[:r 7 [1]] [:append 12 1]], :time 95024280,
> :process 5, :index 50}
>    T2 = {:type :ok, :f :txn, :value [[:append 7 2] [:r 14 nil] [:append 14 1]
> [:r 12 nil]], :time 98700211, :process 6, :index 70}
>
> Then:
>    - T1 < T2, because T1 did not observe T2's append of 2 to 7.
>    - However, T2 < T1, because T2 observed the initial (nil) state of 12, which
> T1 created by appending 1: a contradiction!

Could you test Postgres 9.5? It would be nice to determine if this is
a new issue, or a regression.

Thanks
-- 
Peter Geoghegan

Commits

  1. Doc: Add references for SI and SSI.

  2. Improve comments for [Heap]CheckForSerializableConflictOut().

  3. Avoid update conflict out serialization anomalies.

  4. Implement genuine serializable isolation level.