If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

ORA-00060: deadlock detected while waiting for resource

Step 1 : Session 1 trying to update a row from a stored procedure using a cursor :

DECLARE
CURSOR cursor_data
IS
SELECT *
FROM Test
where COL_A = 1001
and COL_B = 2001
FOR UPDATE;
BEGIN
FOR cursor_data_rec IN cursor_data
LOOP
update Test
set COL_C=DEF
WHERE CURRENT OF cursor_data;
END LOOP;
END;

COMMIT;

Step 2 : Session 2 only tries to the fetch the record which is updated previously
using the below query :

select * from Test where COL_A=1001 and COL_B=2001 for update
COMMIT;

"Thread 1" is executing "Step 1" and "Thread 2" is executing "Step 2". The "Thread 1"
and "Thread 2" are executed in parallel.

When "Thread 1" is trying to do an udpate, "Thread 2" might try to fetch. In this scenario
"Thread 2" should wait for "Thread 1" to complete its update operation. Once "Thread 1"
completes the update and releases the lock using the commit, "Thread 2" will continue with
its fetch operation.

But we get an deadlock exception in "Thread 2", when "Thread 2" tries to obtain the lock
that is already held by "Thread 1".

If u observe the second select statement from thread 2
Select *** for update which requires a rows lock .But thread 1 is already aquires an exclusive lock on this row.So naturally u will get a dead lock situation here.Basically only one session can have at any time exlusive lock on a row.

ORA-00060: deadlock detected while waiting for resource

HI,

Thanks for the reply.

But the "Thread 2" has to wait until "Thread 1" commits the update. And Once the "Thread 1" releases the lock , "Thread 2" should continue with the fetch operation. But we are encoutering a dead lock scenario here.

No.This is not the case.When ever oracle identifies that there is a dead lock situation it will rollback the statement which casuing this.so there is no way that the thread 2 will wait for its turn.It is a purely user error.So u have to be careful while choosing Which MODE,LOCK and TYPE of statements u are using.