Write/Read Deadlocks

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

I love to write about Deadlocks, because there are so many different ways and situations in which you can end up with Deadlocks if you don’t think carefully when writing your code. In today’s blog post I want to show you a very simple Deadlock that can occur when you want to write and read records from the same table in a different order.

A Write/Read Deadlock

The following listing shows a simple table definition statement, where I also insert 2 records.

Imagine now the following situation: you have 2 queries, which access that table for writing and reading, but you access the records in a different order. The first transaction performs the following:

  • UPDATE the 1st record
  • SELECT the 2nd record

And a second transaction accesses both records in the opposite order:

  • UPDATE the 2nd record
  • SELECT the 1st record

The following code shows you both transactions:

If the timing is right (wrong), it’s quite easy to trigger a Deadlock here, because both transactions acquire an Exclusive Lock on the 1st and 2nd record, and then they are not able to acquire the Shared Lock for reading on the other record – Deadlock!

How can you solve that Deadlock? There are many ways:

  • Access the records in the same order (1st record, 2nd record) across all of your transactions
  • Perform the SELECT statement outside of the transaction
  • Enable Read Committed Snapshot Isolation to get rid of the Shared Locks during SELECT statements

Summary

In today’s blog post I wanted to show you how easy it is to trigger a Deadlock in SQL Server when you access records in a table for writing and reading in a different order across transactions. When you write your queries, you always have to think very carefully and implement your queries accordingly. There is always someone else who can block you.

Thanks for your time,

-Klaus

3 Comments

  • Thanks so much Klaus. Please I would like you to elaborate a little on the down sides of using Read committed Snapshot Isolation level.

    • Klaus Aschenbrenner

      Hello Kenneth,

      Thanks for your comment.
      One of the downsides of RCSI is the overhead introduced in the Version Store in TempDb.
      But normally the overhead should be smaller as the problems (Deadlocks, Blocking) that you are trying to solve with RCSCI.

      Thanks,

      -Klaus

  • Hi Klaus

    How does RCSI behave if you are using sp_getapplock?

It`s your turn

Your email address will not be published. Required fields are marked *

SQLpassion

Copyright © 2015 by SQLpassion · Klaus Aschenbrenner · Imprint · Offerings · Academy · Contact · Go to Top