Re: Serializable Isolation without blocking - Mailing list pgsql-hackers

Kevin Grittner wrote:
> > maybe I misunderstood something.
> >
> > Consider a function
> > "makehighlander(personid integer) RETURNS void"
> > defined like this:
> >
> >    SELECT ishighlander INTO b FROM scots WHERE id=personid;
> >    IF b THEN
> >       RETURN; /* no need to do anything */
> >    END IF;
> >    UPDATE scots SET ishighlander=TRUE WHERE id=personid;
> >    SELECT count(*) INTO n FROM scots WHERE ishighlander;
> >    IF (n > 1) THEN
> >       RAISE EXCEPTION 'There can be only one';
> >    END IF;
> >
> > If we assume that "ishighlander" is false for all records in
> > the beginning, and there are two calls to the function with
> > two personid's of records *in different pages*, then there cannot be
> > any conflicts since all (write and intention) locks taken by each of
> > these calls should only affect the one page that contains the one
> > record that is updated and then found in the subsequent SELECT.
> >
> > Yet if the two execute concurrently and the two first SELECTs are
> > executed before the two UPDATEs, then both functions have a snapshot
> > so that the final SELECT statements will return 1 and both functions
> > will succeed, leaving the table with two highlanders.
>
> I do think you misunderstood.  If there are two concurrent executions
> and each reads one row, there will be an SIREAD lock for each of those
> rows.  As an example, let's say that one of them (T0) updates its row
> and does its count, finds everything looks fine, and commits.  In
> reading the row the other transaction (T1) modified it sets the
> T0.outConflict flag to true and the T1.inConflict flag to true.

Where does T0 read the row that T1 modified?

>                                                                  No
> blocking occurs.  Now T1 updates its row.

Wait a minute, I am confused. I thought T1 had already modified the row
before T0 committed? Or is "modify" not the update?

>                                            Still no problem, because
> if it committed there, there would still be a sequence of transactions
> (T0 followed by T1) which would be consistent with the results; but it
> selects rows which include the one modified by T0, which causes
> T0.inConflict and T1.outConflict to be set to true.

Where does T1 select rows that were modified by T0? It selects only one
row, the one it modified itself, right?

>                                                      These would both
> be pivots in an unsafe pattern of updates.  No mystery which one needs
> to be rolled back -- T0 has already committed; so T1 is rolled back
> with a serialization failure (probably indicating that it is an unsafe
> update versus an update conflict or a deadlock, which are two other
> forms of serialization failure).  Assuming that the software
> recognizes the serialization failure code and retries, it now finds
> that there is already a highlander and fails for real.

You see, there must be something fundamental I am getting wrong.

Yours,
Laurenz Albe