"Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at> 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. No
blocking occurs. Now T1 updates its row. 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. 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.
-Kevin