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 sessionsStart 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_1table, it pauses for 30 seconds, then attempts to get a lock on a row in the
DEADLOCK_2table. The second piece of code does the same thing but in reverse, locking a row in the
DEADLOCK_2table, then the
DEADLOCK_1table. The call to the
DBMS_LOCK.SLEEPprocedure 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.
In addition to the deadlock error reported to the session, a message is placed in the alert log.ERROR at line 1: ORA-00060: deadlock detected while waiting for resource ORA-06512: at line 16
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.