Thread: Choosing a Transaction Isolation Level
How should one choose a Transaction Isolation Level? A. -- Adam Sherman Tritus CG Inc. http://www.tritus.ca/ +1 (613) 797-6819
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