I've been working on a paper about MVCC implementations going
back to 1978 and in the process have thought a bit about
the various ways that keeping multiple versions of records can
can be exploited to isolate transactions.

Firebird currently has three modes: "Snapshot Table Stability"
which is serializable, "Snapshot" which provides consistent
reads but is not serializable, and "Read Committed" which is
neither consistent nor serializable. The first locks tables
and is not very interesting. The second is the classic MVCC
access mode: stable reads with some write skew anomalies that
rise from the fact that concurrent transactions are invisible
to each other. The third ... well it was developed by Borland.

Postgres, InnoDB (the MySQL transactional engine), and Oracle
implement another isolation mode between "Snapshot" and
"Read Committed." Normal Select statement works just as
they do in Firebird's Snapshot mode. Update, delete,
and select for update wait if there's a newer version of
the record than the transaction can see, then succeed when
the transaction that created the newer version commits.

Dirty writes? Maybe. Inconsistent results from select vs.
select for update? Yes. But very useful for applications
that want to created identifiers from a table. And for
benchmarks like DBT2 that use tables to create identifiers.
And in my opinion, it's a much more defensible isolation mode
than read committed.

InnoDB and Oracle default to this "write commited" mode.
Postgres called Firebird's "Snapshot" "Serializable", and
this mode "read committed".


Cheers,

Ann