How do you handle a deadlock in MySQL, and how do you prevent them?

A deadlock is two transactions each holding a lock the other needs. InnoDB detects the cycle automatically, picks the transaction that has done less work, and rolls it back with error 1213. Nothing hangs forever.

How to diagnose: run SHOW ENGINE INNODB STATUS and read the LATEST DETECTED DEADLOCK section. It shows both transactions, the exact statements, and which locks each was holding and waiting for.

How to prevent:

• Take locks in a consistent order. Most deadlocks come from one code path updating table A then B while another does B then A. Agreeing an order across the codebase removes the cycle entirely.
• Keep transactions short. Never hold a transaction open across a network call or user input.
• Index the columns you filter on. Without an index, InnoDB locks far more rows than you intended, which widens the window enormously.
• Consider READ COMMITTED for write-heavy workloads, since it takes fewer gap locks.

Note: The most important point to make is that your application must retry a deadlocked transaction. Deadlocks are normal at concurrency and are not by themselves a bug.