Re: Revisited: Transactions, insert unique.

In a message to and Hiroshi Inoue pgsql-general, Ed Loehr wrote:
>
> -- Within transaction A --------------------------
> BEGIN;
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
>
> -- Within transaction B --------------------------
> BEGIN;
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> INSERT INTO foo (id, msg)
>         SELECT 1, 'From B'
>         WHERE NOT EXISTS (SELECT * FROM foo WHERE id = 1);
> COMMIT;
>
> -- Within transaction A --------------------------
> SELECT * FROM foo;
> ...

In this case, it is actually OK for A to see the committed results of B
because the overall outcome is then equivalent to B occuring entirely
before A.

In general, it is important to remember what SERIALIZABLE means: A
particular concurrent execution of several transactions must have an
observable outcome that is equivalent to running the same transactions one
after the other (serialized). It is NOT required that the outcome be
equivalent to the result that would be observed by running the
transactions in a particular order, such as in the order they were
actually started. The outcome is only required to be equivalent to some
(arbitrary) order.

A concurrancy mechanism supports the SERIALIZABLE isolation level if
it guarantees that every concurrent execution of transactions is
serializable.

Joachim

--
private:  joachim@kraut.bc.ca    (http://www.kraut.bc.ca)
work:     joachim@mercury.bc.ca  (http://www.mercury.bc.ca)