CSE Roadmap

Specialist tier · about 2 hours · 5 parts

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.