What four conditions must all hold for a deadlock, and how do you prevent one in practice? Give a database example.

A deadlock needs all four Coffman conditions at the same time:

• Mutual exclusion: only one party can hold a resource.
• Hold and wait: a party holds one resource while it waits for another.
• No preemption: nobody can take a resource away by force.
• Circular wait: there is a cycle of parties, and each one waits on the next.

Break any one condition, and deadlock can't happen.

Database example: transaction A updates row 1, then row 2. At the same time, transaction B updates row 2, then row 1. Each one holds one row lock and waits for the other. PostgreSQL finds the cycle and stops (aborts) one transaction with a deadlock detected error.

In practice, you break circular wait by always taking locks in the same order. For example, sort the IDs before you update the rows. Also:

• Keep transactions short.
• Take the locks you need at the start.
• Use lock timeouts or try-lock, so a waiting party gives up.
• Retry transactions that were aborted.