Help on Deadlock Detected.
I need some help in identifying the deadlock situation, details follow:
Oracle : 220.127.116.11.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.