• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 212
  • Last Modified:

Prevent a table from getting locked on Insert or Update

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

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

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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now