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.
searchsanjaysharmaAsked:
Who is Participating?
 
PadawanDBAOperational DBACommented:
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
 
GanapathiFacets DeveloperCommented:
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
 
searchsanjaysharmaAuthor Commented:
tx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.