Deadlock In DBMS

Deadlock In DBMS

Deadlock In DBMS

Deadlock In DBMS

Deadlock In DBMS

Deadlock In DBMS

WhatsApp
LinkedIn
Pinterest

Deadlock In DBMS

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 – 

TransactionActionResource X
1Requests a lock on Resource XLocked
1Acquires the lock on Resource XLocked
2Requests a lock on Resource Y Waiting
1Requests a lock on Resource Y Waiting
2Acquires the lock on Resource YWaiting
1Waits (Blocked)   Locked
2Waits (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: 

  1. Resource Deadlocks
  2. Wait-For Graph Deadlocks
  3. Communication Deadlock
  4. 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 1Process 2
Acquire Resource AAcquire Resource B
Acquire Resource BAcquire Resource A
Release Resource BRelease Resource A
Release Resource ARelease 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.

 

ProcessRequested ResourcesHeld ResourcesBlocked on Resources
P1R1R3R2
P2R2NoneR3
P3R3R1R2

 

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.

ProcessWaiting For Message FromMessage Content
Process P1Process P2Request for data update on Table A
Process P2Process P4Request for data validation on Table B
Process P4Process P3Request for lock status on Table A
Process P3Process P1Request 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 / ResourceR1R2
T1HW
T2WH

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.

Danish

Danish

Leave a Reply

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

Blogs You May Like

Get in touch to claim Best Available Discounts.

If You Are Looking for Job Assistance Please Fill Up the Form.

× How can I help you?