How to catch SqlException caused by deadlock?

The SQL error code for a deadlock is 1205 so you'd need to handle the SqlException and check for that. So, e.g. If for all other types of SqlException you want the bubble the exception up: catch (SqlException ex) { if (ex. Number == 1205) { // Deadlock } else throw; } A handy thing to do to find the actual SQL error code for a given message, is to look in sys.

Messages in SQL Server. E.g. SELECT * FROM sys.

Messages WHERE text LIKE '%deadlock%' AND language_id=1033 An alternative way to handle deadlocks (from SQL Server 2005 and above), is to do it within a stored procedure using the TRY...CATCH support: BEGIN TRY -- some sql statements END TRY BEGIN CATCH IF (ERR_NUMBER() = 1205) -- is a deadlock ELSE -- is not a deadlock END CATCH There's a full example here in technet of how to implement deadlock retry logic purely within SQL.

Because I suppose you possibly want to detect deadlocks, to be able to retry the failed operation, I like to warn you for a little gotcha. I hope you’ll excuse me for being a bit of topic here. A deadlock detected by the database will effectively rollback the transaction in which you were running (if any), while the connection is kept open in .NET.

Retrying that operation (in that same connection), means it will be executed in a transactionless context and this could lead to data corruption. It's important to be aware of this. I think it’s best to consider the complete connection doomed in case of a failure caused by SQL.

Retrying the operation can only be done on the level where the transaction is defined (by recreating that transaction and its connection). So when you are retrying a failed operation, please make sure you open a completely new connection and start a new transaction.

I use linq to do the query and updates in the following order. Sub Commit() Try Transaction Scope Serialized Begin Transaction Check BusinessRule1 Check BusinesRule2 if BusinessRulesFailed { Transaction. Rollback } else { Query = db.

Out db.SubmitChanges() Transaction.Commit() } Catch DeadLockException in 5 seconds: call Commit() End Sub.

1 please read the FAQ. This is not a discussion forum, so your "reply" is not appropriate if it doesn't answer the question. If you have a question - ask it.

– John Saunders Sep 30 '10 at 18:36 This is an answer. Just wasn't sure what Steven meant by 'don't use the same connection' – Dasiths Oct 1 '10 at 3:14.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.