Part 2 of 5 · 1 min read
How a deadlock forms
Two transactions each hold a lock the other needs, so neither can ever proceed.
Think of two drivers in narrow, opposite one-way alleys, each refusing to back up, each waiting for the other to move first, with neither road wide enough for both cars to pass.
In plain words: a deadlock forms when transaction A holds a lock on item X and is waiting to acquire a lock on item Y, while transaction B holds a lock on item Y and is waiting to acquire a lock on item X. Neither transaction can proceed: A is stuck waiting for a lock B holds, and B is stuck waiting for a lock A holds, and neither will release its own lock until it has finished, which it cannot do without the other's lock. Without intervention, both transactions would simply wait forever.
A worked example. Transaction A locks row X then requests row Y; transaction B, running concurrently, has already locked row Y and now requests row X; both transactions now wait on each other indefinitely.
The key lines. No new SQL; a deadlock is a runtime condition that can arise from the ordinary lock acquisition covered on the two-phase locking page, not something a single statement causes by itself.
Deadlock
A deadlock is a situation where two or more transactions each hold a lock the other needs and are waiting for the other to release it, so none of them can ever proceed without intervention.
Example: two drivers in narrow, opposite one-way alleys, each waiting for the other to move first with no room to pass, mirrors a deadlock.