DBAsupport.com Forums - Powered by vBulletin

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

    0 0%
  • ORA-00060: deadlock detected while waiting for resource

    1 100.00%
Multiple Choice Poll.
Results 1 to 4 of 4

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

  1. #1
    Join Date
    Oct 2004
    Posts
    2

    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

  2. #2
    Join Date
    May 2001
    Posts
    736
    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.

  3. #3
    Join Date
    Oct 2004
    Posts
    2

    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

  4. #4
    Join Date
    May 2001
    Posts
    736
    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
  •  


Click Here to Expand Forum to Full Width