DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2001
    Ludhiana (Punjab) India

    Question Frequent Dead Locks In The Application

    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

  2. #2
    Join Date
    Nov 2000
    Baltimore, MD USA
    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
    Christopher R. Long
    But that's just my opinion. I could be wrong

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width