Deadlock in DBMS

Deadlock in DBMS

In database management systems (DBMS), a deadlock is a situation where two or more transactions are waiting indefinitely for resources held by each other. Deadlocks are considered one of the most critical issues in DBMS, as they halt the progress of transactions and can severely impact system performance.

Understanding Deadlock with an Example

Consider two transactions, T1 and T2, operating on the Student and Grade tables:

• Transaction T1 wishes to update rows in the Grade table and has a lock on some rows in the Student table.
• Simultaneously, Transaction T2 holds a lock on rows in the Grade table and wants to update rows in the Student table that are currently locked by T1.

In this case:

• T1 waits for T2 to release the lock on Grade.
• T2 waits for Student’s lock to be released by T1.

Both transactions wait indefinitely, resulting in a deadlock. The DBMS must detect this deadlock and abort one of the transactions to resolve the issue.

Machine Learning Tutorial:–Click Here
Data Science Tutorial:-Click Here
Complete Advance AI topics:- CLICK HERE
Deep Learning Tutorial:- Click Here

Conditions for Deadlock

Only when all four of the following circumstances are met at the same time can a stalemate occur:

• Mutual Exclusion: Only one transaction at a time has the ability to lock a resource.
• A transaction that uses hold and wait (partial allocation) keeps certain resources while it waits for others.
• No Preemption: Resources must be released voluntarily from a transaction; they cannot be taken by force.
• Circular Wait: There is a closed chain of transactions in which each one waits for a resource that is held by the one behind it.

Deadlocks can be prevented by avoiding at least one of these circumstances.

Deadlock Handling in DBMS

Deadlocks can be managed through three main approaches: Avoidance, Detection, and Prevention.

Deadlock Avoidance

The goal of deadlock avoidance is to foresee possible deadlocks before they happen.

• For small databases, methods like Wait-for Graphs can be used.
• In larger systems, more sophisticated techniques such as resource allocation strategies are required.

Wait-for Graph

• A directed graph with nodes for transactions and edges for the waiting relationship is called a wait-for graph.
• A stalemate is indicated if a cycle is found in the graph.
• The DBMS continuously monitors this graph to detect deadlocks.

Deadlock Prevention

Deadlock prevention ensures that deadlocks never occur by carefully controlling the allocation of resources.

• The DBMS evaluates each transaction to determine if its operations could lead to a deadlock.
• Transactions are assigned a timestamp when they start, which helps decide whether a transaction should wait or be aborted.

Timestamp-Based Schemes

1. Wait-Die Scheme (Non-preemptive)
   • Older transactions can wait for younger ones.
   • Younger transactions requesting a resource held by an older transaction are aborted and restarted.
2. Wound-Wait Scheme (Preemptive)
   • Older transactions can preempt (abort) younger ones holding needed resources.
   • Younger transactions wait if a resource is held by an older transaction.

Comparison of Schemes:

Deadlock Detection and Recovery

Even with prevention techniques, deadlocks can still occur. Detection involves:

• Periodically checking the state of transactions.
• Using the Wait-for Graph to identify cycles indicating deadlocks.

Recovery from Deadlock

Once a deadlock is detected, the system must recover by:

1. Selecting a Victim: Choose transactions that can be rolled back with minimal loss, often those that have just started or have made fewer updates.
2. Rollback: Abort the victim transaction to free resources. Rollback can be total or partial, depending on the situation.
3. Avoiding Starvation: Ensure the same transaction is not repeatedly chosen as a victim by limiting the number of rollbacks per transaction.

Deadlocks are an unavoidable reality in complex database systems, but careful design, detection, and recovery strategies can minimize their impact. Implementing timestamp-based prevention schemes and monitoring with wait-for graphs are key practices to maintain DBMS performance and reliability.

Complete Python Course with Advance topics:-Click Here
SQL Tutorial :–Click Here
Download New Real Time Projects :–Click here

UpdateGadh Tip: Efficient handling of deadlocks is crucial for mission-critical applications where transaction delays can lead to significant data inconsistencies and downtime.

deadlock prevention in dbms
what is deadlock in dbms with example
types of deadlock in dbms
deadlock handling in dbms
deadlock in os
deadlock recovery in dbms
deadlock detection in dbms
deadlock in dbms pdf
normalization in dbms
serializability in dbms
deadlock in dbms examples
deadlock in dbms geeksforgeeks