RE: Deadlock analysis

As Mohamed said - missing index on FK.

The "once in a blue moon" Iif it is really rare) suggests that the FK is defined with "on delete cascade" - causing the parent to delete child rows before the parent delete.
Since this is automatic the timing would usually be:

session 1:  delete child delete parent with a VERY small time window between
Session 2: delete child -- but wait for session 1 to commit - delete parent

But if you're unlucky and session 1 is descheduled by the O/S between the child and parent delete you'll get the deadlock.


Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

________________________________________
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce@freelists.org] on behalf of Martin Klier [usn@usn-it.de]
Sent: 21 January 2014 14:15
To: Oracle Mailinglist
Subject: Deadlock analysis

Hi list,

I have a deadlock situation, not easily reproducible, but nevertheless
nasty for operating the system.


I have two sessions, both are shown as "no row" in "Rows waited on"
section, but both sessions are locking each other with a SX/SSX
lock/lock request constellation.

The other fact is, that both sessions get into the deadlock recognizion
phase when executing the very same DELETE statement (by ID plus another
column that we use for a "meanwhile change" protection mechanism).

The MailingID column is the primary key, and it's absolutely made sure
that one session will not call the DELETE with the ID another one has.
The IDs for deletion are seperated in pools without overlap.


My question:
How do I explain this deadlock, what happens here?


Complication: It happens only once in a full moon, and until it came to
my attention, the data was deleted by repeating the process. So I can't
reproduce. But I have the full trace file, in fact trace files from
multiple occaisions.


Extract from the trace:


Deadlock graph:
                       ---------Blocker(s)--------
---------Waiter(s)---------
Resource Name          process session holds waits  process session
holds waits
TM-000b7897-00000000       287    1182    SX   SSX      221     554
SX   SSX
TM-000b7897-00000000       221     554    SX   SSX      287    1182
SX   SSX

session 1182: DID 0001-011F-000005E8    session 554: DID 0001-00DD-000000DA
session 554: DID 0001-00DD-000000DA     session 1182: DID 0001-011F-000005E8

Rows waited on:
  Session 1182: no row
  Session 554: no row

----- Information for the OTHER waiting sessions -----
Session 554:
  sid: 554 ser: 60325 audsid: 69793482 user: 58/OPS$IWACS_WM
    flags: (0x100041) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
    flags2: (0x40009) -/-/INC
  pid: 221 O/S info: user: SYSTEM, term: CHLUZSDB0206, ospid: 5796
    image: ORACLE.EXE (SHAD)
  client details:
    O/S info: user: abc$, term: unknown, ospid: 1234
    machine: abc program: xxx
    application name: xxx, hash value=1270299263
  current SQL:
  DELETE FROM Mailing WHERE mailingId=:1  AND modVersion=:2

----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=f62mcvsuzpghd) -----
DELETE FROM Mailing WHERE mailingId=:1  AND modVersion=:2
===================================================



Thank you very much in advance!

Martin Klier

--
Usn's IT Blog for Oracle and Linux
http://www.usn-it.de

--
http://www.freelists.org/webpage/oracle-l


--