Two transactions lock rows A then B, and B then A, and deadlock. Best prevention?

Options

1. Increase the lock timeouts of the database to wait longer
2. Take locks in the same order everywhere and keep transactions short (Correct option)
3. Disable transactions in the application for these operations
4. Use SELECT * so that all the columns of the rows are locked

Answer

Acquire locks in a consistent order everywhere and keep transactions short

Why

Deadlock: transaction 1 holds A and waits for B, while transaction 2 holds B and waits for A. Both wait forever, so the database kills one of them.

If every piece of code always locks rows in the same order (e.g. lowest ID first), this circle cannot form.

Short transactions hold locks for less time. Also retry when the database reports a deadlock.

Remember

Same lock order everywhere → no deadlocks.

Key terms

• Deadlock
• Lock (mutex)
• Transaction
• Isolation level
• ACID
• Lock ordering