Deadlock in DBMS
In our daily life, we see a lot of scenarios where we say that a deadlock has occurred, which means that no escape is possible from the current situation. For instance, in a traffic jam, a scenario where cars are stuck at random places can give rise to a situation where no car can move from its place ever. In this blog, we’ll see what a deadlock is from the perspective of the databases.
Before Learning about Deadlocks, let’s see what transactions are in DBMS.
Transactions in Database Management Systems
A transaction comprises many database operations that are logically related to each other. A very common example of this is transferring money from one bank account to another. In ordinary language, we say we made a transaction when moving some money from one account to another; the same goes for databases.
Let’s assume that I have a database that contains account balances of bank consumers. Now, Akshat bought something online and wants to pay some amount to the Seller (both Akshat and the Seller are customers of the same bank).
A simple Transaction between them will follow these steps:
- Read account balance of Akshat.
- Deduct X amount from Akshat’s balance.
- Save/Write Akshat’s balance with the new amount.
- Read account balance of Seller.
- Increment X amount to the Seller’s balance.
- Write Seller’s balance with the new amount.
The DBMS syntax for the above transaction will look like this:
For the sake of synchronicity, we use locks in DBMS. Locks are associated with specific data items in the database. So if some transaction wants to make changes to that data item, it has to acquire the respective lock while making the changes. No other transaction can acquire that lock while some transaction is making changes to that data item. Locks help in maintaining the consistency of the database.
What are Deadlocks in Database Management Systems?
Here, a deadlock scenario may arise where two concurrent transactions are working on a database, and they both are holding two different locks, and both are waiting for each other to release the other locks.
Image source: https://itnext.io/
Let us consider we have some organization’s database. In the database, we have one employee table and one salary table.
Suppose two transactions T1 and T2, are working concurrently, the transaction T1 holds the lock of some rows in the employee table and the transaction T2 holds the lock of some rows in the salary table.
Now, consider a situation where T1 wants to make some changes in the salary table, and at the same time, T2 also wants to make some changes in the employee table.
Both the transactions are waiting for the other to release locks; they will have to wait for an infinite amount of time to execute completely. The infinite wait means we have a Deadlock situation here.
So how do we deal with the Deadlock situation?
Prevention is better than cure, right. So let’s see how we prevent deadlocks.
There are two methods for Deadlock Prevention:
- Wait-Die Method
- Wound-Wait Method
Wait-Die is a non-preemptive type of deadlock prevention method. Since it is non-preemptive, CPU time will be distributed unevenly among all transactions. One transaction cannot be preempted between executing (it executes until it’s complete Burst Time).
In the Wait-Die Method, a transaction is made to wait if it has arrived before the other transaction or is killed.
Let’s consider two transactions Ti and Tj. Consider a situation where Ti requests the data member lock which Tj holds. Then Ti can wait only if the timestamp of Ti is lesser than that of the Tj.
Wound-Wait is a preemptive type deadlock prevention method. It ensures that all transactions get equal CPU time. The transactions are not executed to entire burst-time; they can be preempted during their execution to get CPU time for other transactions.
The wound-wait schema is the opposite of the wait-die schema; here, the transaction waits if it has arrived after the other transaction, or the other transaction is rolled back.
Again, let’s consider two transactions Ti and Tj, where Ti requests a data-lock held by Tj.
Note: In Wait-Die Method, Ti is getting rolled back, whereas, in Wound-Wait, Tj is rolled back.
Frequently Asked Questions
1). What are Transactions in DBMS?
In Database Management Systems, a transaction comprises many database operations that are logically related.
2). What is the reason for using Locks in Database Management Systems?
We use Locks to synchronize the functioning of the transactions; locks are very useful in maintaining the consistency of the database.
3). What are the methods used to prevent Deadlock?
The two methods that we use to prevent deadlock situations in DBMS are:
- Wait-Die Method
- Wound-Wait Method
Cheers if you reached here!! You learned something new today.
Don't stop here, Ninja; check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job.
If you are looking for a new skill to upgrade your knowledge, Machine Learning is the future now. The blogs in the Coding Ninjas ML section are curated and crafted with precision. You can find all blogs related to the topics of ML or DL here.