How to avoid sql deadlock?

Question

I am using MS-SQL 2008 and for coding am using C# in a web project.

Currently, I got stuck with sql deadlock.

I have 2 transactions, one of two is a long transaction.

When both transactions run simultaneously, deadlock occurs and short transaction will be chose to be killed automatically.

My Current solution, I am catching the exception of deadlock and redo the short transaction, it run well, just take some time.

But, I wonder that if there is any solution to avoid deadlock from the root cause?

I hope this helps . msdn.microsoft.com/en-us/library/ms191242(v=sql.105).aspx — kostas ch.
– kostas ch., Commented Jun 11, 2013 at 7:58
try checking that your table has a primary key. Can adding a primary key identity column solve deadlock issues? — Misterhex
– Misterhex, Commented Jun 11, 2013 at 7:59
@AzharKhorasany: is it important? In this case, both of them are insert. — kidgu
– kidgu, Commented Jun 11, 2013 at 9:34

Community · Accepted Answer · 2017-04-13 12:42:40Z

6

No, you can't avoid deadlocks entirely, only mitigate them.

Using RCSI isolation mode. This is basically MVCC, but you have to beware that SQL Server will still use the ANSI ACID modes on occasion (DBA.SE answer). This mode stops writers blocking readers but will not help with writer-writer mutual blocks.
Client retry logic. The classic way
Try to do table updates in the same order eg T1 -> T2 always
Avoid long transactions: is the client round tripping a lot and keeping the transaction open for example. You can mitigate this with stored procedures, better indexes or simply tune the query

edited Apr 13, 2017 at 12:42

CommunityBot

11 silver badge

answered Jun 11, 2013 at 8:25

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

kidgu Over a year ago

"Client retry logic. The classic way", you mean that Try ... catch for deadlock exception and retry the killed transaction is right way, isn't it?

kidgu Over a year ago

Hi gbn, if I have a stored procedure, and both transactions invoke that with different input, and deadlock occurs, so is there any methodology of lock to prevent deadlock?

gbn Over a year ago

@kidgu: create a new question and add the code, tables and indexes please. It is probably an index issue. Thanks

kidgu Over a year ago

It is a pretty huge project, I got difficulty to extract necessary information to provide you. Is deadlock graph enough?

gbn Over a year ago

wow, no. It means you have a lot of wasted space used in memory and on-disk. And it could be a temporary fix that hides the real problem. But if it works for you, hey. Without the tables, indexes and code we can't help any more.

|

Collectives™ on Stack Overflow

How to avoid sql deadlock?

1 Answer 1

9 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Linked

Related