What is a deadlock, and how do I handle it?

Definition:

A deadlock is a cycle of waiting transactions: each holds a lock another needs to continue. The database detects the cycle and cancels a participant so the others can proceed. Applications must treat that cancellation as a failed transaction and decide whether to retry it.

Key points:

• Two transactions hold locks that the other needs.

• The database aborts one transaction to break the cycle.

• Lock rows in a consistent order, keep transactions short and retry the aborted transaction safely.

• Do not retry only the last statement of an aborted transaction; begin a fresh transaction for a safe retry.

Interview point:

• Reduce deadlocks through consistent lock order and short transactions, but still handle them as a possible production outcome.