How to identify the offending application code when a deadlock is detected in Oracle Database.

A deadlock occurs when two or more sessions are waiting for data locked by each other, resulting in all the sessions being blocked. Oracle automatically detects and resolves deadlocks by rolling back the statement associated with the transaction that detects the deadlock. Typically, deadlocks are caused by poorly implemented locking in application code. This tutorial shows the steps necessary to identify the offending application code when a deadlock is detected.

Step1 - Create a test user.

CREATE USER test IDENTIFIED BY test
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users;

GRANT CONNECT, CREATE TABLE TO test;
GRANT EXECUTE ON DBMS_LOCK TO test; 

Step2 - Create a test schema.

CONN test/test

CREATE TABLE deadlock_1 (
  id  NUMBER
);

CREATE TABLE deadlock_2 (
  id  NUMBER
);

INSERT INTO deadlock_1 (id) VALUES (1);
INSERT INTO deadlock_2 (id) VALUES (1);
COMMIT;

Step3 - Start SQL*Plus sessions

Start two SQL*Plus sessions, each logged into the test user, then run the following pieces of code, one in each session.

-- Run in session 1.
DECLARE
  l_deadlock_1_id  deadlock_1.id%TYPE;
  l_deadlock_2_id  deadlock_2.id%TYPE;
BEGIN
  -- Lock row in first table.
  SELECT id
  INTO   l_deadlock_1_id
  FROM   deadlock_1
  WHERE  id = 1
  FOR UPDATE;
 
  -- Pause.
  DBMS_LOCK.sleep(30);

  -- Lock row in second table.
  SELECT id
  INTO   l_deadlock_2_id
  FROM   deadlock_2
  WHERE  id = 1
  FOR UPDATE;

  -- Release locks.
  ROLLBACK;
END;
/

-- Run in session 2.
DECLARE
  l_deadlock_1_id  deadlock_1.id%TYPE;
  l_deadlock_2_id  deadlock_2.id%TYPE;
BEGIN
  -- Lock row in second table.
  SELECT id
  INTO   l_deadlock_2_id
  FROM   deadlock_2
  WHERE  id = 1
  FOR UPDATE;
 
  -- Pause.
  DBMS_LOCK.sleep(30);

  -- Lock row in first table.
  SELECT id
  INTO   l_deadlock_1_id
  FROM   deadlock_1
  WHERE  id = 1
  FOR UPDATE;

  -- Release locks.
  ROLLBACK;
END;
/

The first piece of code gets a lock on a row in the DEADLOCK_1 table, it pauses for 30 seconds, then attempts to get a lock on a row in the DEADLOCK_2 table. The second piece of code does the same thing but in reverse, locking a row in the DEADLOCK_2 table, then the DEADLOCK_1 table. The call to the DBMS_LOCK.SLEEP procedure is only present to give you enough time to switch sessions.

Eventually, one of the sessions will detect the deadlock, rollback its transaction and produce a deadlock error, while the other transaction completes successfully. A typical deadlock error is displayed below.

ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 16

In addition to the deadlock error reported to the session, a message is placed in the alert log.

ORA-00060: Deadlock detected. More info in file c:\oracle\product\10.2.0\admin\db10g\udump\db10g_ora_4200.trc.

The error message contains a reference to a trace file, whose contents indicate the SQL statements blocked in both the session that detected the deadlock and the other blocked sessions.

*** ACTION NAME:() 
*** MODULE NAME:(SQL*Plus) 
*** SERVICE NAME:(SYS$USERS) 
*** SESSION ID:(137.7008) 
DEADLOCK DETECTED
[Transaction Deadlock]
Current SQL statement for this session:
SELECT ID FROM DEADLOCK_2 WHERE ID = 1 FOR UPDATE
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
1AFBE484        16  anonymous block
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
TX-0006001a-0000131b        19     137     X             24     159           X
TX-00030028-00001313        24     159     X             19     137           X
session 137: DID 0001-0013-000067B7 session 159: DID 0001-0018-0000100C
session 159: DID 0001-0018-0000100C session 137: DID 0001-0013-000067B7
Rows waited on:
Session 159: obj - rowid = 0000E6C7 - AAAObHAAEAAAABAAAA
  (dictionary objn - 59079, file - 4, block - 64, slot - 0)
Session 137: obj - rowid = 0000E6C8 - AAAObIAAEAAAABIAAA
  (dictionary objn - 59080, file - 4, block - 72, slot - 0)
Information on the OTHER waiting sessions:
Session 159:
  pid=24 serial=51383 audsid=43465 user: 62/TEST
  O/S info: user: tim_hall, term: winxp1, ospid: 5928:3844, machine: winxp1
            program: sqlplusw.exe
  application name: SQL*Plus, hash value=3669949024
  Current SQL Statement:
  SELECT ID FROM DEADLOCK_1 WHERE ID = 1 FOR UPDATE
End of information on OTHER waiting sessions.
===================================================

The sections in bold are of the most interest. The first section shows the blocked SQL statement in the session that detected the deadlock. The second section is a message from Oracle telling you this is an application issue, not an Oracle error. The third section lists the blocked SQL statements in the other waiting sessions. The SQL statements listed in the trace file should allow you to identify the application code that is causing the problem.

To resolve the issue, make sure that rows in tables are always locked in the same order. For example, in the case of a master-detail relationship, you might decide to always lock a row in the master table before locking a row in the detail table.

In summary, the steps necessary to identify and rectify code causing deadlocks are:

• Locate the error messages in the alert log.
• Locate the relevant trace file(s).
• Identify the SQL statements in both the current session and the waiting session(s).
• Use these SQL statements to identify the particular piece of code that is having problems.
• Alter the application code to prevent deadlocks by always locking rows in the tables in the same order.

For more information, please go through the following article.

• http://docs.oracle.com/cd/B19306_01/server.102/b14220/consist.htm#sthref2043