Deadlock analysis

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

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 Received on Tue Jan 21 2014 - 15:15:57 CET