Mohamed Houri’s Oracle Notes

September 7, 2011

Deadlock – Part 3: Non Indexed Foreign key in action

Filed under: Deadlock — hourim @ 12:46 pm

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;

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;

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
---------- ---------- ------------------- ------------- ---------------------
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
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.

Create a free website or blog at

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog


Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)