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

Advertisements

2 Comments »

  1. Hi Muhammad,

    I have pasted the deadlock graph i have seeing this every day in my alert log.Due to which i have to kill the sessions.

    Deadlock graph:
                           ---------Blocker(s)--------  ---------Waiter(s)---------
    Resource Name          process session holds waits  process session holds waits
    TX-01d5000a-002f25cd        38    2272     X            191    3648           X
    TX-0228000b-0004cedb       191    3648     X             38    2272           X
     
    session 2272: DID 0001-0026-000FA25A	session 3648: DID 0001-00BF-000AEB0A 
    session 3648: DID 0001-00BF-000AEB0A	session 2272: DID 0001-0026-000FA25A 
     
    Rows waited on:
      Session 2272: obj - rowid = 00015FF7 - AABo+5AAQAAAD/JAAB
      (dictionary objn - 90103, file - 16, block - 16329, slot - 1)
      Session 3648: obj - rowid = 00015FF7 - AABo+5AAQAAAD/PAAy
      (dictionary objn - 90103, file - 16, block - 16335, slot - 50)
     
    ----- Information for the OTHER waiting sessions -----
    Session 3648:
      sid: 3648 ser: 8273 audsid: 119293913 user: 86/AREX_APP
        flags: (0x8000041) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
        flags2: (0x40009) -/-/INC
      pid: 191 O/S info: user: oracle, term: UNKNOWN, ospid: 12251
        image: oracle@arexdb1.alansariweb.com
      client details:
        O/S info: user: oracle, term: unknown, ospid: 1234
        machine: vapp24.alansariweb.com program: JDBC Thin Client
        application name: JDBC Thin Client, hash value=2546894660
      current SQL:
      DELETE FROM REP_TEMP_BAL_ENQ_DTL WHERE SESSION_ID = :B1 AND DEBIT_BAL = 0 AND CREDIT_BAL = 0 
    

    I queried to fic the object name but it returned no rows selected

    select OBJECT_NAME,OWNER
    from dba_objects
    where DATA_OBJECT_ID=90103
    /
    
    no rows returned.
    

    Note :- REP_TEMP_BAL_ENQ_DTL this object is a global temporary table.Could you please update how to troubleshoot this issue.

    Comment by Shadab — September 22, 2014 @ 7:48 am | Reply

  2. This is a Transaction (Tx) enqueue held on X mode and waited on X mode also. This means that you have a row level lock. Probably something related with your delete from REP_TEMP_BAL_ENQ_DTL. Check if you are not issuing this delete/update on the same table from different sessions which are overlapping. Change your PL/SQL code to make two processes not deleting/updating the same row from this table at the same time.

    By the way, if you are obliged to kill a session every day this means that there is a flaw in your PL/SQL code. When PMON detects a deadlock between two sessions it ends the statement of the session that is the first one which started waiting. This session should issue a rollback to let the other session continue its treatment.

    Consequently, add a ROLLBACK in your Oracle exceptions and you will not be obliged to kill your session every day.

    Best regards

    Comment by hourim — September 22, 2014 @ 8:30 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

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.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

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

%d bloggers like this: