Deadlock concept and prevention in SQL

Introduction

Deadlocks happen when two or more tasks wait for each other to finish, causing a standstill. Understanding and preventing deadlocks helps keep databases running smoothly without freezing.

When multiple users try to update the same data at the same time.

When two transactions lock resources in opposite order.

When your application needs to handle many database operations concurrently.

When you want to improve database performance by avoiding waiting.

When designing transactions that access multiple tables.

Syntax

SQL

-- No single syntax for deadlock, but prevention techniques include:
-- 1. Access tables in the same order in all transactions
-- 2. Keep transactions short
-- 3. Use proper isolation levels
-- 4. Use explicit locking carefully

-- Example: Locking rows in a consistent order
BEGIN TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;
-- perform updates
COMMIT;

Deadlock is a situation, not a command, so prevention is done by how you write queries and transactions.

Using consistent order of accessing resources helps avoid deadlocks.

Examples

Locking rows in the same order prevents deadlocks.

SQL

-- Example 1: Consistent locking order
BEGIN TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;
-- update balances
COMMIT;

Short transactions reduce chances of deadlocks.

SQL

-- Example 2: Avoid long transactions
BEGIN TRANSACTION;
-- quick update
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
COMMIT;

Lower isolation levels reduce locking and deadlocks.

SQL

-- Example 3: Use lower isolation level if possible
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Sample Program

This transaction locks account 1 first, then account 2, avoiding deadlock with other transactions that follow the same order.

SQL

-- Simulate deadlock prevention by locking in consistent order
BEGIN TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;

Important Notes

Deadlocks are detected by the database engine and usually cause one transaction to roll back.

Always test your transactions under concurrent load to check for deadlocks.

Use database logs or monitoring tools to find deadlocks in real systems.

Summary

Deadlocks happen when tasks wait on each other and stop progress.

Prevent deadlocks by accessing resources in the same order and keeping transactions short.

Use proper isolation levels and locking strategies to reduce deadlocks.