Fixing Locking and Blocking Issues in SQL Server - Part 5 - Application Deadlock Handling

Fixing Locking and Blocking Issues in SQL Server - Part 5 - Application Deadlock Handling

This is part 5 in a series of posts:

  • Part 1 covered being sure there is a locking and blocking issue
  • Part 2 covered the impact of RCSI
  • Part 3 looked at the impacts of indexing on locking and blocking
  • Part 4 looked at what deadlocks really are and how SQL Server handles them

Today, though, I want to look at how applications should deal with deadlocks.

Application Deadlock Handling

In the last post, I talked about what deadlocks are (under the covers) and how SQL Server “resolves” them.

In the end, the statement that is killed by the server fails and an error  (1205) gets passed back to the client application.

The problem is that most client applications aren’t prepared for the error, and then just blow up themselves. We’ll talk more about avoiding deadlocks next time but with a complex application and many concurrent users, you really aren’t going to stop them happening.

So your application should be prepared to deal with them.

What’s the correct action to take when a deadlock occurs? It’s just to retry the transaction that you were trying to apply, after a short delay. Ideally, that delay will also be somewhat random and will be increasing in duration with each retry.

If deadlocks are leaking out to end users when they’re running an application, the developers haven’t done their jobs.

There are several situations where applications should apply retry logic. Deadlocks are just one issue.

Data Modification Logic

Most applications today though, update databases like this:

  • Start a transaction
  • Send the update
  • Commit the transaction and hope for the best

For the religious readers, perhaps praying should be part of that list as well.

But it’s not good enough.

Better logic is something like:

  • While we haven’t yet applied the transaction and haven’t exhausted our retries
    • Start the transaction
    • Send the update
    • Commit the transaction
  • Trap any errors, and if a failure occurs and it was caused by an error that it’s worth retrying, try again

Applications need to plan for failure, and then be happily surprised when the failure doesn’t occur.

In the next post, I’ll talk about things you can do to avoid deadlocks in the first place.

2019-05-16