Re: Fix FK deadlock, but no magic please

Oracle's behavior AFAIK depends on which lock you are talking about.  If you
are talking about not being able to get a read lock then Oracle just blocks
until it can, indefinately unless a deadlock is detected.

If you are talking about a write lock, that depends on how the write lock is
called.  If you call SELECT ... FOR UPDATE or call UPDATE/DELETE without
locking first then again Oracle will block until it can get the lock.  If you
call SELECT ... FOR UPDATE NOWAIT then Oracle will throw a specific SQL
exception if a lock cannot be granted immediately.  This allows you to do a
sleep and retry in your code so that you only wait for a lock so long:

boolean locked = false ;
int retryCount = 3 ;
while (!locked) {
  try {
    SELECT 1 FROM some_table WHERE some_condition FOR UPDATE NOWAIT ;
  } catch (SQLException e) {
    if (retryCount > 0) {
      retryCount += 1 ;
      sleep(1);
    } else {
      throw e ;
    } //end if
  } //end try
} //end while

With this kind of logic you can control how long you wait for a lock.  If some
idiot did something that locked a whole bunch of records, you don't want the
entire DB to come to a stop waiting to get a lock.  That type of thing can't
be caught by deadlock detection since the idiot isn't trying to lock anything
else.

Unfortunately, this is not possible in PostgreSQL even if you added the NOWAIT
functionality (yet?).  Thats because the first SQL Exception thrown
automatically voids the transaction and you are forced to roll back.  It
doesn't matter that your code knows how to get around the exception.
PostgreSQL requires that you call rollback and start over.  It is actually
kind of funny.  When I first came across this it was the first time in my
life that I had ever seen a SQL exception on a sequence select (caused by a
SQL Exception just before the select).

On Friday 17 January 2003 10:09 am, Stephan Szabo wrote:
> On Fri, 17 Jan 2003, Jon Swinth wrote:
> > Now, if we could only have the feature like Oracle of SELECT  ... FOR
> > UPDATE NOWAIT, so I can control how long we wait for a lock.  Wait...
> > can't do that until SQL exceptions stop voiding the transaction (I want
> > to be able to retry the lock several times before giving up).
>
> What's Oracle's behavior when some of the rows can be locked and some
> can't?