Deadlock condition in DBMS

The deadlock is a condition where two or more transactions are waiting indefinitely for one another to give up locks. The Deadlock is said to be one of the most feared complications in DBMS as no task ever gets finished and is in waiting for state forever.

Example:-

Into the Employee table, transaction Tab1 holds a lock on some rows and needs to update some rows in the grade table. Simultaneously, transaction Tab2 holds locks on some rows in the grade table and needs to update the rows in the Employee table held by Transaction Tab1. And now, the main problem arises. This means the Transaction Tab1 is waiting for Tab2 to release its lock and similarly, transaction Tab2 is waiting for Tab1 to release its lock. Each activity comes to a halt state and remains at a standstill. That will remain in a standstill until the DBMS detects the deadlock and aborts one of the transactions.

Deadlock Detection

The resource scheduler can detect a deadlock as it keeps track of all the resources that are allocated to different processes. After a deadlock is detected, it can be resolved using the following methods:

1. All the processes involved in the deadlock are terminated. This is not a good approach as all the progress made by the processes is destroyed.
2. Resources can be preempted from some processes and given to others until the deadlock is resolved.

Deadlock Avoidance

This is better to avoid a deadlock rather than take measures after the deadlock has occurred. Here the wait for the graph can be used for deadlock avoidance. That is however only useful for smaller databases as it can get quite complex in larger databases.

1. When a database is stuck in a deadlock state, then it is better to avoid the database rather than aborting or restarting the database. That is a waste of time and resources.
2. The deadlock avoidance mechanism is used to detect any deadlock situation in advance. That method like "wait for graph" is used for detecting the deadlock situation but this method is suitable only for the smaller database.
   For the larger database, the deadlock prevention method can be used.