This forum is currently locked. You can't register or post questions at this time. (read more)

ORA-00060: deadlock detected while waiting for resource

All posts relating to Oracle database administration.

Moderator: Tim...

Guest

ORA-00060: deadlock detected while waiting for resource

Postby Guest » Mon Nov 01, 2004 6:09 pm

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

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

RE:ORA-00060: deadlock detected while waiting for resource

Postby Tim... » Mon Nov 01, 2004 6:09 pm

Hi.

The search criteria you've listed for thread 2 matches that listed for thread 1. There is nothing to indicate that the record has already been updated by thread 1 in this query.

In these situations I never lock the outermost cursor. I reselect the records individually for update as I'm about to process them, then commit immediately. This way each record is locked for the minimal amoutn of time, but it does match the criteria at the time it is locked.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 8 guests