Single-Row Deadlock

Introduction

Demonstration of the smallest possible deadlock: only one statement, one table, one row.

Without transactions (no BEGIN TRANSACTION). Even RCSI (Read Committed Snapshot Isolation) is turned ON to eliminate shared locks. Everything is “by the book” as Books Online suggest to minimize deadlocks. How is that possible to deadlock? There is a complexity in simplicity of this demo. In video it is explained why single-row deadlock occurs. If you understand single-row deadlock, you will be able to understand even most complex production deadlock situations. And understanding the cause is the first step to eliminate them.

Enjoy the video, and tell me your opinion. Thank you!

What statement deadlocks?

Believe it or not, these two statements will deadlock:

Those will deadlock, even though:

  • it is only ONE statement per session (not usual example with two SQL commands within BEGIN TRAN .. COMMIT block)
  • they touch only ONE table
  • they touch only ONE row (and both sessions touch the same row)
  • we do not have (explicit) transactions, just “normal” commands (BEGIN TRAN/COMMIT is not there at all!)

There are three deadlock examples in the demo, this is only the first one. In case you wonder, demo is recorded on SQL Server 2017, but the same is on any version so far, and probably with any other RDBMS that uses locks. Same effect could be shown using eg. Oracle. The beauty is to understand it and armed with that knowledge, eliminate the deadlocks in production or at least greatly reduce them.

Why “WHILE” Loop?

In the demo, you will notice that I add this at start of each session’s command:

Why? Because deadlock is a racing condition. It does not happen every time.

Deadlock will happen only if we hit the “right” moment to execute other session in relation to first session. To achieve that, I run the command in a loop waiting for that special moment to hit after certain number of loop iterations. Because on each iteration session’s relation to each other will move a tiny bit, until the “right” moment is hit and deadlock occurs. The same reason is why in your production environment you see deadlocks only occasionally, and often you cannot reproduce it, at least not easily.

The explanation

There is no “row” lock. In reality, what is locked is a 6-byte hash calculated from index’s KEY value of that row. For example, we have 2 indexes on that table: clustered which has “Id” column as index key, and non-clustered which has column “SomeValue” as index key. That hashed value of the index key can be seen using %%lockres%% function:

Therefore, for the same row (row with Id=1), we have 2 indexes and X-locking 2 different hash values. And two locks are locked in order, not at the same time. If one session first locks first hash then second hash, and the other session first locks second hash then first hash, it is possible to create a deadlock “circle” between them if we hit the right moment. That depends on execution plan, with which index will it access the table first.

Hash collision

By the way, hash values can “collide”, that is, for different input value give same output value. That happens with all hash functions, even with hashing function that is used by locking algorithm. More rows locked, higher probability of collision. For example, if you have a large transaction that insert rows in the table, and some other session does the same, it is likely they will occasionally block even they never insert the same row values. Eg each insert command inserts 1 000 000 000 rows and normally takes 4h. Occasionally, insert takes 8h, because session is blocked by other session and waits 4h until first one is finished, although they are inserting different values for sure (enforced by app logic). Execution times are 4h, 4h, 8h, 4h, 4h, 8h, etc. But that is a topic for a new blog post.

The Setup

Creating a database and a table for the demo:

3 Deadlock Examples

You can run examples from the video yourself:

Summary

It is possible deadlock will occur between two ordinary DML commands, without BEGIN TRAN/COMMIT block. Some showed that it can occur between two rows, but here is demonstrated It can occur even when they lock the same, single row, as long as table has more than one index. Even on a table with only one index, deadlock can occur if it has more than 1 row, because we again have at least 2 hashes to lock and now all depends on order we lock them. Since almost every databases has more than 1 row in total, deadlock can occur in any database.

The challenge:

Produce a deadlock in a database without any rows! 🙂

Tagged with: ,

Leave a Reply

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

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.