Thanks for the confirmation.
You were (and ARE) absolutely right! Look at the schema's explanation below (all superfluous details are removed).
BILLS is a parent table. ROAMER_CALLS(object_id 713575), ROAMER_SERVICES, RMR_ACTIONS are the child tables Direct relationships: ROAMER_CALLS.BILL_ID references to BILLS.ID and ROAMER_SERVICES.BILL_ID references to BILL.ID also.
Indirect relationships: ROAMER_CALLS.RMR_ACTION_ID references to RMR_ACTIONS.ID and ROAMER_SERVICES.RMR_ACTION_ID references to RMR_ACTIONS.ID also. Finally RMR_ACTIONS.BILL_ID references to BILLS.ID. Everywhere column BILL_ID has an index.
Before I sent you the previous letter, I had created a missing index on ROAMER_CALLS.RMR_ACTION_ID. But I still had the deadlock. It was my mistake, I didn't check the new trace file. The deadlock appeared on ROAMER_SERVICES! Then I created index on ROAMER_SERVICES.RMR_ACTION_ID and deadlocks disappeared.
Spasibo (thank you very much) for your help. You pointed me at the right direction. I have never heard before that "indirect relationship" is a possible cause of deadlocks.
OK, I've just posted a 7.3 version of the script (untested). Please use it to check. If that fails to show anything, it may be that Oracle is not using the concatenated indexes to avoid taking the shared lock on one of the child tables for the delete from BILLS. This was a long standing bug. My memory is that it was fixed in all 7.3 releases, but maybe it was later than that. Perhaps one of the internal Oracle people on the Ixora Answers mailing list who has access to WebIV could check for us?
Thank you for your response. Unfortunately, I can't use your script. The schema is on 7.3.3.4.0 (Solaris). But I've just checked all the child tables. All of them have single-column or concatenated (the first column references to BILLS.ID) indexes. There are no unique columns on BILLS except primary key (BILLS.ID). Please, let me know if you have any ideas.
The deadlock is clear. Both sessions have an SX lock on table 713575 and are trying to convert it to SSX. Session 81 is trying to delete from BILLS at the time. As you guessed, the only reason why an S lock would be required on table 713575 at this point is a missing foreign key index. Given that the direct foreign key relationship is supported by a valid index, the only alternative is that there is an indirect foreign key relationship that is not. That is, table 713575 references table X, and table X references BILLS, and one of those foreign keys is not properly indexed. Try running the new APT script missing_fk_indexes.sql to see if you can find it.
Will you be so kind to explain the following problem? Here is a piece of my trace.
Tue Oct 24 10:32:55 2000
*** SESSION ID:(81.14858) 2000.10.24.10.32.55.000
DEADLOCK DETECTED
Current SQL statement for this session:
DELETE FROM BILLS WHERE ID = :b1
----- PL/SQL Call Stack -----
object line object
handle number name
84153430 805 package body TALQ.CREDIT_NOTE_SUPPORT
85cd49e0 239 package body TALQ.CHARGING
860edc98 595 package body TALQ.BALLING
80780474 1 anonymous block
80780474 1 anonymous block
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
TM-000ae367-00000000 21 81 SX SSX 26 73 SX SSX
TM-000ae367-00000000 26 73 SX SSX 21 81 SX SSX
Rows waited on:
Session 73: no row
Session 81: no row
===================================================
PROCESS STATE
-------------
Process global information:
process: 8805672c, call: 880c9e48, xact: 875ce6f8, curses: 880908a4, usrses: 880908a4
----------------------------------------
SO: 8805672c, type: 1, owner: 0, flag: INIT/-/-/0x00
(process) Oracle pid=21, calls cur/top: 880c9e48/87a2c0d0, flag: (0) -
int error: 0, call error: 0, sess error: 0
(latch info) wait_event=0 bits=10
holding 80001a20 Parent+children enqueue hash chains level=4 state=busy
O/S info: user: oracle, term: console, ospid: 21643
OSD pid info: Unix process pid: 21643, image: oracleQC0
You can see that the resource names are the same. What does it mean in this case? What pieces of the trace should I check first? I tried tracking this enqueue name through the trace file and caught the following:
SO: 875a6080, type: 9, owner: 875ce6f8, flag: INIT/-/-/0x00
DML LOCK: tab=713575 flg=01 chi=0
his[0]: mod=3 spn=53889
(enqueue) TM-000AE367-00000000
lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
res:881108bc, mode: SX, req: SSX, prv: 881108d4, sess: 880908a4, proc: 8805672c
The table (object_id = 713575) has a foreign key to BILLS.ID, but the field of the foreign key has a valid index. I'm confused. What things should I check else? Maybe I'm missing something important?