Deadlock caused by missing indexes in SQL Server

In today's article I want to demonstrate how missing indexes on tables in SQL Server cause deadlocks (deadlock). To prepare the test scenario, the following code creates 2 tables, and then 2 tables insert 4 records.

1 --Create a table without any indexes2 CREATE TABLETable13 (4Column1INT,5Column2INT6 )7 GO8 9 --Insert a few recordTen INSERT  intoTable1VALUES(1,1) One INSERT  intoTable1VALUES(2,2) A INSERT  intoTable1VALUES(3,3) - INSERT  intoTable1VALUES(4,4) - GO the  - --Create a table without any indexes - CREATE TABLETable2 - ( +Column1INT, -Column2INT + ) A GO at  - --Insert a few record - INSERT  intoTable2VALUES(1,1) - INSERT  intoTable2VALUES(2,2) - INSERT  intoTable2VALUES(3,3) - INSERT  intoTable2VALUES(4,4) in GO

Before I repeat the deadlock to you, look at the following code, which is a simple UPDATE statement that updates a specified row in the 1th table.

1 -- acquires an Exclusive Lock on the row 2 UPDATE SET = 3 WHERE = 1

Because there is no index definition on COLUMN2, for our UPDATE statement, the query optimizer must select the table scan operator in the execution plan to find the matching records:

That means we have to scan the entire heap table to find the rows we want to update. In that case, SQL Server locks the 1th row in the table with an exclusive lock (Exclusive Lock) . When you execute a SELECT statement in a different session, referencing a row in another heap that "will occur", the table scan operator blocks because first you have to read all the rows in the heap table "occurred", that is, to get the rows of the logical request in your query.

--  This query now requests a Shared lock, but get ' s blocked, because the other session/transaction have an Exclusive lock On one row, which is currently updatedSELECT from Table1WHERE=4< /c10>

Table Scan Default means that you must scan the entire table, so you must have a shared lockon each record-even on records that you do not logically request. If you access 2 tables in a different order in different sessions, this situation can lead to a deadlock situation when you try to read and write from the same table. The following code shows a transaction from the 1th query:

1 BEGIN TRANSACTION2 3 --acquires an Exclusive Lock on the row4 UPDATETable1SETColumn1= 3 WHEREColumn2= 15 6 --Execute the query from Session 2 ...7 --This query acquires the Exclusive Lock on the one row from Table2 ...8 9 --This query now requests a Shared lock, but get ' s blocked, because the other session/transaction have an Exclusive lock On one row, which is currently updatedTen SELECTColumn1 fromTable2 One WHEREColumn2= 3 A  - ROLLBACK TRANSACTION - GO

The following shows the code from the 2nd transaction:

1 BEGIN TRANSACTION2 3 --acquires an Exclusive Lock on the row4 UPDATETable2SETColumn1= 5 WHEREColumn2= 25 6 --Continue with the query from Session 2 ...7 --This query now requests a Shared lock, but get ' s blocked, because the other session/transaction have an Exclusive lock On one row, which is currently updated8 9 --This query now requests a Shared lock, but get ' s blocked, because the other session/transaction have an Exclusive lock On one row, which is currently updatedTen SELECTColumn1 fromTable1 One WHEREColumn2= 4 A  - ROLLBACK TRANSACTION - GO

From 2 transactions you can see that 2 tables are accessed in a different order. If the timing is right, running these 2 transactions at the same time can lead to a deadlock (deadlock) scenario. Assume the following order of execution:

1. Run the UPDATE statement on the 1th transaction on Table1.
2. Run the UPDATE statement on the 2nd transaction on Table2.
3. The 1th transaction runs the SELECT statement on Table2. This SELECT statement is blocked because the table scan operator wants to obtain a shared lock on the line, which is locked by a 2nd transaction exclusively lock.
4. The 2nd transaction runs the SELECT statement on Table1. This SELECT statement is blocked because the table scan operator wants to obtain a shared lock on the line, which is locked by a 1th transaction exclusively lock.

Demonstrates this deadlock scenario:

Now 2 transactions are blocking each other, so you are causing a deadlock in SQL Server. In that case, the deadlock Monitor (Deadlock monitor) background process kicks in, making the most "inexpensive" transaction rollback (based on the number of bytes that the transaction needs to write to the transaction log).

You can easily resolve this deadlock in 2 tables by providing an index to the Column2. In that case, SQL Server can perform the lookup (seek) operator of a conforming column, so when you execute a SELECT statement, you can skip the locked row that is already in the index leaf layer:

1 CREATE nonclustered INDEX  on Table1 (COLUMN2) 2 CREATE nonclustered INDEX  on Table2 (COLUMN2) 3 GO

Demonstrates how the current deadlock situation is:

Using the find operation you can skip the locked lines of the index leaf layer and you can avoid deadlocks that we have discussed. So when you see a deadlock situation in your database, it's important to look closely at your indexing strategy (design)! In SQL Server, indexing has always been a very important thing-always remember this!

Thanks for your attention!

Deadlock caused by missing indexes in SQL Server