notes on SERIALIZABLE transactions

Hi everyone! After experimenting with the databases InnoDB and PostgreSQL,
I summarized some of my results that concern the execution of SERIALIZABLE
transactions. Note that I compare with InnoDB for the purpose of giving
the reader a clearer idea of how transactions are implemented in
PostgreSQL. I am not sure if my findings are entirely correct and there
are still a few open issues throughout the text. I would appreciate any
feedback.

s. post, mail: wortelsapje AT hotmail.com
--------------------------------------------------------------------------


             $Id: db.txt,v 1.4 2004/08/15 13:47:42 darseq Exp $

       Execution of SERIALIZABLE transactions in InnoDB and PostgreSQL
       ---------------------------------------------------------------

InnoDB: follows the "2-phase rigorous locking protocol with lock
conversion". Recent versions automatically share lock accessed records in
SERIALIZABLE mode. Updated records are always exclusively locked. To set
exclusive locks for accessed records use SELECT FOR UPDATE although this
is not necessary to execute SERIALIZABLE transactions. The phantom
phenomenon is solved by setting next key locks on the indexes that are
accessed. In REPEATABLE READ mode, normal selects access the database
snapshot that was obtained at the moment of the first select in the
transaction whereas locking selects access the latest committed updates.
Using only the data from locking selects when performing updates is the
same as operating in SERIALIZABLE mode.

PostgreSQL: normal selects *and* locking selects access the database
snapshot that was obtained at the moment of the first select in the
transaction. So PostgreSQL works with database snapshots only. When a
record is updated by a transaction T4...

- ..just before transaction T3 selects it with FOR UPDATE, T3 blocks until
  T4 commits in which case T3 exits with an error message. When T4 rolls
  back, T3 proceeds.
- ..just after transaction T3 selects it with FOR UPDATE, T4 blocks until
  the selected record is actually updated and T3 commits in which case T4
  exits with an error message. When the selected record is not updated or
  T3 rolls back, T4 proceeds.
- ..and transaction T3 updates it as well, both transactions are unaware of
  each others updates, even when either one of them commits. Therefore one
  of them must and will fail. <- true? Why are there write-write
  conflicts? Why not block until others commit?

* Why are exclusive locks used for reads (SELECT FOR UPDATE) instead of
  shared locks?

Because only snapshots are accessed, locking selects do not block when
others update records in such a way that they suddenly match the search
criteria of the select: The phantom phenomenon simply cannot occur. One
should always explicitely lock all the records that are accessed with
SELECT FOR UPDATE to execute SERIALIZABLE transactions. normal selects
cannot be used at all. This is illustrated by the schedule below where
client A is deleted and its appointment B is not.

BEGIN                        BEGIN
SELECT ? <- prevents DELETE A to get the right database snapshot
                             SELECT A FOR UPDATE
                             INSERT B (contains info from A)
                             COMMIT
DELETE A <- no error message because A was not actually updated
DELETE B <- deletes nothing
COMMIT