Deadlock Prevention - how deadlocks occur and how to prevent them

A deadlock occurs when two or more transactions are perpetually waiting for each other to release a resource (lock) they need, resulting in a system standstill. Prevention focuses on designing systems to avoid the conditions necessary for deadlocks.

How Deadlocks Occur

Deadlocks arise when four conditions are simultaneously met (Coffman conditions):

Mutual Exclusion: Resources are held exclusively by one transaction.

Hold and Wait: A transaction holds at least one lock while waiting to acquire another.

No Preemption: Locks cannot be forcibly taken from a transaction.

Circular Wait: A set of transactions are waiting for each other in a cyclic chain (e.g., Tx1 waits for Tx2, Tx2 waits for Tx1).

Deadlock Prevention Strategies

Prevention aims to negate at least one of these conditions.

* Consistent Lock Ordering: The most effective strategy to break "Circular Wait." All transactions needing multiple resources (e.g., rows, tables) must acquire locks in the same predefined order.

    -- Transaction A
    UPDATE accounts SET balance = balance - 10 WHERE id = 1;
    UPDATE accounts SET balance = balance + 10 WHERE id = 2;

-- Transaction B (acquires locks in the same order)
    UPDATE accounts SET balance = balance - 5 WHERE id = 2;
    UPDATE accounts SET balance = balance + 5 WHERE id = 1;
    

If Transaction B updated id=2 then id=1, and Transaction A updated id=1 then id=2, a deadlock could occur.

* Timeouts: Implement a maximum waiting time for a lock. If a transaction waits too long, it's aborted, releasing its held locks and breaking "Hold and Wait" or "No Preemption."

* Keep Transactions Short & Minimize Lock Duration: Reduce the time a transaction holds locks, lessening the window for other transactions to request them. Avoid holding locks during external I/O or long computations.

* Request All Locks Upfront: Break "Hold and Wait" by requiring a transaction to acquire all necessary locks before starting execution. If any lock is unavailable, the transaction waits or aborts without holding any locks.

In the interview, also mention that modern data platforms like Delta Lake leverage optimistic concurrency control and transaction logs to minimize explicit locking and the associated deadlock risks, often preferring conflict detection and retry mechanisms over strict prevention.