Deadlock
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Deadlock

  1. #1
    Join Date
    Sep 2006
    Posts
    114

    Deadlock

    I had checked alertlog file and found the following

    Tue Jan 16 11:37:57 2007
    ORA-000060: Deadlock detected. More info in file E:\oracle\admin\wizerp\udump\ORA02996.TRC.

    and when i checked that ORA02996.TRC file

    DEADLOCK DETECTED
    Current SQL statement for this session:

    SELECT 1 FROM journal_mt WHERE journal_id=
    ( SELECT NVL(MAX(journal_id),1 - 1) FROM journal_mt WHERE journal_id BETWEEN 1 AND 99999999) FOR UPDATE

    i also had found some other statements in that particular ORA02996.TRC file
    But i suspect that the above mentioned statement is the major source of deadlock

    My question is ,should i look in-depth the above mentioned query
    or i have to look the other statements in this trace file to resolve the deadlocks?

  2. #2
    Join Date
    Feb 2003
    Location
    INDIA
    Posts
    96
    Hi,

    The same statement should be reason for deadlock.

    Dilipkumar Patel.

  3. #3
    Join Date
    Mar 2000
    Location
    Atlanta, GA,USA
    Posts
    155
    This is a classical design issue.

    You need to:

    a) Look for another statement that comes in pair with this one and utilized by the application in mixed order (no certain order in executing two statements caused by multiple implementation of the same function).

    OR

    b) If journal_id in the PK for journal_mt and there is a child table referencing it that does not have an index on its FK column.

    Both are design problems ( a) is application design problem, b) is database design problem). b) can be a result of missing index as a result of incomplete database migration to a new server or simply deleted by accident.

    In case of the second scenario you have another table locked... THE ENTIRE TABLE as a result of this "innocent" query.

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