Deadlock In DBMS
A simple definition of Deadlock is, When two or more processes or tasks are stuck, each waiting for the other to finish and release resources, resulting in a “Blocked” where nothing can progress.
Similar to that, In DBMS Deadlock occurs when two or more transactions are stuck, waiting always for each other to let go of something they need.
In this blog will explain Deadlock in DBMS with examples, Types of Deadlock in DBMS, Deadlock handling, Deadlock Algorithms and more.
Want to transform a career in SQL Developer ?
Enroll now and take the first step towards a successful career. Click here to join our courses today!
What is Deadlock In DBMS?
As we discussed earlier, A deadlock takes place when two or more transactions are stuck, each waiting forever for the other to give up something they need. This makes a loop of waiting means they depend on each other (circular dependency), stopping any of the transactions from finishing and moving forward.
Let’s take an example –
| Transaction | Action | Resource X |
| 1 | Requests a lock on Resource X | Locked |
| 1 | Acquires the lock on Resource X | Locked |
| 2 | Requests a lock on Resource Y | Waiting |
| 1 | Requests a lock on Resource Y | Waiting |
| 2 | Acquires the lock on Resource Y | Waiting |
| 1 | Waits (Blocked) | Locked |
| 2 | Waits (Blocked) | Locked |
As you can see in above example –
- Transaction 1 acquires a lock on Resource X.
- Transaction 2 acquires a lock on Resource Y.
- Transaction 1 now needs Resource Y and requests a lock, but it’s currently held by Transaction 2.
- Transaction 2, in turn, needs Resource X and requests a lock, which is held by Transaction 1.
Now, both transactions (Transaction 1 & Transaction 2) are waiting for the other to release a resource, which creates a deadlock. Neither Transaction 1 nor Transaction 2 can continue, and the system is in a “Deadlock” state.
Also Read: Schedule In DBMS
Types Of Deadlock In DBMS
Here are some types of Deadlock in DBMS:
- Resource Deadlocks
- Wait-For Graph Deadlocks
- Communication Deadlock
- Cycle Deadlock
1) Resource Deadlocks
A set of processes is resource-deadlocked when they are locked in a circular wait for resources, it means it depends on each other & each holding resources that others in the set need to proceed. This creates a Deadlock where transactions can wait for many resources at the same time and cannot move forward until they have obtained all of them.
| Process 1 | Process 2 |
| Acquire Resource A | Acquire Resource B |
| Acquire Resource B | Acquire Resource A |
| Release Resource B | Release Resource A |
| Release Resource A | Release Resource B |
As you can see in above example –
- Process 1 has Resource A and wants Resource B.
- Process 2 has Resource B and wants Resource A.
Both processes are in a state of Deadlock because they are both waiting for a resource owned by the other. Neither process can continue, and the system is stuck.
Also Read: Objectives Of DBMS
2) Wait-For Graph Deadlocks
A Wait-For Graph is a graphical representation which is mainly used to analyze and detect deadlocks in a system where multiple processes (transactions) are competing for resources.
This graph helps highlight the relationships between processes and resources. In the context of deadlock detection, a Wait-For Graph is particularly useful for identifying circular wait conditions.
| Process | Requested Resources | Held Resources | Blocked on Resources |
| P1 | R1 | R3 | R2 |
| P2 | R2 | None | R3 |
| P3 | R3 | R1 | R2 |
As you can see in above example –
I) P1 holds R3
II) P2 holds no resources
III) P3 holds R1
- P1 requests R1 but is blocked because P3 is holding it.
- P2 requests R2 but is blocked because P1 is holding it.
- P3 requests R3 but is blocked because P2 is holding it.
3) Communication Deadlock
In DBMS, a communication deadlock occurs when two or more transactions are waiting for each other to release resources, resulting in a situation where none of the transactions can proceed.
It’s similar to Resource Deadlock but Resource Deadlock occurs when transactions fight over database resources on the other hand communication deadlocks occur when transactions need each other’s cooperation but end up stuck, not able to move forward.
| Process | Waiting For Message From | Message Content |
| Process P1 | Process P2 | Request for data update on Table A |
| Process P2 | Process P4 | Request for data validation on Table B |
| Process P4 | Process P3 | Request for lock status on Table A |
| Process P3 | Process P1 | Request to confirm lock release on Table B |
Also Read: Types Of Attributes In DBMS
4) Cycle Deadlock
The meaning of “Cycle Deadlock” is already in the name “Cycle”. A “Cycle Deadlock” in a DBMS happens when resources (transactions) get stuck in a loop, stopping any progress. This loop is visible in the resource allocation graph, where each task is waiting for a resource held by another task in the same loop.
Because of this loop, the whole system comes to a deadlock, and none of the tasks can move forward.
Example:
| Transaction / Resource | R1 | R2 |
| T1 | H | W |
| T2 | W | H |
In this above example –
i) H for Holding
ii) W For Waiting
iii) T1 Transaction1
iv) T2 Transaction2
- Transaction T1 is holding resource R1 and waiting for resource R2.
- Transaction T2 is holding resource R2 and waiting for resource R1.
So basically this forms a circular dependency (Circle)
- T1 is holding R1 and waiting for R2.
- T2 is holding R2 and waiting for R1.
Because of this loop, T1 and T2 can’t move forward, resulting in a “Deadlock.” The system is stuck because each transaction is waiting for a resource that the other one has.
If you want to start a career in DBMS?
Call us For Free Career Counseling +91 8600998107 / +91 7028710777
FAQ’s
What causes deadlocks in DBMS?
Deadlocks happen in databases when multiple transactions compete for the same resources which creates a loop where each transaction is waiting for something the other has.
What is a deadlock in SQL?
In SQL, a deadlock is a situation where two or more transactions are blocked, each waiting for the other to release a resource, leading to a “Deadlock” in the system.
How to check deadlock in SQL query?
To check for deadlocks in SQL, you can use system views like “sys.dm_tran_locks” and “sys.dm_os_waiting_tasks” to identify blocked and blocking sessions.
How can deadlock be avoided In DBMS?
To avoid deadlocks in databases, you can use simple methods like organizing the order in which locks are taken, having a clear hierarchy for locks, and setting timeouts to automatically release locks if they are held for too long.
