1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

DEADLOCK Alert in log file

Discussion in 'SQL PL/SQL' started by mail82amol, Apr 21, 2014.

  1. mail82amol

    mail82amol Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    We have recently enabled deadlock trace log on our servers and have seen multiple occurrence of statement "initiate state dump for DEADLOCK" in our trace file. We checked that all the foreign keys on the tables are indexed and also there is no occurrence of ORA-0060 in database alert logs.

    Want to check if there is an issue with the system and what needs to be done to resolve this.

    Deadlock trace file logs attached.

    Thanks in advance. Also please let me know if more information is required.
     

    Attached Files:

  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    Oracle version and os version?

    Note:
    One sql the operator not an index for DEADLOCK
    DEADLOCK results from two conflicting DML
    DEADLOCK results from flawed application coding & can be fixed only by a coding change.

    That there DEADLOCK



     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The code needs to be changed to eliminate the deadlocks; this is the result of two sessions waiting for the same resources where one session as resource A locked and won't release it until it completes actions on resource B, and the other session has resource B locked and won't release it until it completes actions on resource A. It's a no-win situation and eventually one of the transactions will be killed to resolve the issue.