Example Deadlock Code

Deadlocks happen. As you’re about to see in this post, deadlocks are actually quite easy to create. In my previous post I showed how to monitor and analyze deadlock events using Extended Events. In this post I present an easy way to trigger a an example deadlock that can be used to test that monitor and analysis code.

Maddona, most definitely not involved in any deadlock example.. By Giovanni Carnovali, 18th Century

Deadlocks occur when two or more processes are attempting to lock the same resources in an incompatible manner. With that in mind, we’ll need two query windows open in SQL Server Management Studio (SSMS). We’ll need to execute commands in one window, then switch to the other window, in order to trigger the deadlock. First, we need to create a resource to lock, so execute this code in the first SSMS window:

This table needs to be a global temporary table, as opposed to a session-local temporary table. This allows both SSMS windows to affect the same table. We could build a permanent table instead, but this way requires no cleanup. Which is win/win.

Now in the first SSMS window, run this code:

Now, switch to the second SSMS window, and run this:

Switch back to the first SSMS window and run this:

Again, switch to the second SSMS window:

SQL Server should report a deadlock within a few seconds:

Msg 1205, Level 13, State 51, Line 9
Transaction (Process ID 63) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

That’s as simple as it gets.

See our post on deadlock detection and analysis for code that shows exactly what statements caused the deadlock. The example deadlock code above shows this output from our analysis code:

Example Deadlock Analysis Output

Microsoft has another example of how to create a simple deadlock here.