Обсуждение: default_transaction_isolation

I am just know comming to grips with the various transaction_isolation level that can be chosen.
I notice that postgresql.conf file comes with 'read committed' pre-set.

I am wondering why is 'read commited' chosen over any of the other isolation levels such as
SERIALIZABLE?

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

Regards,
Richard Broersma Jr.

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

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

I see, that makes sense.  Thanks for the link and the response. :-)

Regards,
Richard Broersma Jr.