How to correct this deadlock  error in sql server

Posted on 2014-04-28
Last Modified: 2014-05-15
I have and table consisting of 60000 records.
Now I have 560 users who access any of these records from 60000 rows. And the procedure which executes just update the flag field with 1.

Some times the server  gives dead lock error.
How to correct this.
Question by:searchsanjaysharma

Assisted Solution

Ganapathi earned 250 total points
ID: 40026744
As the deadlock happens only SOMETIMES, we will have to go with multiple options.

If you don't worry about the integrity of the data, then have WITH(NOLOCK)


Create a Non-Clustered Index on the Flag field that is getting updated


Make sure you have your table's locking schema as DATAROWS
LVL 10

Accepted Solution

PadawanDBA earned 250 total points
ID: 40027392
I would recommend you take a look at implementing a more optimistic view of blocking/locking:

A deadlock scenario is a little different in so far as it is going to occur in the event of similar issues: transaction1 locks object1, transaction2 locks object4, transaction1 needs to lock object4 and transaction2 needs to lock object1.  Neither can finish while the other remains.

Author Closing Comment

ID: 40067340

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

809 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