Re: A third lock method

"Albe Laurenz" wrote:

> See the example I concocted in
> http://archives.postgresql.org/pgsql-hackers/2009-05/msg00316.php

Sure, let's look at that example. Of course, *any* transaction run
by itself won't show differences from true serializable behavior
*regardless* of the mode in which it runs -- because it actually was
serialized. Let's see how your example might work if the function
was being run on two different backends at the same time with
different personid values.

Connection 1:
==========
[Currently no highlander; the function does this for personid = 1]
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;
[Connection 1 now sees a highlander; not yet committed]

Connection 2:
===========
[Currently no highlander according to this snapshot]
[the function does exactly the same thing as on Connection 1,
but for personid 2]
[It doesn't see the work of Connection 1,
so it's count shows the update is OK]

Now they commit, in either order. You now have two highlanders in
the database. You have just demonstrated another case of write skew,
where snapshot isolation does not behave in a truly serializable
fashion, allowing constraints enforced in application software or
functions (including triggers) to be violated. With the changes I'm
working on, one of these would be rolled back with a serialization
error.

> PS: Different from what Kevin claimed, Oracle also cannot grant
> you strictly serializable transactions, because they also use
> snapshot isolation.

Apologies if that is still true. I don't use Oracle and one of the
recent articles I recently read seemed to indicate otherwise.
Thanks for the correction.

-Kevin