Re: Which query is best?

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."


--
http://www.freelists.org/webpage/oracle-l