Deadlock concept and prevention in SQL - Time & Space Complexity

Time Complexity: Deadlock concept and prevention

O(n)

Understanding Time Complexity

Deadlocks happen when two or more database operations wait for each other to finish, causing a standstill.

We want to understand how the waiting time grows as more transactions compete for resources.

Scenario Under Consideration

Analyze the time complexity of this simple deadlock-prone transaction sequence.

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 200 WHERE id = 2;
UPDATE accounts SET balance = balance + 200 WHERE id = 1;
COMMIT;

This code shows two transactions updating the same rows but in reverse order, which can cause a deadlock.

Identify Repeating Operations

Look for operations that wait or retry due to deadlock.

• Primary operation: Lock acquisition on rows during UPDATE statements.
• How many times: Each transaction tries to lock two rows; if deadlock occurs, one retries.

How Execution Grows With Input

As more transactions try to update overlapping rows in conflicting order, waiting and retries increase.

Input Size (n transactions)	Approx. Operations (lock attempts + retries)
2	Few retries if deadlock occurs
10	More retries as conflicts rise
100	Many retries, longer waits, possible cascading delays

Pattern observation: Waiting and retries grow quickly as more transactions compete for the same resources.

Final Time Complexity

Time Complexity: O(n)

This means the waiting and retry time grows roughly in proportion to the number of competing transactions.

Common Mistake

[X] Wrong: "Deadlocks only happen rarely and don't affect performance much."

[OK] Correct: Even a few deadlocks cause retries and waiting, which add up as more transactions run concurrently.

Interview Connect

Understanding deadlocks helps you design safer database operations and shows you can think about how systems behave under load.

Self-Check

"What if transactions always locked rows in the same order? How would the time complexity change?"