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