Databases: Which one's right for me?

[Jeremy Hylton]

On Sun, 2004-01-11 at 20:12, Aaron Watters wrote:
> > "The standard" ones aren't all that standard.  ANSI SQL-92 defines multiple
> > isolation levels, and they've been (I think) fairly critiqued as  incomplete
> > and partly ambiguous; e.g.,
> > 
> >     A Critique of ANSI SQL Isolation Levels
> >     Hal Berenson, et al.
> >     http://citeseer.nj.nec.com/berenson95critique.html
> 
> Some SQL isolation levels are hacks to allow long running transactions,
> etcetera.  If you keep to the strictest isolation level you get the
> classical behaviour which has been studied and elaborated by many very
> smart people over the last several decades and which is very well
> understood.

I think lower isolation levels are more than hacks for long-running
transactions.  They seem like they're pretty important for achieving
good performance.  The strictest isolation level is necessary, of
course, for some applications, but not all.

Here's was PostgreSQL has to say about full serializability.
http://www.postgresql.org/docs/current/static/transaction-iso.html

> The Serializable mode provides a rigorous guarantee that each
> transaction sees a wholly consistent view of the database. However,
> the application has to be prepared to retry transactions when
> concurrent updates make it impossible to sustain the illusion of
> serial execution. Since the cost of redoing complex transactions may
> be significant, this mode is recommended only when updating
> transactions contain logic sufficiently complex that they may give
> wrong answers in Read Committed mode. Most commonly, Serializable mode
> is necessary when a transaction executes several successive commands
> that must see identical views of the database.

Jeremy