Deadlock in DBMS
In a database management system (DBMS), a deadlock occurs when two or more
transactions are waiting for each other to release resources, such as locks on database
objects, that they need to complete their operations. As a result, none of the transactions
can proceed, leading to a situation where they are stuck or “deadlocked.”
Deadlocks can happen in multi-user environments when two or more transactions are
running concurrently and try to access the same data in a different order. When this
happens, one transaction may hold a lock on a resource that another transaction needs,
while the second transaction may hold a lock on a resource that the first transaction
needs. Both transactions are then blocked, waiting for the other to release the resource
they need.
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, which periodically
scan the transaction log for deadlock cycles and then choose a transaction to abort to
resolve the deadlock.
It is also possible to prevent deadlocks by careful design of transactions, such as always
acquiring locks in the same order or releasing locks as soon as possible. Proper design of
the database schema and application can also help to minimize the likelihood of
deadlocks
In a database, a deadlock is an unwanted situation in which two or more transactions are
waiting indefinitely for one another to give up locks. Deadlock is said to be one of the
most feared complications in DBMS as it brings the whole system to a Halt.
Example – let us understand the concept of Deadlock with an example :
Suppose, Transaction T1 holds a lock on some rows in the Students table and needs to
update some rows in the Grades table. Simultaneously, Transaction T2 holds locks on
those very rows (Which T1 needs to update) in the Grades table but needs to update the
rows in the Student table held by Transaction T1.
Now, the main problem arises. Transaction T1 will wait for transaction T2 to give up the
lock, and similarly, transaction T2 will wait for transaction T1 to give up the lock. As a
consequence, All activity comes to a halt and remains at a standstill forever unless the
DBMS detects the deadlock and aborts one of the transactions.
, Deadlock in DBMS
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.
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:
In a database management system (DBMS), a deadlock occurs when two or more
transactions are waiting for each other to release resources, such as locks on database
objects, that they need to complete their operations. As a result, none of the transactions
can proceed, leading to a situation where they are stuck or “deadlocked.”
Deadlocks can happen in multi-user environments when two or more transactions are
running concurrently and try to access the same data in a different order. When this
happens, one transaction may hold a lock on a resource that another transaction needs,
while the second transaction may hold a lock on a resource that the first transaction
needs. Both transactions are then blocked, waiting for the other to release the resource
they need.
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, which periodically
scan the transaction log for deadlock cycles and then choose a transaction to abort to
resolve the deadlock.
It is also possible to prevent deadlocks by careful design of transactions, such as always
acquiring locks in the same order or releasing locks as soon as possible. Proper design of
the database schema and application can also help to minimize the likelihood of
deadlocks
In a database, a deadlock is an unwanted situation in which two or more transactions are
waiting indefinitely for one another to give up locks. Deadlock is said to be one of the
most feared complications in DBMS as it brings the whole system to a Halt.
Example – let us understand the concept of Deadlock with an example :
Suppose, Transaction T1 holds a lock on some rows in the Students table and needs to
update some rows in the Grades table. Simultaneously, Transaction T2 holds locks on
those very rows (Which T1 needs to update) in the Grades table but needs to update the
rows in the Student table held by Transaction T1.
Now, the main problem arises. Transaction T1 will wait for transaction T2 to give up the
lock, and similarly, transaction T2 will wait for transaction T1 to give up the lock. As a
consequence, All activity comes to a halt and remains at a standstill forever unless the
DBMS detects the deadlock and aborts one of the transactions.
, Deadlock in DBMS
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.
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:
