title: How to find out the cause of an Oracle deadlock
body: <p>When testing my application with multiple users, I have encountered an ORA-00060 deadlock error. I'm not sure how to determine the cause of this error, so if anyone could help me it would be greatly appreciated.</p>
  
  <p>I looked in the trace file generated for this event and it shows me which query caused this:</p>
  
  <pre><code>UPDATE TABLE_A SET CK=CK+1 WHERE A_ID=(
      SELECT A_ID FROM TABLE_B WHERE SOME_COLUMN=:SOMECOLUMN
  )
  </code></pre>
  
  <p>It also generated this:</p>
  
  <pre><code>[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-0002a675-00000000        51     200    SX   SSX       52      22    SX   SSX
  TM-0002a675-00000000        52      22    SX   SSX       51     200    SX   SSX
  
  session 200: DID 0001-0033-0000014A session 22: DID 0001-0034-0000005A 
  session 22: DID 0001-0034-0000005A  session 200: DID 0001-0033-0000014A 
  
  Rows waited on:
    Session 200: obj - rowid = 0002A6B1 - AAAqaxAAUAAAAFTAAA
    (dictionary objn - 173745, file - 20, block - 339, slot - 0)
    Session 22: obj - rowid = 0002A6B1 - AAAqaxAAUAAAAFTAAA
    (dictionary objn - 173745, file - 20, block - 339, slot - 0)
  </code></pre>
  
  <p>How do I use the above information to find out what caused the problem? There aren't any unindexed foreign keys on either TABLE_A or TABLE_B (which I believe is commonly the cause of this type of error).</p>
  
  <p>When I run this query:</p>
  
  <pre><code>SELECT OWNER, OBJECT_NAME, OBJECT_TYPE 
  FROM DBA_OBJECTS 
  WHERE OBJECT_ID = 173745;
  </code></pre>
  
  <p>I get the name of a primary key on a completely different table! Could this be a clue as to why the deadlock is happening?</p>