Frequent Dead Locks In The Application

[sethi]

Hello I Am Gurpreet Singh Sethi Working As A DBA. At My Work Place We Are Running A Gurgaon Based Company's ERP Package. In That Package We Have A Table MRN . Let Me Discribe

Gate Entry Code Is refered In MRN Table
Mrncode Is refered In Debit Note Table
Mrncode Is refered In VOucher Table
Mrncode Is refered In Material Issue Table

So As A Whole MRN Is The main Table Refered At Several Other table By FOreign Key. When Ever sone One Do any Thing On Mrn Table All Other Table Get Locked And Soon Results In Dead Lock Situation. I have Alredy make Indexes On Reference Keys

All Hit Ratios are fine and up to the mark.


Any Suggetion

[chrisrlong]

Why would modifying the parent table lock the children? The only case is if you don't have indexes on the FKs and are modifying the key (VERY BAD) or deleting records. In either case, since you said you have the FKs indexes, there should be no locks on the children.

Further, even if there were locks, one would still wonder why you would have deadlocks. You have to be messing with the same records to have deadlocks.

My guess is that you have bitmap indexes in an OLTP environment. This is a BIG no-no. Bitmap indexes lock multiple rows when updating only one - extremely bad for OLTP.

So, if you only have B*Tree indexes and all your FKs are indexed, then the application is causing its own deadlocks. This risk of deadlocks can be greatly reduced by always locking tables in the same order, but this is an application issue. Further, applications usually employ some larger workflow controls to keep multiple people from messing with the sane records in the first place, before it becomes a database problem.

- Chris