A recent question on otn about deadlock came up last week. The deadlock graph was as such that I was wondering if I have already encountered it before.
Deadlock graph: ---------Blocker(s)-------- ---------Waiter(s)--------- Resource Name process session holds waits process session holds waits TX-001a0002-0002a0fe 196 197 X 166 1835 S TM-0000c800-00000000 166 1835 SX 196 197 SX SSX
Albeit it is an incomplete deadlock graph because it doesn’t present the type of sql session 197 and 1835 were executing before they deadlocked, we can point out that this deadlock involves two types of locks Transaction (TX) and DML (TM) locks. I have never seen yet both type of locks within the same deadlock graph.
Knowing that TM enqueue can almost always be due to a DML operation on a parent–child table where the foreign key is not indexed, I asked then the Original Poster (OP) to check using Tom Kyte script if his OLTP application contains non indexed foreign keys. But I was a little bit struggled when the OP said that the deadlock occurs on an insert statement. Thought that he was unable to feed us with the sql the other session was doing before the deadlock occurred. In a follow up to the same thread, Jonathan Lewis gave a possible scenario for this kind of deadlock. I decided to implement this scenario and to analyze the generated trace file. I opened two sessions (session3 is just to select from v$lock) and followed the steps described below:
session1 > select distinct sid from v$mystat; SID ---------- 1074 session1 > create table parent (x number primary key); Table created. session1 > create table child (y number references parent); Table created. session1 > insert into parent values (1); 1 row created. session1 > insert into parent values (2); 1 row created. session1 > insert into parent values (3); 1 row created. session1 > insert into child values (1); 1 row created. session1 > insert into child values (3); 1 row created. session1 > commit; Commit complete. session1 > delete from child where y = 1; 1 row deleted. session1 > delete from parent where x = 1; 1 row deleted.
At this step there are still no locks acquired as it can be verified via a simple query against v$session and v$lock
session3 > start getlocks.sql no rows selected
Now, open a new session (session2) and issue the following insert
session2 > select distinct sid from v$mystat; SID ----- 333 session2 > insert into child values (1);
session2 (SID 333) starts hanging!!! From session3, we can see that a lock has been acquired
session3 > start getlocks.sql SID WSID LOCK_TYPE MODE_HELD MODE_REQUESTED ---------- ---------- ------------------- ------------- --------------------- 1074 333 Transaction Exclusive Share
Then, go back to session 1 (SID 1074) and issue the following delete
session1 > delete from parent where x = 2;
The last delete hangs at about 3 seconds before it successfully complete. If you manage to check the new lock situation before the deadlock happens you will see the following picture:
session3 > start getlocks.sql SID WSID LOCK_TYPE MODE_HELD MODE_REQUESTED ---------- ---------- ------------------- ---------------------------------------- 333 1074 DML Row-X (SX) S/ROW-X (SSX) 1074 333 Transaction Exclusive Share
And finally after about 3 seconds of wait, session2 with SID 333 has been deadlocked and its transaction stopped by Oracle
insert into child values (1) * ERROR at line 1: ORA-00060: deadlock detected while waiting for resource
The corresponding generated deadlock graph looks like:
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-00011fe5-00000000 28 333 SX 27 1074 SX SSX --> the session timed out TX-000a0013-0000039d 27 1074 X 28 333 S --> the persistent session session 333: DID 0001-001C-00000006 session 1074: DID 0001-001B-00000030 session 1074: DID 0001-001B-00000030 session 333: DID 0001-001C-00000006 Rows waited on: Session 333: no row Session 1074: no row ----- Information for the OTHER waiting sessions ----- Session 1074: sid: 1074 ser: 93 audsid: 540090 user: 84/MOHAMED flags: 0x8000045 pid: 27 O/S info: user: SYSTEM, term: FSC401216102802, ospid: 3708 image: ORACLE.EXE (SHAD) client details: O/S info: user: FSC401216102802\Mohamed, term: FSC401216102802, ospid: 2816:748 machine: WORKGROUP\FSC401216102802 program: sqlplus.exe application name: SQL*Plus, hash value=3669949024 current SQL: delete from parent where x = 2 ----- End of information for the OTHER waiting sessions ----- Information for THIS session: ----- Current SQL Statement for this session (sql_id=6cnrmyxw03k6f) ----- insert into child values (1) ===================================================
Since then, I know that an unindexed foreign key can present a deadlock situation with a graph showing both TX and TM enqueue.
Mohamed Houri’s Oracle Notes 