DBMS Concepts

DBMS Tutorial Components of DBMS. Applications of DBMS The difference between file system and DBMS. Types of DBMS DBMS Architecture DBMS Schema Three Schema Architecture. DBMS Languages.

DBMS ER Model

ER model: Entity Relationship Diagram (ERD) Components of ER Model. DBMS Generalization, Specialization and Aggregation.

DBMS Relational Model

Codd’s rule of DBMS Relational DBMS concepts Relational Integrity Constraints DBMS keys Convert ER model into Relational model Difference between DBMS and RDBMS Relational Algebra DBMS Joins

DBMS Normalization

Functional Dependency Inference Rules Multivalued Dependency Normalization in DBMS: 1NF, 2NF, 3NF, BCNF and 4NF

DBMS Transaction

What is Transaction? States of transaction ACID Properties in DBMS Concurrent execution and its problems DBMS schedule DBMS Serializability Conflict Serializability View Serializability Deadlock in DBMS Concurrency control Protocols

Difference

Difference between DFD and ERD

Misc

Advantages of DBMS Disadvantages of DBMS Data Models in DBMS Relational Algebra in DBMS Cardinality in DBMS Entity in DBMS Attributes in DBMS Data Independence in DBMS Primary Key in DBMS Foreign Key in DBMS Candidate Key in DBMS Super Key in DBMS Aggregation in DBMS Hashing in DBMS Generalization in DBMS Specialization in DBMS View in DBMS File Organization in DBMS What Is A Cloud Database What Is A Database Levels Of Locking In DBMS What is RDBMS Fragmentation in Distributed DBMS What is Advanced Database Management System Data Abstraction in DBMS Checkpoint In DBMS B Tree in DBMS BCNF in DBMS Advantages of Threaded Binary Tree in DBMS Advantages of Database Management System in DBMS Enforcing Integrity Constraints in DBMS B-Tree Insertion in DBMS B+ Tree in DBMS Advantages of B-Tree in DBMS Types of Data Abstraction in DBMS Levels of Abstraction in DBMS 3- Tier Architecture in DBMS Anomalies in Database Management System Atomicity in Database Management System Characteristics of DBMS DBMS Examples Difference between Relational and Non-Relational Databases Domain Constraints in DBMS Entity and Entity set in DBMS ER Diagram for Banking System in DBMS ER Diagram for Company Database in DBMS ER Diagram for School Management System in DBMS ER Diagram for Student Management System in DBMS ER Diagram for University Database in DBMS ER Diagram of Company Database in DBMS Er Diagram Symbols and Notations in DBMS How to draw ER-Diagram in DBMS Integrity Constraints in DBMS Red-Black Tree Deletion in DBMS Red-Black Tree Properties in DBMS Red-Black Tree Visualization in DBMS Redundancy in Database Management System Secondary Key in DBMS Structure of DBMS 2-Tier Architecture in DBMS Advantages and Disadvantages of Binary Search Tree Closure of Functional Dependency in DBMS Consistency in Database Management System Durability in Database Management System ER Diagram for Bank Management System in DBMS ER Diagram for College Management System in DBMS ER Diagram for Hotel Management System in DBMS ER Diagram for Online Shopping ER Diagram for Railway Reservation System ER Diagram for Student Management System in DBMS Isolation in DBMS Lossless Join and Dependency Preserving Decomposition in DBMS Non-Key Attributes in DBMS Data Security Requirements in DBMS DBMS functions and Components What is Homogeneous Database? DBMS Functions and Components Advantages and Disadvantages of Distributed Database Relational Database Schema in DBMS Relational Schema Transaction Processing in DBMS Discriminator in DBMS

Deadlock in DBMS
2019-10-03 12:42:15

Deadlock in DBMS

Deadlock in DBMS

A deadlock is an unwanted condition in which two or more transaction are waiting indefinitely for one another to give up locks. It is said to be one of the most feared complications in database management system as no operation ever gets completed and is in waiting state forever.          


Example  

To understand the concept of deadlock, take an example:In the account table, transaction T1 holds a lock on some rows and needs to update some rows in the order table. Simultaneously, transaction T2 holds a lock on some rows in the order table but needs to update the rows in the account table held by Transaction T1.

Now, the main problem arises. Transaction T1 cannot complete its execution because it is waiting for transaction T2 to release its lock. And similarly, transaction T2 is waiting for transaction T1 to release its lock. Due to this, all activities come to a halt state and remains at a standstill forever until the database management system (DBMS) detects the deadlock and aborts one of the transactions.

Deadlock Avoidance

When a database is stuck in the deadlock condition, then it is always better to avoid the database rather than restarting or aborting the database.

The deadlock avoidance method is used to detect and deadlock situation in advance, and this mechanism is suitable for smaller databases. For the larger databases, deadlock prevention mechanism can be used.

Deadlock Detection

When a transaction waits indefinitely to obtain a lock in the database, then the database management system (DBMS) should detect whether the transaction is involved in a deadlock condition or not. To detect the deadlock cycle in the database lock manager maintains the wait-for-graph.


Wait-For Graph

  • Wait-for graph is one of the suitable methods for deadlock detection, and it is suitable for smaller databases. In this method,
  • graph can be created on the basis of a transaction and their locks on the resources. If the created graph contains a loop or a cycle, then there exists a deadlock situation.
  • For the above scenario, the wait-For graph is shown below:


Deadlock Prevention

  • Deadlock prevention is  method which is suitable for large databases. If the resources are allocated in such a way that the deadlock situation never occurs, then a deadlock can be prevented.   
  • The database management system (DBMS) analyses the operation of a transaction, whether they can create a deadlock condition or not. If they create a situation, then DBMS never allowed that transactions to execution.  

Deadlock prevention mechanism proposes the following two different schemes:   

1. Wait-Die Scheme
2. Wound-Wait Scheme

Wait-Die Scheme

The wait–die scheme is a nonpreemptive technique. When transaction Ti requests a data item which is currently held by Tj. T

hen Ti transaction is allowed to wait only if the timestamp of Ti transaction is smaller than that of transaction Tj ( i.e., Ti is older than Tj). Otherwise, Ti is rolled back (dies).

 
For example, suppose that transactions T14, T15, and T16 have timestamps 5, 10, and 15, respectively. If transaction T14 requests a data item held by T15, then T14 will wait for execution. If T24 transaction requests a data item held by T15, then T16 will be rolled back. 

Wound-Wait Scheme

The wound–wait scheme is a preemptive technique. When Ti transaction requests a data item which is currently held by Tj, Ti is allowed to wait only if it has a timestamp larger than that of Tj (that is, Ti is younger than Tj). Otherwise, Tj transaction is rolled back(Tj is wounded by Ti).