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
- Launch the SQL Server Management Studio (SSMS).
- Open a query window. Let’s call it Window-1.
- Begin a transaction using BEGIN TRAN.
- Below the begin transaction, wright an update query against a record in a table, say PurchaseOrders.
- Execute the statement along with the begin transaction. Make sure, the transaction is not committed or roll-backed.
- Now, open another query window. Let’s call it Window-2.
- Copy the contents of Window-1 to Window-2.
- Execute the content of Window-2. Notice the process in Window-2 is blocked.
- To remove the deadlock, either commit or rollback the transaction in Window-1.
- Collecting deadlocks and blocked processes using XEvents.
- About deadlocking in MS TechNet.