Oracle Database Discussions

Hi all.

I'm facing a deadlock which I'm having trouble to figure out. At the end of this message is part of my oracle deadlock file. Right below are my general comments.

My application consists of many identical processes performing the same operation, but on different sets of data. The particular deadlock file at the end has three processes in deadlock. All three processes on the same exact sql statement, which is an update.
In theory, it should not happen that more than one process perform the same update with the same primary key (ID_FK1 and ID_FK2 form the PK). However, as you can see in the file, this seems to be happening with sessions 423 and 63. I say that because the file shows the same rowid and dictionary obj for these sessions. An interesting thing is that when I search for any of the rowids in the file, I can't find them in TABLE_A (the one which is being updated). Why?

I also wonder the reason the blocker 'holds' are described by an 'X' and the waiter 'holds' are described by an 'S'. Shouldn't the blocker 'holds' be a 'RX' (row exclusive table lock)? In addition, what important information can I take from the transaction id (the TX-....)?

A last question is if there's a way to identify the values of the parameters being passed to the update (b1, b2, b3 and b4). I did a few queries on some oracle views at the moment the deadlock was happening but I couldn't find one with this information (although I must say that I didn't have access to many dba views at the moment, so my attemps were not the best as they could have been). Attaching a debugger at runtime is not also a good idea because the application is in production. (I haven't been able to reproduce it in my testing environment).

Well, I'd appreciate any suggestion that helps me precisely identify this deadlock.

Thank you for your time,

Leandro Melo.


*** SESSION ID:(67.33860) 2007-09-06 06:00:06.460
DEADLOCK DETECTED
Current SQL statement for this session:
update TABLE_A set ID_PARAM=:b1, DT_OPERATION=TO_DATE(:b2,'YYYYMMDD') where (ID_FK1=:b3 and ID_FK2=:b4)
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00280018-000df8dc 841 76 X 48 423 S
TX-005e001b-00005ecf 48 423 X 27 63 S
TX-00180002-0017bb69 72 63 X 841 76 S
session 76: DID 0001-0094-000006A4 session 423: DID 0001-0054-00001A6F
session 423: DID 0001-0054-00001A6F session 63: DID 0001-0048-000024F2
session 63: DID 0001-0048-000024F2 session 76: DID 0001-0094-000006A4
Rows waited on:
Session 423: obj - rowid = 0000E0E9 - AAAODpAMvAAAAAAAAA
(dictionary objn - 98658, file - 815, block - 0, slot - 0)
Session 63: obj - rowid = 0000E0E9 - AAAODpAMvAAAAAAAAA
(dictionary objn - 98658, file - 815, block - 0, slot - 0)
Session 76: obj - rowid = 0000E261 - AAAOJhAORAAAAAAAAA
(dictionary objn - 45455, file - 913, block - 0, slot - 0)
Information on the OTHER waiting sessions:
Session 423:
pid=48 serial=46832 audsid=112200496 user: 80/MYUSER
O/S info: user: myuser, term: , ospid: 12222, machine:
program: PEXP@stak (TNS V1-V3)
application name: PEXP@stak (TNS V1-V3), hash value=0
Current SQL Statement:
update TABLE_A set ID_PARAM=:b1, DT_OPERATION=TO_DATE(:b2,'YYYYMMDD') where (ID_FK1=:b3 and ID_FK2=:b4)
Session 63:
pid=72 serial=35788 audsid=112198034 user: 80/MYUSER
O/S info: user: myuser, term: , ospid: 25836, machine: stak
program: PEXP@stak (TNS V1-V3)
application name: PEXP@stak (TNS V1-V3), hash value=0
Current SQL Statement:
update TABLE_A set ID_PARAM=:b1, DT_OPERATION=TO_DATE(:b2,'YYYYMMDD') where (ID_FK1=:b3 and ID_FK2=:b4)