Choosing a Transaction Isolation Level

On Wed, 14 May 2003, Adam Sherman wrote:

How should one choose a Transaction Isolation Level?

Well, first, read up a bit here:

http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=transaction-iso.html

Basically, the philosophy goes like this:

Serializable treats every select kinda like a "select for update" in that
if what you selected in the first line changes by the third line, the
transaction

On Wed, 14 May 2003, Adam Sherman wrote:

How should one choose a Transaction Isolation Level?

Previous message was unfinished and accidentally sent. Please ignore.

On Wed, 14 May 2003, Adam Sherman wrote:

How should one choose a Transaction Isolation Level?

So, read committed may return different data based on when you select, it,
while serializable will always return the same data. Further, concurrent
updates will not cause a read committed transaction to fail, but will
cause a serializable transaction to fail.

This means that in a read committed mode you MAY do an update on old data
that has changed in the database while you were running your transaction,
while serializable will fail and rollback it's transaction automatically,
thus forcing you to resubmit your transaction again.

This means read committed is easier to program in, but may result in some
data coherency issues, while serializable is more work, and tends to run
slower, but ensures your data is coherent as though each transaction ran
one at a time while all other transactions waited in line for it.

Adam Sherman wrote:

How should one choose a Transaction Isolation Level?

I think Tom Lane's presentation on MVCC and isolation levels is a must
read. It's in the .tgz file here:

http://conferences.oreillynet.com/cs/os2002/view/e_sess/2681

I don't know if it's available on techdocs or apart of the normal
documentation set yet (but it should be).

Mike Mascari
mascarm@mascari.com

Very informative posts, thanks everyone.

Sounds like what I need to do is code with a stick up my a_s and use
SERIALIZABLE.

Thanks,

A.

--
Adam Sherman
Tritus CG Inc.
http://www.tritus.ca/
+1 (613) 797-6819