Deadlocks on Join Indexes- How/What/Why?

Database
Enthusiast

Deadlocks on Join Indexes- How/What/Why?

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?

Thanks in advance.

Thanks,
ShyGuy
3 REPLIES
Senior Apprentice

Re: Deadlocks on Join Indexes- How/What/Why?

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.

Dieter
Enthusiast

Re: Deadlocks on Join Indexes- How/What/Why?

Thanks Dieter for shedding some light on this.

So you mean to say that at the time of insert, somehow there are potential duplicate row-hash values across users and that leads to row-hash lock contention, right?

Thanks,
ShyGuy

Enthusiast

Re: Deadlocks on Join Indexes- How/What/Why?

And Dieter, to add to my above post, what kind of additional info would one need to address this clearly?

Thanks,
ShyGuy