Deadlocks caused by Missing Indexes in SQL Server

(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.)

In today’s blog posting I want to demonstrate how missing indexes on tables can cause deadlocks in SQL Server. For the preparation of the test scenario the following code creates 2 tables and populates both tables with 4 records.

Before I show you the actual deadlock repro, the following code shows a simple UPDATE statement that updates a specific row in the first table.

Because we have no index defined on Column2, the Query Optimizer has to choose a Table Scan operator in the execution plan to find the qualifying rows for our UPDATE statement:

Execution Plan for an UPDATE statement

This means that we have to scan the complete heap table to find the one row we want to update. In that case SQL Server locks the first row in the table with an Exclusive Lock. When you run in a different session a SELECT statement where you reference another “later occurring” row in the heap table, the Table Scan operator will block, because first you have to read all the “earlier occurring” rows of the heap table to actually get to the row that you have logically requested in your query.

A Table Scan by default means that you have to scan the whole table, and therefore you have to acquire a Shared Lock on every record – even on records that you have not logically requested. This situation can lead now to a deadlock situation if you access the 2 tables in the 2 different sessions in a different order, and when you try to write and read from both tables. The following code shows the transaction from the first query:

And the following listing shows the code from the 2nd transaction:

As you can see from both transactions, the two tables are accessed in a different order. If the timing is now right, running both transactions at the same time can lead to a deadlock situation. Imagine the following execution sequence:

  1. The first transaction runs the UPDATE statement on Table1.
  2. The second transaction runs the UPDATE statement on Table2.
  3. The first transaction runs the SELECT statement on Table2. This SELECT statement will block, because the Table Scan operator wants to acquire a Shared Lock on a row that is already exclusively locked by the second transaction.
  4. The second transaction runs the SELECT statement on Table1. This SELECT statement will block, because the Table Scan operator wants to acquire a Shared Lock on a row that is already exclusively locked by the first transaction.

The following picture illustrates this deadlock situation.

Deadlock caused by a missing index

Both transaction are now blocking each other, therefore you cause a deadlock in SQL Server. In that case the Deadlock Monitor background process kicks in, and performs a rollback of the cheapest transaction (based on the number of bytes that the transaction has written to the Transaction Log).

You can resolve this deadlock very easily by providing an index for the Column2 in both tables. In that case SQL Server can perform a Seek operation to find the qualifying rows, and can therefore bypass already locked rows in the leaf level of the index when you perform the SELECT statement:

The following picture illustrates how the locking situation looks now:

A index provides you an alternative data access path

With a Seek operation you can just bypass locked rows in the leaf level of the index, and you can avoid the deadlock that we have been talking about. Therefore it’s always very important that you have a detailed look at your indexing strategy when you see deadlock situations in your database. Indexing is one of the most important things in SQL Server – always remember that!

Like or share this blog posting to get the source code.

Thanks for your time,

-Klaus

3 Comments

  • Torsten

    Hi Klaus,

    I wondering if this will only work with the two indexes instead:

    CREATE NONCLUSTERED INDEX idx_Column2 ON Table1(Column2) INCLUDE(column1)
    CREATE NONCLUSTERED INDEX idx_Column2 ON Table2(Column2) INCLUDE(column1)

    With your index definition I still get the deadlock but maybe I am doing something wrong…

  • Torsten

    I guess the issue is that the non clustered index needs to update the Heap(RID) cause it is not a covered index and therefor you still get the X lock on the heap which leads to a deadlock.

  • Torsten

    BTW:
    CREATE CLUSTERED INDEX NCLTable1_Col2 ON Table1(Col2)
    GO
    CREATE CLUSTERED INDEX NCLTable2_Col2 ON Table2(Col2)
    GO

    will work as well 😉

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