Martin 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@xxxxxxxxx> > 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 > > -- > //www.freelists.org/webpage/oracle-l > > > -- Bien Respectueusement Mohamed Houri