Re: Deadlock analysis

This looks simple to me. You have a *TM* lock held on *SX* and waited on *SSX*. This is almost always related to a *delete *from a *parent *table having a child table with an unindexed foreign key.

Look to the child tables of your Mailing table and verify that their foreign key are indexed

Best regards

Mohamed Houri

2014/1/21 Martin Klier <usn_at_usn-it.de>

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

-- 
Bien Respectueusement
Mohamed Houri

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