Re: default_transaction_isolation

On Thu, Mar 22, 2007 at 10:21:27PM -0700, Richard Broersma Jr wrote:
> I am wondering why is 'read commited' chosen over any of the other
> isolation levels such as SERIALIZABLE?

If you use SERIALIZABLE then some transactions will need extra logic
to handle SQLSTATE 40001 SERIALIZATION FAILURE ("could not serialize
access due to concurrent update"). The documentation suggests why
SERIALIZABLE isn't the default:

http://www.postgresql.org/docs/8.2/interactive/transaction-iso.html#XACT-SERIALIZABLE

"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."

> A more generalized question would be, are there any application/RDBMS
> design considerations to help determine the best default isolation level?

In addition to the above:

"Most commonly, Serializable mode is necessary when a transaction
executes several successive commands that must see identical views
of the database."

I leave READ COMMITTED as the default and use SERIALIZABLE only in
transactions that need it, such as in reporting applications that
must see consistent results across multiple queries.

--
Michael Fuhr