All, I have a table, CUSTOMER and 3 Join Indexes- CUST_JI1, CUST_JI2, CUST_JI3 defined on the same, say for columns- cust_address, cust_phone, cust_email.
During peak hours, users insert data into the CUSTOMER table via a web UI. Randomly, they suffer from time outs. Analysis showed that deadlocks are the culprit and PMON points to the 3 JIs i.e. CUST_JI1, CUST_JI2 and CUST_JI3 as the contention points. User 1 session waits up on CUST_JI1 WRITE LOCK of User 2 and so on and so forth.
Per TD text, JIs cannot be updated directly, rather they are updated post insert/updates to the base table, CUSTOMER in this case.
Since the earlier random time outs are occurring frequently now, someone, please do shed some light on how come lock contention happens on JIs?
It's one Insert in the base table plus three Inserts into the JIs, so if it's single row insert, it's a RowHash lock on the target table plus three RowHash locks on the JIs. For RowHash locks there are no Pseudo Table Locks so when two Inserts need similar locks they might end up with a deadlock.
The exact reason is hard to tell without additional info. There's an Orange Book called "Indexes and Active Ingest" which might help.