The information on deadlock in this blog is get from my final year of engineering book, Database System Concepts - 7th edition by Henry F. Korth
The deadlock topic is fixed 7 marks question in our university.
Deadlock:
System is deadlocked if there is a set of transactions such that every transaction in the set is waiting for another transaction in the set.
Before starting first learn what is lock:
LOCK:
A lock is a mechanism to control concurrent access to a data item
Data items can be locked in two modes :
1. Exclusive (X) mode.
Data item can be both read as well as written. X-lock is requested using lock-X instruction.
2. Shared (S) mode. Data item can only be read. S-lock is requested using lock-S instruction.
Deadlock Situation:
Consider the partial schedule
Neither T3 nor T4 can make progress — executing lock-S(B) causes T4 to wait for T3 to release its lock on B, while executing lock-X(A) causes T3 to wait for T4 to release its lock on A.
Such a situation is called a deadlock.
To handle a deadlock one of T3 or T4 must be rolled back and its locks released.
SQL Server selects deadlock victim following these rules:
1. The process with the lowest deadlock priority is set as deadlock victim.
2. If the deadlock priority of all the processes involved in deadlock is same, then the process that is least expensive to rollback is selected as deadlock victim.
3. If both the deadlock priority and cost of processes involved in deadlock is same, then the process a process is selected randomly as deadlock victim.
Handling Deadlock:
After a deadlock is detected, the Database Engine ends a deadlock by choosing one of the threads as a deadlock victim.
After choosing it rolls back the transaction of the deadlock victim, and returns a 1205 error to the application.
The 1205 deadlock victim error records information about the threads and resources involved in a deadlock in the error log.
Deadlock Error Detail:
Msg 1205, Level 13, State 45, Line 20
Transaction (Process ID 57) was deadlocked on lock resources with another process and has been
chosen as the deadlock victim. Rerun the transaction.
DEADLOCK PRIORITY:
Alternatively, a user can specify the priority of sessions in a deadlock situation using the SET DEADLOCK_PRIORITY statement. DEADLOCK_PRIORITY can be set to LOW, NORMAL, or HIGH, or alternatively can be set to any integer value in the range (-10 to 10). The deadlock priority defaults to NORMAL.
Deadlock priority low : -5
Deadlock priority high: 5
Normal: 0
After setting low and high priority deadlock is resolved automatically by eliminating low priority deadlock session.
If both sessions have the same deadlock priority, the session with the transaction that is least expensive to roll back is chosen. If sessions involved in the deadlock cycle have the same deadlock priority and the same cost, a victim is chosen randomly.
Setting deadlock priority:
SET DEADLOCK_PRIORITY NORMAL;
GO
or
SET DEADLOCK_PRIORITY -10
GO
DMV’s to identify deadlock priority:
SELECT session_id, deadlock_priority FROM sys.dm_exec_sessions;
GO
Changing deadlock priority:
SET DEADLOCK_PRIORITY 5
GO
SELECT session_id, deadlock_priority FROM sys.dm_exec_sessions;
