title: Finding cause of deadlock error from oracle trace file
body: <p>I have been getting this "ora-00060 deadlock detected while waiting for resource" error often now in my application when multiple users are using the application. I have got the trace file from the oracle Admin, but need help in reading it. Below is bits of data from the trace file, which i hope would help in locating the cause.</p>
  
  <pre><code>*** 2013-06-25 09:37:35.324
  DEADLOCK DETECTED ( ORA-00060 )
  
  [Transaction Deadlock]
  
  The following deadlock is not an ORACLE error. It is a deadlock due 
  to user error in the design of an application
  or from issuing incorrect ad-hoc SQL. The following
  information may aid in determining the deadlock:
  
  Deadlock graph:
                     ---------Blocker(s)--------  ---------Waiter(s)---------
  Resource Name          process session holds waits  process session holds waits
  TM-000151a2-00000000       210      72    SX   SSX      208      24    SX   SSX
  TM-000151a2-00000000       208      24    SX   SSX      210      72    SX   SSX
  
  session 72: DID 0001-00D2-000000C6  session 24: DID 0001-00D0-00000043 
  session 24: DID 0001-00D0-00000043  session 72: DID 0001-00D2-000000C6 
  
  Rows waited on:
   Session 72: no row
   Session 24: no row
  
  ----- Information for the OTHER waiting sessions -----
  Session 24:
   sid: 24 ser: 45245 audsid: 31660323 user: 90/USER
    flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
    flags2: (0x40009) -/-/INC
   pid: 208 O/S info: user: zgrid, term: UNKNOWN, ospid: 2439
     image: oracle@xyz.local
   client details:
     O/S info: user: , term: , ospid: 1234
     machine: xyz.local program: 
   current SQL:
    delete from EMPLOYEE where EMP_ID=:1
  
   ----- End of information for the OTHER waiting sessions -----
  
  Information for THIS session:
  
   ----- Current SQL Statement for this session (sql_id=dyfg1wd8xa9qt) -----
   delete from EMPLOYEE where EMP_ID=:1
  ===================================================
  </code></pre>
  
  <p>I would appreciate if some one can tell me what the "Deadlock graph::" is saying. Also the rows waited on section says no rows. </p>
  
  <p>I also read in some blogs that "sqltxt" section from the trace file can suggest the cause. Below is the query i see in that section.</p>
  
  <pre><code> select /*+ all_rows */ count(1) from "USERS"."EMPLOYEE_SALARY" where EMPSAL_EMP_ID=:1
  </code></pre>
  
  <p>The employee_salary table has foreignkey constraint on EMPSAL_EMP_ID column. </p>
  
  <p>The sql hint says "all_rows", so does it mean that this table gets table level lock when deleting records from employee table? i dont have an index on the foreign key column currently. Would adding an index on this column help?</p>
  
  <p>Kindly post, in case any more information is need.</p>
  
  <p>Thanks</p>