I need some help in identifying the deadlock situation, details follow:
Oracle : 184.108.40.206.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
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:
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:
pid=13 serial=14 audsid=14644 user: 80/SENATE_DISTRICTS
O/S info: user: , term: , ospid: 1234, machine: ldcdev2
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.
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.
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.
This table has FK references and they are indexed as well and thanks for your response.