Oracle Database Discussions

Hi,

I'm wondering if anyone can provide any guidance here. Environment is:

Microsoft Windows 2003 Server
Oracle Standard Edition 10.2.0.4

We had a deadlock detected in our app at a client site - I'm attaching the relevant part of the trace file below. Whilst we think we have found the reason for the deadlock (looks like it's due to a cascading foreign key) I'm confused as to the lock mode that is being requested.

Here's the trace information:

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00120012-00003b59        28     475     X             29     481           S
TX-000a0029-0003508b        29     481     X             28     475           X
session 475: DID 0001-001C-000F96B0	session 481: DID 0001-001D-00000079
session 481: DID 0001-001D-00000079	session 475: DID 0001-001C-000F96B0
Rows waited on:
Session 481: no row
Session 475: obj - rowid = 00014625 - AAAUYlAAIAAAAawAAo
  (dictionary objn - 83493, file - 8, block - 1712, slot - 40)
Information on the OTHER waiting sessions:
Session 481:
  pid=29 serial=18261 audsid=202192707 user: 51/info
  O/S info: user: SYSTEM, term: our_term, ospid: 5244:940, machine: our_machine
            program: our_exe.exe
  client info: GUI
  application name: app, hash value=3864155245
  action name: our_action, hash value=3631189430
  Current SQL Statement:
  DELETE FROM TABLE_1 T WHERE T.T_ID = :B1
End of information on OTHER waiting sessions.
Current SQL statement for this session:
UPDATE TABLE_1 T SET T.STATUS_ID = :B2 WHERE T.T_ID = :B1 

It looks to me like session *481* is trying to do a delete when the deadlock is thrown:

 DELETE FROM TABLE_1 T WHERE T.T_ID = :B1

and that is requesting in S (hared) mode.

                                   ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00120012-00003b59        28     475     X             29     481           S

(also the "no row" for rows waited on)

Session 481: no row

I believed it would be in (e) X (clusive) mode (for the row being deleted). In fact yes, I set up a quick and simple deadlock test and a the final delete is waiting for (e) X (clusive) mode.

So, am I misinterpreting the trace information, or is the DELETE statement requesting the lock in S (hare) mode?!! If that's the case, I would very much appreciate a quick explanation of how/why this is...



Regards,

Ados