Re: Which query is best?

From: Martin Bach <>
Date: Tue, 15 Dec 2009 10:10:23 +0000
Message-ID: <>

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


Martin Bach
OCM 10g

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."
Received on Tue Dec 15 2009 - 04:10:23 CST

Original text of this message