Why Do Deadlocks Happen and How Can You Prevent Them?

Deadlock occurs when two or more threads or transactions each hold resources the other needs, causing a standstill.

Example: Thread A locks resource A then waits for B; Thread B locks resource B then waits for A, leading to a deadlock.

The four necessary conditions for a deadlock are:

Mutual exclusion : a resource can be held by only one process at a time.

Hold and wait : a process holds one resource while requesting another.

No preemption : allocated resources cannot be forcibly taken away.

Circular wait : a closed chain of processes each waiting for the next.

Practical ways to avoid deadlocks:

Standardize lock acquisition order to break circular wait.

Set lock wait timeout, e.g., SET innodb_lock_wait_timeout = 5; Update data in small batches using LIMIT to reduce lock contention.

Keep transactions short and commit or rollback quickly.

// Java deadlock example
synchronized(resourceA) {
    Thread.sleep(100);
    synchronized(resourceB) {
        // ...
    }
}

synchronized(resourceB) {
    Thread.sleep(100);
    synchronized(resourceA) {
        // ...
    }
}

-- Transaction A
BEGIN;
LOCK TABLE table_A WRITE;
LOCK TABLE table_B WRITE;
COMMIT;

-- Transaction B (wrong order, may deadlock)
BEGIN;
LOCK TABLE table_B WRITE;
LOCK TABLE table_A WRITE;
COMMIT;

-- Corrected order: lock tables consistently
BEGIN;
LOCK TABLE table_A WRITE;
LOCK TABLE table_B WRITE;
COMMIT;

UPDATE orders SET status = 'shipped' WHERE status = 'pending' LIMIT 1000;