Prevent a table from getting locked on Insert or Update

Posted on 2014-08-26
Last Modified: 2014-09-04
I have a stored procedure that is being called from a Great Plains application that updates and inserts into a custom table.

The issue is that on occasion the table for some reason gets locked and starts blocking SQL connections until all user connections are terminated.

How is the best way to provide inserts and updates and prevent table locking issues.

I have attached the SPROC in question

Question by:jdr0606
    LVL 29

    Accepted Solution

    You have open transactions and so SQL must "protect" the table.
    Look into transaction isolation levels
    LVL 68

    Expert Comment

    Verify that you have an index on udtUserPrefS with a key of UserID and including at least column CompanyID.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Suggested Solutions

    If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now