Study Smart, Not Hard. - StudyMuch

Deadlock in DBMS

Published by StudyMuch on

Deadlock in DBMS

Deadlock in DBMS

Deadlock is a common issue that can occur in database management systems (DBMS) and can result in a halt in system operations, leading to significant disruptions and potential data loss. As a database administrator or a software developer working with databases, it’s crucial to understand what deadlock is, how it occurs, and how to prevent and resolve it. In this blog post, we will explore the concept of Deadlock in DBMS, its causes, prevention strategies, and resolution techniques.

What is Deadlock?

Deadlock refers to a situation in which two or more transactions in a DBMS are waiting indefinitely for a resource that is held by another transaction, and none of them can proceed. In other words, it’s a state in which multiple transactions are blocked and unable to complete their execution due to a circular dependency on resources.

Causes of Deadlock

Deadlock can occur due to various reasons in a DBMS. Here are some common causes:

  • Resource Competition: When multiple transactions are competing for the same resources, such as locks on database objects or system resources like CPU, memory, or disk space, and are unable to release them, deadlock can occur.
  • Locking Mechanisms: Most DBMS use locking mechanisms to ensure data integrity and consistency. If transactions request and hold locks on resources in a way that creates a circular dependency, where each transaction is waiting for a lock that is held by another transaction, deadlock can occur.
  • Poor Transaction Scheduling: Inadequate transaction scheduling algorithms can also contribute to deadlock. For example, if transactions are not scheduled in a way that avoids circular dependencies on resources, it can lead to deadlock situations.
  • Concurrent Access: Concurrent access to shared resources can also lead to deadlock. For instance, if multiple transactions are trying to update the same row in a database simultaneously and are waiting for each other to release the lock on that row, deadlock can occur.

Let’s consider an example to illustrate a deadlock scenario. Suppose there are two transactions, T1 and T2, operating on two tables, Student and Grade, respectively. Transaction T1 holds a lock on certain rows in the Student table and needs to update rows in the Grade table. At the same time, transaction T2 holds a lock on certain rows in the Grade table and needs to update the rows in the Student table that are locked by T1.

Now, if T1 requests a lock on the rows in the Grade table that are held by T2, and T2 requests a lock on the rows in the Student table that are held by T1, a circular dependency is formed, resulting in a deadlock. Both transactions are waiting for each other to release locks, and neither can proceed, leading to a standstill.

Deadlock in DBMS

For example:

Transaction T1:
1. Locks rows in Student table.
2. Requests lock on rows in Grade table.

Transaction T2:
1. Locks rows in Grade table.
2. Requests lock on rows in Student table.

In this scenario, T1 and T2 are stuck in a deadlock, where T1 is waiting for the lock held by T2, and T2 is waiting for the lock held by T1, causing both transactions to be in a waiting state indefinitely.

To resolve the deadlock, DBMS can take actions such as aborting one of the transactions, rolling back one of the transactions, or forcing the release of locks in a controlled manner. Deadlock prevention and detection techniques, such as using timeouts, resource allocation strategies, and transaction scheduling algorithms, can also be employed to minimize the likelihood of deadlock occurrence in a DBMS. Proper design and management of transactions and resource usage are crucial to avoid deadlocks and ensure efficient operation of a DBMS.

Avoidance of deadlock

It is preferable to avoid a database when it is in a deadlock state as opposed to terminating the database or restating it. It is a waste of time and money to do this.

Any deadlock situation is anticipated using a deadlock avoidance method. A technique called “wait for graph” is used to identify deadlocks, but this technique is only appropriate for smaller databases. A deadlock prevention strategy can be utilised for larger databases.

Detection of Deadlocks

When a transaction in a database waits interminably for a lock, the database management system (DBMS) should determine if the transaction is in a deadlock or not. The deadlock cycle in the database is detected by the lock manager’s Wait for the graph.

Deadlock Prevention

Deadlock prevention is a mechanism used in database management systems (DBMS) to eliminate the possibility of deadlock occurrence. It focuses on designing the system and transactions in a way that prevents the formation of circular dependencies and potential deadlocks.

