Deadlocks are a property of SQL Server’s pessimistic concurrency scheme

I’ve encountered more than a few SQL Server based applications where deadlocks are treated as an error in almost exactly the same manner as say, a .NET null-reference exception. In most cases this means the user sees a message along the lines of: “An error has occurred, please try again”.

A deadlock is usually a recoverable condition, so this is the wrong behaviour, at least at first. Any concurrency scheme where a “thread” is able to block waiting on a lock while holding other lock(s) is potentially subject to deadlock conditions. Deadlocks and pessimistic concurrency go hand-in-hand.

More often than not a simple retry of the rolled-back transaction will succeed. An error should be allowed to bubble-up to the user only after a specified number of retries; three retries is often reasonable.

This does not mean to say that you shouldn’t endeavour to limit deadlocks in your SQL Server applications, of course you should. Rolling-back and retrying a transaction is an expensive operation that you want to avoid if at all possible but the point is, don’t give up after the first attempt.

Comments are closed.