Help on Deadlock Detected.
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Help on Deadlock Detected.

  1. #1
    Join Date
    Sep 2001
    Posts
    34

    Help on Deadlock Detected.

    Hi Guys,

    I need some help in identifying the deadlock situation, details follow:

    Oracle : 9.2.0.4.0

    O/S : Sun Solaris 5.8

    There are two instances of the database, on one database there are no deadlocks detected, but the other is consistently getting deadlocked. Both have the same schema and have same init parameters. I was not able to find the OBJID given in the deadlock trace file in the database. Here is the partital trace dump.



    *** 2004-08-03 08:44:29.145
    *** SESSION ID:(12.14) 2004-08-03 08:44:29.100
    DEADLOCK DETECTED
    Current SQL statement for this session:
    Insert /*+ APPEND */ into constituent_history_tbl (partition_id, constituent_id, action_id, action_category, action_type, description, affiliation_id, list_case_id, trans_uid) VALUES ( 7, :1, :2, 'List', 'Assignment', 'ABX5 10 2004 Support', :3, 266, 'dragsecr')
    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-0012000a-00003087 19 12 X 13 18 S
    TX-00120029-00003094 13 18 X 19 12 S
    session 12: DID 0001-0013-00000006 session 18: DID 0001-000D-00000006
    session 18: DID 0001-000D-00000006 session 12: DID 0001-0013-00000006
    Rows waited on:
    Session 18: no row
    Session 12: obj - rowid = 0000B3F6 - AAAAAAAAcAAAGqrAAA
    (dictionary objn - 46070, file - 28, block - 27307, slot - 0)
    Information on the OTHER waiting sessions:
    Session 18:
    pid=13 serial=14 audsid=14644 user: 80/SENATE_DISTRICTS
    O/S info: user: , term: , ospid: 1234, machine: ldcdev2
    program:
    Current SQL Statement:

    Insert /*+ APPEND */ into constituent_history_tbl (partition_id, constituent_id, action_id, action_category, action_type, description, affiliation_id, list_case_id, trans_uid) VALUES ( 7, :1, :2, 'List', 'Assignment', 'Jan test List 5', :3, 413, 'dragsecr')
    End of information on OTHER waiting sessions.
    ===================================================

    I appreciate any help to resolve this issue, any tips are also helpful.

    Thanks
    Prasad.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Remove APPEND hint in the insert statement b/c APPEND hint forces to insert rows into the block that is above High Watermark.

    If multiple concurrent transactions insert rows in a table, then increase FREELIST value for that table.

    Tamil

  4. #4
    Join Date
    Sep 2001
    Posts
    34
    I can as well remove APPEND hint or increase the freelists, but the problem is one of the databases which is on the same machine is not deadlocking, whereas the other database is detecting a deadlock. Bothe databases are identical, except one is used for testing and the other is for developemnt.

    Prasad.

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    How do you test?

    Does this table have FK references?

    Tamil
    Last edited by tamilselvan; 08-05-2004 at 02:45 PM.

  6. #6
    Join Date
    Oct 2002
    Posts
    807
    "Session 12: obj - rowid = 0000B3F6 - AAAAAAAAcAAAGqrAAA
    (dictionary objn - 46070, file - 28, block - 27307, slot - 0)"


    Can you pull up the details of the row being waited upon? I'm interested in knowing the object and row being locked. It might very well be the index on it for all you know.

    select object_name, object_type from dba_objects where object_id=46070;

    select * from table_name where rowid='AAAAAAAAcAAAGqrAAA';

  7. #7
    Join Date
    Sep 2001
    Posts
    34
    Hi Axr2,

    Thanks for your reply, Yes they are waiting for Indexes blocks. There are multiple indexes on this table, each time it is deadlocked it is referring to a different Index. I don't know if there are any problems with indexes. I am trying to rebuild all the indexes and give it a try.

    Hi Tamil,

    This table has FK references and they are indexed as well and thanks for your response.

    Thanks,
    Prasad.

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    May be you need to increase INITRANS value in the indexes.

    Tamil

  9. #9
    Join Date
    Oct 2002
    Posts
    807
    AFAIK, 9.2.0.4 DEFAULTS your index initrans values to 2.

  10. #10
    Join Date
    Jul 2004
    Location
    bangalore
    Posts
    24
    table and index initran is default to 2 in 9.2.0.4.
    Since he is in 9.2.0.4 he should be in LMT, if so freelist doesn't make any sense.

    Thanks and Regards,
    Satheesh Babu.S

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width