Forum Stats

  • 3,783,353 Users
  • 2,254,762 Discussions
  • 7,880,372 Comments

Discussions

Deadlock : objn in trace indicates index

421723
421723 Member Posts: 29
edited Jan 14, 2009 12:35PM in General Database Discussions
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
Tagged:

Answers

  • 591186
    591186 Member Posts: 3,668 Silver Trophy
    To view the bind variable,

    v$sql_bind_capture
    select substr(value_string,1,40) from v$sql_bind_capture where sql_id=’’;
  • 3530
    3530 Member Posts: 459
    Hi Rahul,

    Can you give details of Index? Type of Index, columns etc

    I am not sure to get bind variable values from trace file.

    Dilip Patel.
  • 421723
    421723 Member Posts: 29
    Dilip,

    Sorry I missed the index info details.

    It is a B-tree index. I am translating the table/index/column names from original in this post. Going by that the index would look something like this:
    CREATE UNIQUE INDEX my_index ON my_table
      (
        col4                   ASC,  --corresponds to predicate in update stmt
        col6                   ASC,
        col5                   ASC,  --corresponds to predicate in update stmt
        col7                   ASC,
        col8                   ASC,
        col9                   ASC
      )
    /
    Thanks,
    Rahul
  • 421723
    421723 Member Posts: 29
    Thanks Anantha.

    I don't see anything for my sql in there right now.
    select name, position, substr(value_string,1,40) 
    from v$sql_bind_capture
    where sql_id = '4zp9shk74kua5';
    If i did, how do I get the bind variable values for each session from their execution a few hours ago?

    Thanks,
    Rahul
This discussion has been closed.