Re: performance of transaction isolation serializable / optimistic locking

In article <95d59091.0107021025.42d7614a_at_posting.google.com>, tinou_at_tinou.com says...
>
>Hi,
>
>I realize that there is a performance hit when you use serializable
>transaction, but I'm looking for a better feel of how much the hit is.
>I also realize this is a vague question since it'll depend on the
>ratio of writes to reads, the likelyhood of conflict, etc., but what

In the world of serializable transactions, you should be going for very very short transactions. In this case, there is virtually no overhead. (the longer the window, the less and less optimistic one can be ;)

the overhead might come in if you start a serializable transaction and leave it open for an hour. When you come back and run a query -- we'll have to roll back every block you query (if we can -- if the rollback still exists) to the point in time your transaction began.

It will be a function of the amount of changes we have to roll back in your transaction using serializable that we would NOT have had to roll back in read committed.

>are people's experiences with serializable transaction. Switching
>from default read committed to serializable what are "typical"
>performance hits you have experience. It seems that since Oracle

watch your initrans setting, the defaults are too small for most systems that use serializable transactions with active blocks.

>doesn't put read locks for either serializable or read committed the
>hit in oracle for serializable may be significantly less than compared
>to other databases that uses read locks. Is this true. My issue is
>how to get optmistic locking. Seems that either to serialize the
>transaction or do verified updates. If the hit isn't that big I'd
>rather not have to make my sql verified the updates (i.e., update
>table set attribute = 'value' where version = x).
>
>Much thanks.
>
>--
>Tinou Bao
>www.tinou.com

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp