1. Deadlock Handling in DBMS

Deadlock is a situation in DBMS where two or more transactions are unable to proceed because each is waiting for one of the others to release a lock. Effective deadlock handling ensures the smooth operation of the database system.

1.1 Deadlock Prevention

Deadlock prevention aims to ensure that the system never enters a deadlock state. There are several strategies for this:

• Mutual Exclusion: Avoiding mutual exclusion is generally not possible, as some resources are inherently non-shareable.
• Hold and Wait: A transaction must acquire all necessary locks before execution or release all held locks before requesting new ones.
• No Preemption: If a transaction holding some resources requests another resource that cannot be immediately allocated, all held resources are released.
• Circular Wait: Enforcing a global ordering of resource types and ensuring that each transaction requests resources in an increasing order of enumeration.

1.2 Deadlock Avoidance

Deadlock avoidance dynamically examines the resource-allocation state to ensure that there can never be a circular wait. The most common algorithm used is the Banker’s algorithm.

1.2.1 Wait-Die Scheme

In this scheme, if an older transaction requests a resource held by a younger one, it waits. Otherwise, it is rolled back (dies).

1.2.2 Wound-Wait Scheme

If an older transaction requests a resource held by a younger one, the younger one is rolled back (wounded). Otherwise, the older transaction waits.

1.3 Deadlock Detection

In deadlock detection, the system allows transactions to request resources and detects deadlocks when they occur. This involves maintaining a wait-for graph and periodically checking it for cycles.

-- Example of detecting deadlocks in SQL Server
SELECT 
    request_session_id, 
    resource_type, 
    resource_associated_entity_id
FROM 
    sys.dm_tran_locks
WHERE 
    resource_type = 'OBJECT'

1.4 Deadlock Recovery

Once a deadlock is detected, the system must recover from it. This involves aborting one or more transactions to break the deadlock. The aborted transactions are rolled back and restarted.

• Victim Selection: Choose the transaction to abort based on factors like how long the transaction has been running, how much it has done, and how many resources it holds.
• Rollback: Abort the selected transaction and roll back its operations.
• Starvation Prevention: Ensure that the same transaction is not repeatedly chosen as a victim.

1.5 Implementation Methods in SQL

SQL-based systems have specific commands and settings to handle deadlocks. Here are a few examples:

1.5.1 Setting Deadlock Priority

-- Set the deadlock priority in SQL Server
SET DEADLOCK_PRIORITY HIGH;
-- Other possible values are LOW and NORMAL

1.5.2 Detecting Deadlocks

-- Enable deadlock graph in SQL Server for analysis
DBCC TRACEON (1222, -1);