Monday, April 14, 2014

What is deadlock in database?

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.


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.
Deadlock example in DBMS
1 (a)
deadlock example in dbms
1 (b)
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.

deadlock situation example
Figure 2 - Deadlock situation example

What is the solution to handle deadlocks if occured?

The only solution is to pick up one of the Transactions and roll back the same.

definition of deadlock in rdbms
deadlock during database transactions explained 
real time example of deadlock detection
solution to handle deadlock

Featured Content

Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

All time most popular contents

data recovery