2

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?

Keren Caelen
  • 1,466
  • 3
  • 17
  • 38
kidgu
  • 413
  • 1
  • 8
  • 18
  • I hope this helps . http://msdn.microsoft.com/en-us/library/ms191242(v=sql.105).aspx – kostas ch. Jun 11 '13 at 07:58
  • 1
    try checking that your table has a primary key. [Can adding a primary key identity column solve deadlock issues?](http://stackoverflow.com/questions/10737324/can-adding-a-primary-key-identity-column-solve-deadlock-issues) – Misterhex Jun 11 '13 at 07:59
  • What are your statements? Are they updates, inserts ?? – Azhar Khorasany Jun 11 '13 at 08:06
  • @AzharKhorasany: is it important? In this case, both of them are insert. – kidgu Jun 11 '13 at 09:34

1 Answers1

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

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
  • "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 Jun 11 '13 at 09:50
  • 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? – kidgu Jun 12 '13 at 04:47
  • @kidgu: create a new question and add the code, tables and indexes please. It is probably an index issue. Thanks – gbn Jun 12 '13 at 07:00
  • It is a pretty huge project, I got difficulty to extract necessary information to provide you. Is deadlock graph enough? – kidgu Jun 12 '13 at 07:55
  • In this link, http://support.microsoft.com/kb/169960, my current situation is somehow similar to Example 2 or 3. In that article, the solution is enable or disable IRL (by sp_tableoption 'example1', 'insert row lock', true), but as my investigation 'insert row lock' is no longer supported, and a funny fact that Example 3 contradicted the Example 2, seems this one is also not good solution. @gbn: Is it related to Index issue which you mentioned? Thanks so much for kind sharing knowledge! – kidgu Jun 13 '13 at 04:38
  • Hi gbn, by setting fillfactor of table = 50 % (and Pad index), then I reorganize all indexes of that table. --> Deadlock seems not to occur any longer. According to you, is this solution fine? – kidgu Jun 13 '13 at 07:29
  • 2
    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. – gbn Jun 13 '13 at 07:42
  • Hi gbn, you are correct. :( that did not work, just some test cases, it happened again. I'll create a new question with necessary information. Thanks for your advice so far. – kidgu Jun 13 '13 at 08:08
  • You're welcome. Please add the link to the new question here. – gbn Jun 13 '13 at 08:13