If you’ve spent any time working with SQL Server concurrency, you’ve probably run into deadlocks as a frustrating, hard-to-replicate production issue. Most guides focus on fixing them, but if you want to test your monitoring tools, validate error handling, or train your team on troubleshooting, you might need to know how to create a deadlock in SQL Server on purpose. This process is totally safe if you follow basic guardrails, and it will give you a much clearer understanding of how these conflicts play out in real workloads. I’ve used this exact method dozens of times to test deadlock alerting for client systems, so I’ll walk you through every step, plus what to avoid so you don’t break anything.
Prerequisites Before You Attempt to Create a Deadlock in SQL Server
Before you run any queries for this test, you need to set up your environment properly to avoid accidental disruptions. The last thing you want is to block your entire dev team’s work because you forgot to isolate your test, or worse, run something on a production instance by mistake. I always go through this pre-check list before every deadlock test, even if I’m in a hurry:
- A dedicated test SQL Server instance (never run this on production, even if you think it’s low traffic)
- Two separate active query sessions connected to the same database, like two open SSMS query windows
- Basic write permissions for the test database you’re working in, to create tables and run update queries
- Deadlock tracking enabled, either via Extended Events or the default system health session, so you can capture the deadlock graph after you create it
Core Deadlock Conditions Required for Replication
Most people have heard of the four standard deadlock conditions, but you don’t need to memorize them to replicate the issue. I’ll break down what each means in the context of SQL Server specifically, so you know exactly what you’re setting up. First, mutual exclusion: each resource (in this case, a table row or page) is locked by only one session at a time. Second, hold and wait: a session holds one lock and waits for another held by a different session. Third, no preemption: SQL Server can’t just take a lock away from a session to resolve the conflict. Fourth, circular wait: each session is waiting for a resource the other holds, creating a loop. All four conditions have to be true at the same time for a deadlock to occur, which is why they’re so hard to replicate randomly in production. So when you’re creating one intentionally, you just need to set up a scenario that hits all four, step by step, with controlled timing between the two sessions. I like to write each step out for both sessions before running anything, so I don’t mess up the order and waste time.
Step-by-Step Process to Create a Deadlock in SQL Server
First, in either of your two sessions, run the code to create two simple test tables and populate them with one row each. You can name them whatever you want, just make sure the names are unique so you don’t overwrite existing tables. Add one row to each table, so you have a single record to update for the test. First, in Session 1, run a BEGIN TRANSACTION statement, then update the first test table, setting a dummy value for the single row you added. Do not commit the transaction after this first update – that’s the most common mistake people make when trying to replicate this, because the lock will be released immediately if you commit. Session 1 now holds an exclusive lock on the row in the first table. Next, switch to Session 2, run a BEGIN TRANSACTION statement, then update the second test table the same way, and again, do not commit the transaction. Session 2 now holds an exclusive lock on the row in the second table. Go back to Session 1, and run an update query targeting the row in the second test table. This query will hang immediately, because Session 2 already holds the exclusive lock on that row. Session 1 is now waiting for Session 2 to release its lock, while still holding its own lock on the first table. Switch back to Session 2, and run an update query targeting the row in the first test table. Now you’ve created the circular wait condition: Session 1 is waiting for the second table, Session 2 is waiting for the first table. Within 5 seconds, which is SQL Server’s default deadlock detection interval, one of the two sessions will be chosen as the deadlock victim, and you’ll see a standard deadlock error message. The session that is terminated as the victim is chosen based on which transaction has the lowest rollback cost, so it’s not always the last one to run the query. You can adjust the DEADLOCK_PRIORITY setting for a session if you want to control which one is picked, which is useful if you’re testing error handling for a specific application workflow.
What to Do After You Create the Deadlock
Now that you’ve successfully created a deadlock, you don’t just stop there. The whole point of running this test is to learn, so there are a few key actions you should take next. First, make sure you roll back any open transactions in both sessions, so you don’t leave locks hanging around that block other users on the dev instance. If you forget to do this, you might get a bunch of complaints from other developers whose queries are timing out, which I’ve definitely learned the hard way. Next, pull the deadlock graph from the system health session or your Extended Events trace. The deadlock graph will show you exactly which sessions were involved, what resources they were holding, and which one was chosen as the victim. This is the same data you’ll use to troubleshoot production deadlocks, so practicing reading it with a test case you created yourself will make you much faster at real issues. You can also use this test to validate that your deadlock alerting tools are working correctly. If you have monitoring set up to send an alert every time a deadlock occurs, running this test will confirm that the alert triggers, and that it includes all the data you need to troubleshoot. I always run this test after setting up new monitoring for a client, to make sure we’re not missing deadlock events in production. You can also experiment with different isolation levels to see how they impact deadlock frequency. For example, running the same test with READ COMMITTED SNAPSHOT ISOLATION enabled will often prevent the deadlock entirely, because readers don’t take shared locks in that isolation level. That’s a common fix for production deadlocks, so testing it yourself will help you understand when it’s a good solution to implement.
Learning how to create a deadlock in SQL Server is one of the most useful exercises for anyone working with SQL Server concurrency, even if you spend most of your time trying to prevent deadlocks instead of creating them. It gives you first-hand experience with how these conflicts form, what the error messages look like, and what data you need to troubleshoot them when they happen in production. Just remember to always run these tests in an isolated, non-production environment, and clean up your open transactions after you’re done, so you don’t cause unnecessary issues for other team members. Once you’ve run this test a few times, you’ll be much more confident the next time you get a deadlock alert from your production system.