Simulating Deadlocks And Blocks In SQL Server

As a DBA or SQL Server programmer, In several situations to test the stability of the database and the server or even testing a transact-SQL query or a stored procedure, you may need to simulate a deadlock situation.

Here is a simple way to simulate the deadlock or block situation.

Simulating Deadlocks And Blocks

1. Launch the SQL Server Management Studio (SSMS).
2. Open a query window. Let’s call it Window-1.
3. Begin a transaction using BEGIN TRAN.
4. Below the begin transaction, wright an update query against a record in a table, say PurchaseOrders.
5. Execute the statement along with the begin transaction. Make sure, the transaction is not committed or roll-backed.
6. Now, open another query window. Let’s call it Window-2.
7. Copy the contents of Window-1 to Window-2.
8. Execute the content of Window-2. Notice the process in Window-2 is blocked.
9. To remove the deadlock, either commit or rollback the transaction in Window-1.

Reference

• Collecting deadlocks and blocked processes using XEvents.
• About deadlocking in MS TechNet.