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


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,


It`s your turn

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


Copyright © 2018 by SQLpassion e.U. · Imprint · Offerings · Academy · Contact · Data Protection · Go to Top