Re: Serializable Isolation without blocking

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