View Poll Results: ORA-00060: deadlock detected while waiting for resource
- Voters
- 1. You may not vote on this poll
-
ORA-00060: deadlock detected while waiting for resource
Table Name - Test
------------------------
COL_A COL_B COL_C
-------- -------- ------
1001 2001 ABC
-------------------------
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".
The following is the stack trace that we found in the ORACLE logs :
*** 2004-10-25 06:27:45.287
*** SESSION ID:(69.21060) 2004-10-25 06:27:45.285
Single resource deadlock: blocked by granted enqueue, f 0
-
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.
Could you please comment on the Same ??
Thankx
E.Saravanan
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|