Re: Which query is best?
Date: Tue, 15 Dec 2009 10:10:23 +0000
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).
-- Martin Bach OCM 10g http://martincarstenbach.wordpress.com Syed Jaffar Hussain wrote:Received on Tue Dec 15 2009 - 04:10:23 CST
> 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
> "Winners don't do different things. They do things differently."