If you’ve ever spent hours troubleshooting random transaction failures in SQL Server, you know how frustrating deadlocks can be for both developers and database administrators. But what if you need to replicate this scenario intentionally? Understanding how to create deadlock in SQL Server is a critical skill for testing error handling flows, validating deadlock detection tools, and training your team to resolve these issues fast when they hit production. I’ve used intentional deadlock creation dozens of times to stress test new application features, and it’s saved me from multiple post-launch outages over the years. You don’t need advanced SQL skills to pull this off, just access to a test environment and a basic understanding of how transaction locking works.
Core Conditions Required for a SQL Server Deadlock
Before you jump into writing test code, you need to understand the four non-negotiable conditions that must be present for a deadlock to occur. These are standard for all database systems, not just SQL Server, and missing even one will mean your test won’t work as expected. These four conditions are non-negotiable if you want to successfully create deadlock in SQL Server, so you need to make sure all are present in your test setup.
The first condition is Mutual exclusion, which means only one transaction can access a given resource (like a table row) at a time. SQL Server enforces this by default with row, page, and table locks to prevent data corruption. The second is Hold and wait, where a transaction holds at least one locked resource while requesting another resource that’s already locked by a different transaction.
The third condition is No preemption, meaning SQL Server will never force an active transaction to release the locks it’s holding before it completes or rolls back. The final condition is Circular wait, where two or more transactions form a closed loop, each waiting for a resource that the next transaction in the loop is holding. If all four of these are in place, a deadlock is guaranteed to trigger.
One quick note before you move forward: never run any of these tests on a production SQL Server instance. Even small intentional deadlocks can escalate to block live user transactions and cause outages if you’re not careful. Stick to a dedicated development or staging environment that only your team has access to.
How to Create Deadlock in SQL Server: A Working Code Example
This test uses two simple tables and two parallel transactions to trigger a deadlock consistently. First, create two test tables in your dev instance to avoid touching any existing production or test data. You can run this code in a single SSMS query window to set up your test environment: create table dbo.TestCustomer (CustomerID int primary key, Balance decimal(10,2)); insert into dbo.TestCustomer values (1, 100.00); create table dbo.TestOrder (OrderID int primary key, Total decimal(10,2)); insert into dbo.TestOrder values (123, 50.00);
Next, open two separate query windows in SSMS, both connected to the same test database. You’ll run one transaction in each window, timing them to run within a few seconds of each other. In the first query window, paste this code: begin transaction; update dbo.TestCustomer set Balance = Balance - 10 where CustomerID = 1; waitfor delay '00:00:05'; update dbo.TestOrder set Total = Total + 10 where OrderID = 123; commit transaction;
In the second query window, paste this code: begin transaction; update dbo.TestOrder set Total = Total - 10 where OrderID = 123; waitfor delay '00:00:05'; update dbo.TestCustomer set Balance = Balance + 10 where CustomerID = 1; commit transaction;
Run the code in the first window, then immediately run the code in the second window. The 5-second delay gives both transactions time to lock their first resource before requesting the second one that the other transaction is holding. Always run these tests on a dedicated development or staging instance to avoid disrupting live user traffic. After about 5 seconds, one of the two windows will show a 1205 error message, saying it was chosen as the deadlock victim and rolled back. Once you create deadlock in SQL Server, the system automatically picks the transaction with the lower rollback cost to terminate, so the other transaction will complete successfully.
How to Verify Your Deadlock Was Triggered Correctly
The 1205 error message is a clear sign your test worked, but you’ll likely want more details about the deadlock for testing or training purposes. SQL Server tracks all deadlocks by default in the system_health extended events session, so you don’t need to set up any special logging ahead of time to capture this data. You can use any of the following methods to pull full details about your test deadlock:
- Check the Messages tab in SSMS for the deadlock victim error message (error code 1205), which will include basic details about which transaction was terminated
- Query the system_health session XEL files to pull the full deadlock graph with transaction details, locked resources, and victim selection logic
- Use SQL Server Profiler (for older SQL Server versions) to capture deadlock events in real time during your test for easier analysis
The deadlock graph is the most useful output, as it shows you exactly which resources each transaction was holding and requesting, and why the system chose the specific victim. You can save this graph as an xdl file to share with your team for training, or use it to validate that your deadlock monitoring tool is capturing events correctly. Save the deadlock graph output for use in training or debugging your application’s error handling logic.
If your test didn’t trigger a deadlock, double check that you ran both transactions within the 5-second delay window, and that you’re using separate query windows for each transaction. You can adjust the waitfor delay value to 10 seconds if your test server is slow, to give both transactions enough time to lock their first resource before requesting the second.
Key Risks and Best Practices for Intentional Deadlock Testing
Intentional deadlock testing is low risk if you follow basic guardrails, but there are a few common mistakes that can cause headaches for you and your team. The most important rule is to never run these tests on production, even if you think you’re using small, isolated tables. It’s too easy for lock escalation to spread to other tables and block live user requests, leading to unexpected outages.
Another common mistake is leaving uncommitted transactions open after your test. If you cancel a test mid-run, make sure to run rollback transaction in any open query windows to release all held locks. Always clean up test tables and open transactions immediately after you finish your test to avoid impacting other team members’ work. If you leave locks open, other queries running on the test server will hang indefinitely until the locks are released.
When you create deadlock in SQL Server for testing, you’ll often notice that the victim selection varies depending on how much work each transaction has completed. You can use this to test how your application handles different failure scenarios, like if a high-priority transaction is chosen as the victim. Adjust the wait time in your test code based on your server’s response speed to make sure the deadlock triggers consistently every time you run the test.
I once worked on a team that skipped deadlock testing for a new e-commerce checkout flow, and we had 3 hours of downtime during a Black Friday sale because orders kept failing with deadlocks and the app didn’t have retry logic built in. Running 15 minutes of intentional deadlock tests during staging would have caught this gap before launch, saving us thousands of dollars in lost revenue. It’s a small investment that pays off huge when you launch high-traffic features.
Intentional deadlock testing doesn’t have to be complicated, and it’s one of the most effective ways to harden your SQL Server application against unexpected concurrency failures. Whether you’re validating a new monitoring tool, testing application retry logic, or training junior DBAs, taking the time to learn how to create deadlock in SQL Server will give you hands-on experience that no textbook can match. Just remember to stick to your test environment, follow the best practices we covered, and you’ll be able to replicate and resolve deadlocks confidently whenever they come up.