Help on Deadlock Detected.

[pdh]

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.

[marist89]

http://metalink.oracle.com/metalink/...T&p_id=62365.1

[tamilselvan]

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

[pdh]

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.

[tamilselvan]

How do you test?

Does this table have FK references?

Tamil

[Axr2]

"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';

[pdh]

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.

[tamilselvan]

May be you need to increase INITRANS value in the indexes.

Tamil

[Axr2]

AFAIK, 9.2.0.4 DEFAULTS your index initrans values to 2.

[shans]

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