Hi All,
There was a deadlock detected and logged and I am trying to figure out the application issue that caused it.
Version info:
-----
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for HPUX: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
Here is a snippet from the trace file:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-0016001f-00013ae8 68 892 X 229 818 S
TX-0007001c-0002c66a 229 818 X 101 929 S
TX-0010001d-000299c5 101 929 X 68 892 S
session 892: DID 0001-0044-00002E42 session 818: DID 0001-00E5-000003FD
session 818: DID 0001-00E5-000003FD session 929: DID 0001-0065-00001940
session 929: DID 0001-0065-00001940 session 892: DID 0001-0044-00002E42
Rows waited on:
Session 818: obj - rowid = 0003E8F1 - AAA+jxAEhAAAFeaAAA
(dictionary objn - 256241, file - 289, block - 22426, slot - 0)
Session 929: obj - rowid = 0003E8F1 - AAA+jxAEgAAAAFnAAA
(dictionary objn - 256241, file - 288, block - 359, slot - 0)
Session 892: obj - rowid = 0003E8F1 - AAA+jxAEgAAADIEAAA
From the SQL listed, I see that the same update is being run by the 3 sessions.
UPDATE my_table
SET col1 = TRIM (NVL (:b5, '5')),
col2 = DECODE (NVL (:b5, '5'), '1', 1, '0', 1, '4', 1, 0),
col3 = :b4
WHERE col4 = :b3 AND col5 = :b2 AND col6 = :b1;
The predicates have bind variables in them. If the bind variable is set to the same value, then I would expect this issue to occur. In this case I would expect the "objn - 256241" to point to table "my_table". However, when I lookup this in dba_objects, it points to the PK index for this table:
xxxx> select object_type
2 from dba_objects
3 where object_id = 256241;
OBJECT_TYPE
-------------------
INDEX
The questions i have are:
1. Is my assumption correct
In this case I would expect the "objn - 256241" to point to table "my_table"
2. I was going to use the rowids to check what row this is occuring on. How do I do this if I only have rowids from the index instead of the table? Is there another way to get the bind variable info from the deadlock trace file?
3. Any other suggestions about looking into this.
Thanks,
Rahul