Deadlock in DBMS

In a Database Management System (DBMS), a deadlock occurs when two or more transactions are waiting indefinitely for one another to release resources (such as locks on tables, rows, or other database objects). This results in a situation where none of the transactions can proceed, effectively bringing the system to a halt.

What is Deadlock?

The Deadlock is a condition in a multi-user database environment where transactions are unable to the complete because they are each waiting for the resources held by other transactions. This results in a cycle of the dependencies where no transaction can proceed.

Necessary Conditions of Deadlock

• Mutual Exclusion: Only one transaction can hold a particular resource at a time.
• Hold and Wait: The Transactions holding resources may request additional resources held by others.
• No Preemption: The Resources cannot be forcibly taken from the transaction holding them.
• Circular Wait: A cycle of transactions exists where each transaction is waiting for the resource held by the next transaction in the cycle.

How Do Deadlocks Happen?

Deadlocks typically occur in multi-user environments, where multiple transactions run concurrently and access the same data in different orders. Here's how it can unfold:

1. Transaction T1 locks Resource A and needs Resource B.
2. Transaction T2 locks Resource B and needs Resource A.
3. T1 waits for T2 to release Resource B, while T2 waits for T1 to release Resource A.

Neither transaction can proceed, resulting in a circular wait - a classic deadlock condition.

Why Deadlocks Are a Problem?

• Transactions are stuck indefinitely.
• System throughput decreases as transactions remain blocked.
• Resources are held unnecessarily, preventing other operations.
• Can lead to performance bottlenecks or even system-wide standstill if not handled.

Example: Consider the following transactions:

• Transaction T1:
  • Holds a lock on some rows in the Students table.
  • Wants to update rows in the Grades table.
• Transaction T2:
  • Holds a lock on rows in the Grades table.
  • Wants to update the rows in the Students table held by T1.

Now, What Happens?

• T1 waits for T2 to release the lock on Grades.
• T2 waits for T1 to release the lock on Students.

This results in deadlock, and all database activity comes to a standstill.

DBMSs often use various techniques to detect and resolve deadlocks automatically. These techniques include timeout mechanisms, where a transaction is forced to release its locks after a certain period of time, and deadlock detection algorithms

What is Deadlock Avoidance?

When a database is stuck in a deadlock, It is always better to avoid the deadlock rather than restarting or aborting the database. The deadlock avoidance method is suitable for smaller databases whereas the deadlock prevention method is suitable for larger databases. 

One method of avoiding deadlock is using application-consistent logic. In the above-given example, Transactions that access Students and  Grades should always access the tables in the same order. In this way, in the scenario described above, Transaction T1 simply waits for transaction T2 to release the lock on  Grades before it begins. When transaction T2 releases the lock, Transaction T1 can proceed freely. 

Another method for avoiding deadlock is to apply both the row-level locking mechanism and the READ COMMITTED isolation level. However, It does not guarantee to remove deadlocks completely. 

What is Deadlock Detection?

When a transaction waits indefinitely to obtain a lock, The database management system should detect whether the transaction is involved in a deadlock or not. 

Wait-for-graph is one of the methods for detecting the deadlock situation. This method is suitable for smaller databases. In this method, a graph is drawn based on the transaction and its lock on the resource. If the graph created has a closed loop or a cycle, then there is a deadlock. For the above-mentioned scenario, the Wait-For graph is drawn below:

What is Deadlock Prevention?

For a large database, the deadlock prevention method is suitable. A deadlock can be prevented if the resources are allocated in such a way that a deadlock never occurs. The DBMS analyzes the operations whether they can create a deadlock situation or not, If they do, that transaction is never allowed to be executed. 

Deadlock prevention mechanism proposes two schemes: 

1. Wait-Die Scheme (Non-preemptive)

• Older transactions are allowed to wait.
• Younger transactions are killed (aborted and restarted) if they request a resource held by an older one

If T1 requests a resource held by T2:

• If TS(T1) < TS(T2) → T1 is older → T1 waits
• If TS(T1) > TS(T2) → T1 is younger → T1 is aborted (dies) and restarted later with the same timestamp.

Example:

• TS(T1) = 10, TS(T2) = 20
• If T1 (older) wants a resource held by T2 → T1 waits
• If T2 (younger) wants a resource held by T1 → T2 dies and restarts

Prevents deadlock by not allowing a younger transaction to wait and form a wait cycle.

2. Wound-Wait Scheme (Preemptive)

• Older transactions are aggressive (preemptive) and can force younger ones to abort.
• Younger transactions must wait if they want a resource held by an older one.

If T1 requests a resource held by T2:

• If TS(T1) < TS(T2) → T1 is older → T2 is aborted (wounded) and T1 takes over the resource.
• If TS(T1) > TS(T2) → T1 is younger → T1 waits

Example:

• TS(T1) = 10, TS(T2) = 20
• If T1 (older) wants a resource held by T2 → T2 is killed, T1 proceeds.
• If T2 (younger) wants a resource held by T1 → T2 waits

Prevents deadlock by not allowing younger transactions to block older ones.

The following table lists the differences between Wait - Die and Wound -Wait scheme prevention schemes:

Applications

1. Delayed Transactions: Deadlocks can cause transactions to be delayed, as the resources they need are being held by other transactions. This can lead to slower response times and longer wait times for users.
2. Lost Transactions: In some cases, deadlocks can cause transactions to be lost or aborted, which can result in data inconsistencies or other issues.
3. Reduced Concurrency: Deadlocks can reduce the level of concurrency in the system, as transactions are blocked waiting for resources to become available. This can lead to slower transaction processing and reduced overall throughput.
4. Increased Resource Usage: Deadlocks can result in increased resource usage, as transactions that are blocked waiting for resources to become available continue to consume system resources. This can lead to performance degradation and increased resource contention.
5. Reduced User Satisfaction: Deadlocks can lead to a perception of poor system performance and can reduce user satisfaction with the application. This can have a negative impact on user adoption and retention.

Features of Deadlock in a DBMS

1. Mutual Exclusion: Each resource can be held by only one transaction at a time, and other transactions must wait for it to be released.
2. Hold and Wait: Transactions can request resources while holding on to resources already allocated to them.
3. No Preemption: Resources cannot be taken away from a transaction forcibly, and the transaction must release them voluntarily.
4. Circular Wait: Transactions are waiting for resources in a circular chain, where each transaction is waiting for a resource held by the next transaction in the chain.
5. Indefinite Blocking: Transactions are blocked indefinitely, waiting for resources to become available, and no transaction can proceed.
6. System Stagnation: Deadlock leads to system stagnation, where no transaction can proceed, and the system is unable to make any progress.
7. Inconsistent Data: Deadlock can lead to inconsistent data if transactions are unable to complete and leave the database in an intermediate state.
8. Difficult to Detect and Resolve: Deadlock can be difficult to detect and resolve, as it may involve multiple transactions, resources, and dependencies.

Disadvantages

1. System downtime: Deadlock can cause system downtime, which can result in loss of productivity and revenue for businesses that rely on the DBMS.
2. Resource waste: When transactions are waiting for resources, these resources are not being used, leading to wasted resources and decreased system efficiency.
3. Reduced concurrency: Deadlock can lead to a decrease in system concurrency, which can result in slower transaction processing and reduced throughput.
4. Complex resolution: Resolving deadlock can be a complex and time-consuming process, requiring system administrators to intervene and manually resolve the deadlock.
5. Increased system overhead: The mechanisms used to detect and resolve deadlock, such as timeouts and rollbacks, can increase system overhead, leading to decreased performance.