How to simulate a SQL deadlock for testing purposes

24 Oct 2023

Deadlocks, or concurrency conflicts, can occur in a multi-user database system when multiple transactions compete for the same resources. It’s important to test how your application handles such situations. In this blog post, we’ll discuss how to simulate a SQL deadlock for testing purposes.

Prerequisites

To simulate a SQL deadlock, you’ll need access to a database management system (DBMS) that supports transactions, such as MySQL or PostgreSQL. You’ll also need a basic understanding of SQL and database transactions.

Simulating a SQL deadlock

Here’s a step-by-step guide on how to simulate a SQL deadlock:

Set up a testing environment: Create a database with a few tables and populate them with some sample data. This will serve as the basis for simulating the deadlock.
Open two or more database connections: Make sure you have multiple connections to the database. This can be achieved by using separate database clients or opening multiple tabs/windows in your DBMS’s interface.
Start two conflicting transactions: In each connection, start a transaction and perform some operations that conflict with each other. For example, if you have a table accounts with a balance column, you can update the balances of two accounts simultaneously.

-- Connection 1
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- Connection 2
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 2;

-- Both connections are now holding locks on the respective rows they are updating

Force a delay: To increase the chances of a deadlock occurring, introduce a delay in one of the transactions. This delay will hold the lock for a longer time and allow the other transaction to proceed further.

-- Connection 1
-- Introduce a delay of 2 seconds
SELECT SLEEP(2);

Create a deadlock: In the other transaction, try to update the same rows that are already locked by the first transaction.

-- Connection 2
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

Observe the deadlock: If everything is set up correctly, the second transaction will be blocked and a deadlock will occur. The DBMS will detect the deadlock and choose a victim transaction to terminate.
Handle the deadlock: In your application, you should handle deadlocks by implementing retry logic or using DBMS-specific mechanisms like deadlock detection and resolution.

Remember, this is a simplified example of simulating a SQL deadlock. In real-world scenarios, deadlocks can be more complex and involve multiple transactions and resources. The steps outlined here should help you understand the basics and get started with testing your application’s deadlock handling capabilities.

Conclusion

Simulating SQL deadlocks for testing purposes is an essential part of ensuring the robustness and reliability of your application. By following the steps outlined in this blog post, you can create a controlled environment to test how your application handles concurrent transactions and resolves deadlocks.

#References