Deadlocks
Although locking resolves the major concurrency problems illustrated earlier, it can give rise to a problem of its own. Imagine there are two concurrent transactions, T1 and T2, and that they both need to update the same rows in tables A and B. However, T1 updates table A first, and T2 updates table B first. This means that T1 could obtain a lock on table A and T2 obtains a lock on table B, but neither transaction can obtain the second lock because it has to wait for the other. This situation is known as a deadlock, and is illustrated in the table below.
| Step | Transaction | Operation | DBMS reply | Table A | Table B |
|---|---|---|---|---|---|
| 1 | - | - | - | Unlocked | Unlocked |
| 2 | T1 | Lock A | OK | Locked T1 | Unlocked |
| 3 | T2 | Lock B | OK | Locked T1 | Locked T2 |
| 4 | T1 | Lock B | Wait | Locked T1 | Locked T2 |
| 5 | T2 | Lock A | Wait | Locked T1 | Locked T2 |
| 6 | T1 | Lock B | Wait | Locked T1 | Locked T2 |
| 7 | T2 | Lock A | Wait | Locked T1 | Locked T2 |
From step 4 onwards, neither transaction can proceed because it is waiting for the other to release its locks. This is a deadlock.
There are several ways that the DBMS can resolve the deadlock problem, either by ensuring that transactions obtain all their locks at once, or by restarting one of the conflicting transactions. Usually the transaction that started first will be allowed to proceed while the later transaction will be forced to restart. This is done transparently to the database user.
Deadlocks
Blocking Locks and Deadlocks