There are various techniques used for deadlock prevention, including:

  • One-way locking: Transactions are allowed to request locks in only one direction, preventing circular dependencies. For example, if a transaction T1 has locked resource A, it can request a lock on resource B, but not on resource C if it is already locked by another transaction.
  • Two-phase locking: Transactions follow strict protocols for acquiring and releasing locks in two phases – the growing phase and the shrinking phase. This ensures that transactions request and release locks in a consistent order, avoiding circular dependencies and potential deadlocks.
  • Lock timeout: Transactions are given a specific time limit to obtain a lock, and if the lock cannot be obtained within that time, the transaction is rolled back to prevent indefinite waiting and potential deadlocks.
  • Lock hierarchy: Transactions are required to request and release locks in a predefined hierarchy, preventing circular dependencies. For example, if a transaction T1 has locked resource A, it cannot request a lock on resource B unless it first releases the lock on resource A.
  • Resource pre-allocation: Resources are allocated to transactions before they start execution, ensuring that all necessary resources are available without the need for locking during transaction execution. This prevents potential deadlocks arising from lock contention.
  • Lock Ordering: Enforcing a strict ordering of locks can help prevent deadlock. For example, if transactions always request locks on resources in a consistent and predetermined order, it can prevent circular dependencies and reduce the chances of deadlock.
  • Deadlock Detection: Implementing deadlock detection algorithms can help identify and prevent deadlock situations. These algorithms periodically check the system for potential deadlocks and take actions to resolve them, such as aborting one or more transactions to break the deadlock.
  • Timeout Mechanisms: Implementing timeout mechanisms, where transactions are automatically aborted if they are unable to obtain a lock within a specified time, can also prevent deadlock situations.
  • Resource Allocation Strategies: Using resource allocation strategies, such as resource reservation or optimistic concurrency control, can help prevent deadlock. These strategies ensure that transactions reserve resources in advance or use optimistic techniques to access resources without acquiring locks, reducing the chances of deadlock.

Deadlock prevention techniques are typically implemented at the system design level and require careful planning and coordination to ensure that transactions can execute without encountering deadlock situations. Proper transaction and resource management, along with a well-designed system architecture, can help prevent deadlocks and ensure smooth operation of a DBMS.

Deadlock in DBMS

Wait-Die scheme

In this scheme, if a transaction requests for a resource which is already held with a conflicting lock by another transaction then the DBMS simply checks the timestamp of both transactions. It allows the older transaction to wait until the resource is available for execution.

Let’s assume there are two transactions Ti and Tj and let TS(T) is a timestamp of any transaction T. If T2 holds a lock by some other transaction and T1 is requesting for resources held by T2 then the following actions are performed by DBMS:

Check if TS(Ti) < TS(Tj) – If Ti is the older transaction and Tj has held some resource, then Ti is allowed to wait until the data-item is available for execution. That means if the older transaction is waiting for a resource which is locked by the younger transaction, then the older transaction is allowed to wait for resource until it is available.

Check if TS(Ti) < TS(Tj) – If Ti is older transaction and has held some resource and if Tj is waiting for it, then Tj is killed and restarted later with the random delay but with the same timestamp.

Wound wait scheme in DBMS

The wound-wait scheme is a deadlock prevention mechanism used in database management systems (DBMS) where transactions are given priority based on their timestamp or age. In this scheme, if an older transaction requests a resource that is held by a younger transaction, the older transaction “wounds” or forces the younger transaction to abort and release the resource. After a brief delay, the younger transaction is restarted, but with the same timestamp or age.

Alternatively, in the wound-wait scheme, if an older transaction holds a resource that is requested by a younger transaction, the younger transaction is forced to wait until the older transaction releases the resource. This prevents potential deadlocks by ensuring that older transactions are given priority over younger transactions in case of conflicting resource requests.

Resolution of Deadlock

Despite preventive measures, deadlock situations can still occur. When a deadlock is detected, it needs to be resolved promptly. Here are some common techniques for deadlock resolution:

  • Deadlock Victim Selection: Selecting a transaction as a victim and aborting it is one way to resolve deadlock. The selection can be based on various criteria, such as the transaction with the least progress, the transaction that has acquired the fewest resources, or the transaction that started last.
  • Rollback and Restart: When a transaction is aborted, the changes made by that transaction need to be rolled back to their original state. The aborted transaction can then be restarted, allowing it to reacquire the necessary resources and continue execution.
  • Resource Preemption: Another approach to resolve deadlock is resource preemption. In this technique, a transaction that is holding a resource is forced to release it so that other waiting transactions can acquire it and progress.
  • Timeouts and Retries: Implementing timeouts and retries can also help resolve deadlock situations. If a transaction times out while waiting for a resource, it can be automatically aborted, and the system can attempt to restart it with a different resource allocation strategy.
  • Avoiding Cascading Rollbacks: When resolving deadlock, it’s essential to avoid cascading rollbacks, where aborting one transaction leads to the rollback of multiple dependent transactions. This can be achieved by carefully selecting the victim transaction and considering the impact of its rollback on other transactions.


Deadlock is a significant concern in DBMS and can disrupt system operations, causing delays and potential data loss. It’s crucial for database administrators and software developers to understand the causes of deadlock and implement preventive measures to avoid it. Strategies such as lock ordering, deadlock detection, timeout mechanisms, and resource allocation strategies can be used to prevent deadlock. If deadlock does occur, resolution techniques such as victim selection, rollback and restart, resource preemption, timeouts and retries, and avoiding cascading rollbacks can be applied. By understanding deadlock and implementing appropriate prevention and resolution strategies, you can ensure the smooth and efficient functioning of your DBMS and minimize the impact of potential deadlock situations.

Read Related;


Leave a Reply

Avatar placeholder

Your email address will not be published. Required fields are marked *