Re: Potential G2-item cycles under serializable isolation

On 6/2/20 12:50 PM, Peter Geoghegan wrote:
> On Tue, Jun 2, 2020 at 9:19 AM Kyle Kingsbury <aphyr(at)jepsen(dot)io> wrote:
>> OK! So I've designed a variant of this test which doesn't use ON CONFLICT.
>> Instead, we do a homebrew sort of upsert: we try to update the row in place by
>> primary key; if we see zero records updated, we insert a new row, and if *that*
>> fails due to the primary key conflict, we try the update again, under the theory
>> that since we now know a copy of the row exists, we should be able to update it.
>>
>> https://github.com/jepsen-io/jepsen/blob/f47eb25ab32529a7b66f1dfdd3b5ef2fc84ed778/stolon/src/jepsen/stolon/append.clj#L31-L108
> Thanks, but I think that this link is wrong, since you're still using
> ON CONFLICT. Correct me if I'm wrong, I believe that you intended to
> link to this:
Whoops, yes, that's correct. :-)
> Good to have that confirmed. Obviously we'll need to do more analysis
> of the exact circumstances of the anomaly. That might take a while.
No worries! Is it still important that I check this behavior with 9.x as well?
> You might find that "A Critique of ANSI SQL Isolation Levels" provides
> useful background information:
>
> https://www.microsoft.com/en-us/research/wp-content/uploads/2016/02/tr-95-51.pdf

This is one of my favorite papers, and precisely why I asked!

> One section in particular may be of interest:
>
> "ANSI SQL intended to define REPEATABLE READ isolation to exclude all
> anomalies except Phantom. The anomaly definition of Table 1 does not
> achieve this goal, but the locking definition of Table 2 does. ANSI’s
> choice of the term Repeatable Read is doubly unfortunate: (1)
> repeatable reads do not give repeatable results, and (2) the industry
> had already used the term to mean exactly that: repeatable reads mean
> serializable in several products. We recommend that another term be
> found for this."

So... just to confirm, Postgres *did* go along with the anomaly interpretation,
rather than the strict interpretation? It's just weird cuz, like... the Postgres
docs act like SI is stronger than RR, but Berenson et al are pretty clear that's
not how they see it!

--Kyle