Re: Which query is best?
- From: Martin Bach <development@xxxxxxxxxxxxxxxxx>
- To: sjaffarhussain@xxxxxxxxx
- Date: Tue, 15 Dec 2009 10:10:23 +0000
Hi Jaffar,
I read a very good section about optimistic vs pessimistic locking in
Tom Kyte's effective oracle by design. I can only recommend the book to
anyone developing code.
In my experience "select for update" only works if it's quickly followed
by the update and then a commit. And by quickly I really mean within a
second or less. I used to look after a homegrown 3rd party application
which immediately switched all selects to "select for update" whenever a
user modified data. It really killed the performance in their
multi-tiered web environment, and the worst thing was that the
application wasn't written to deal with connection pools (I think it
assumed dedicated server connections). So even when a user logged off,
the "select for update" mode persisted until the next deadlock. And I
also had a hard time explaining what a deadlock is and that it's not
caused by Oracle itself but rather their code (which was hibernate so
next to impossible to change).
Regards,
Martin
--
Martin Bach
OCM 10g
http://martincarstenbach.wordpress.com
Syed Jaffar Hussain wrote:
My main idea behind looking for an alternative sql was to reduce the
waiting time for the select time to avoid long locking period for the
record.
[..]
--------------------
"Winners don't do different things. They do things differently."
--
//www.freelists.org/webpage/oracle-l
Other related posts:
