Re: deadlock

A copy of this was sent to grigsby_at_inquiregroup.com (Ben Grigsby) (if that email address didn't require changing) On Mon, 24 Jan 2000 22:26:07 GMT, you wrote:

>I am writing an app in VC6 accessing an Oracle 8i database using
>RogueWave's dbtools.
>
>My problem is that I am trying to implement row-level locking in a
>distributed applicaition by declaring a cursor for update. It does
>lock the selected row, but any other processes that try to update the
>row simply hang forever until the row is unlocked.
>
>What I need is a way for the other processes to timeout if they cannot
>get a lock for a given row.
>
>I have tried a NOWAIT statement on the end of my select..for update
>statement, but it does not help.

>
>Is there some setting in the database that I can change to prevent
>this deadlock?
>

this is *not* a deadlock. deadlocks are detected and one of the transactions is given an error.

this is a block & lock, this is waiting for a resource you have requested. No one is deadlocked (undetected deadlocks would sit in the database forever with no hope of ever being resolved. In this case, once someone commits -- one of the updates will be released). the updates are blocked by the SELECT for UPDATE (since a select for update is just like an UPDATE -- you may as well just have updated the row).

The processes that do the update -- if they do not wish to BLOCK -- should themselves do a select for updat NOWAIT. If they code:    

   select * from T where id = :x for update NOWAIT;    if ( sqlcode == 0 )

      update T set c1 = :newvalue where id = :x;    else

      error -- if sqlcode =- -54, row is locked    end if;

They (the updates) will not block.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Jan 25 2000 - 07:33:43 CST