Careeroza

Deadlocks

How a Deadlock Happens

Transaction A locks product 1 and wants product 2. Transaction B locks product 2 and wants product 1. Both wait forever, so the database kills one of them with a deadlock error.

Tx A (cart: product 1, product 2)      Tx B (cart: product 2, product 1)
   lock product 1  ✔                       lock product 2  ✔
   lock product 2  … waits for B           lock product 1  … waits for A
                     └──────── deadlock ───────┘
 MySQL: "Deadlock found when trying to get lock; try restarting transaction"

Prevent It

  1. Lock in a fixed order. Sort the ids before locking, so every transaction locks product 1 before product 2.
  2. Lock everything you need in one query (where p.id in :ids order by p.id).
  3. Keep locking transactions short: no HTTP calls or emails while holding locks.
  4. Retry the transaction a few times on CannotAcquireLockException / deadlock errors.
@Lock(LockModeType.PESSIMISTIC_WRITE)
@Query("select p from Product p where p.id in :ids order by p.id")   // fixed order
List<Product> lockAll(@Param("ids") Collection<Long> ids);

Exercise

Exercise: A checkout locks every product in the cart. Carts are processed in the order the user added items. Why can this deadlock, and what is the one-line fix?

Show answer
Two users can hold the same products in a different order (A: [5, 9], B: [9, 5]).
A locks 5 and waits for 9; B locks 9 and waits for 5 -> deadlock.
Fix: sort the product ids before locking (or lock them in one query "order by p.id"),
so every transaction acquires locks in the same order.