What is Deadlock? / Deadlock - Explained with examples / How to handle deadlocks?
What is Deadlock?
In Database Management
System, Deadlock is part of discussion in Transaction Processing Component. Deadlock
is a situation where two or more transactions waiting for locks on some data
items which are locked by other transactions in an incompatible
mode.
Here, incompatible
mode would mean one of the following;
A read lock request for a data item which is locked in write mode
A write lock request for a data item which is locked in read mode
A write lock request for a data item which is locked in write mode.
Example:
Assume that
two transactions T1 and T2 are needed data items A and B to be locked. In the
lock acquiring process, let us suppose T1 locked A successfully and T2 locked B
successfully. For successfully completing the transactions, T1 needs B also to
be locked and T2 needs A. The problem is T1 cannot release lock on A and T2
cannot release lock on B. This situation is called deadlock. If you carefully
observe this you would understand that we have formed a cycle which leads to deadlock.
|
|
Figure 1 - (a) Deadlock occurrence with two transactions, (b) deadlock occurrence with three transactions
Real time example of Deadlock situation:
Let us assume
two bank transactions, namely T1 and T2 as follows;
T1 – Transaction which transfers money,
say Rs. 5000 from account A to account B. T1 needs to lock both A and B in
Write mode (Exclusive Lock). T1 is said to be completed if and only if it
successfully updates the old balance of A and B with a new balance and commits
the transaction.
T1 would
involve two update queries;
UPDATE
ACCOUNT SET balance = balance -5000 WHERE account = ‘A’;
UPDATE
ACCOUNT SET balance = balance +5000 WHERE account = ‘B’;
T2 – Transaction which updates all the
accounts with yearly interest, say 5%. T2 need to lock all the accounts in
Write mode (Exclusive lock). T2 is said to be completed if and only if it
successfully updates the old balances of all the accounts with the new balances
and commits the transaction.
T2 would
involve one update query;
UPDATE
ACCOUNT SET balance = balance + (balance*0.05);
Assume that the
transactions are executed as follows;
T1 has
started and acquired Write lock on account A. T1 can now debit the amount to be
transferred from account A and save the new value of A. (cannot commit the transaction
T1 at this stage)
T2 also has
started at the same time and acquired Write lock on B along with other
accounts. T2 can now update all the accounts with their interest amounts except
account A. (because account A is held by T1. Hence, T2 cannot commit as well at
this stage).
At this
stage, both T1 and T2 are waiting for each other which leads to deadlock. This is shown in Figure 2.
 |
| Figure 2 - Deadlock situation example |