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.

Simulating Deadlocks And Blocks In SQL Server



Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.