Deadlocks on Join Indexes- How+What+Why?


Deadlocks on Join Indexes- How+What+Why?

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?

Thanks in advance.