Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • Last Modified:

How to correct this deadlock error in sql server

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.
0
searchsanjaysharma
Asked:
searchsanjaysharma
2 Solutions
 
GanapathiCommented:
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)

OR

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

OR

Make sure you have your table's locking schema as DATAROWS
0
 
PadawanDBACommented:
I would recommend you take a look at implementing a more optimistic view of blocking/locking: http://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/.

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.
0
 
searchsanjaysharmaAuthor Commented:
tx
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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