Coaching & Solutions - Deadlock Analysis

Deadlock is when two transactions block each other trying to acquire locks on resources and the other transaction holds in a conflicting mode. The lock monitor thread is responsible for deadlock detection and initiates periodic searches to identify and resolve deadlocks. The default between deadlocks searches is 5 seconds.

Detection of deadlock occurs on, when search interval decreases to as low as 100ms based on the frequency, it will alleviate to 5 seconds and it will enter to a lock waits and enter to a deadlock cycle then it will trigger deadlock information.

Transaction - a unit of work performed within the database
Lock - the synchronization mechanism on a resource that protects changes among multiple concurrent transaction.
Lock mode - defines the level of access that other transactions have while the resource is locked.
Blocking - when the transaction request a lock mode that conflicts with a currently held lock and has to wait for that lock to be released.

Deadlocks are SQL Server bug.
Deadlocks can NOT be prevented.
Using NOLOCK on all SELECT statements is the best way to prevent deadlocks from occurring.
Adding covering indexes for every type of query will prevent deadlocks from occuring.
Troubleshooting deadlocks is a complex task that requires an experienced SQL Server developer or administrator

Trace flags (1222, 1205)

> Provide process-level information about the task and it is written in ERRORLOG.

Trace Flag 1204 - provides deadlock graph

SQL Trace/ Profiler
Event Notification/ WMI
Extended events

DELETE statement begins executing that affects one row in a table.
A intent exclusive (IX) lock is acquired for the table
A intent exclusive (IX) lock is acquired for the page containing the row
A exclusive (X) lock is acquired for the row being modified

RID/KEY - a single row locked

> RID - row identifier for a single row in a heap

> KEY - index key for a single row in a index

PAGE - a single page in the database is locked
Hobt - a heap or B-tree (index) partition is locked.
TABLE - the entire table is locked.
METADATA - the table schema definition is locked.
Locks are acquired at multiple levels of granularity to fully protect the lowest-level resource.
Locks are always acquired "top-down", from the table level down to individual rows.

Shared (S) - acquired for read operations that do not modify data.

> No transaction can modify data while the shared lock exists.

> Concurrent SELECT operations can read the data

> Locks are released as soon as the read operation completes unless the isolation level is repeatable read or higher or hints are used

Update (U) - acquires for resources that will be updated.

> Only one transaction can acquire an update (U) lock at a time.

> Prevents deadlocks caused by lock conversions from a shared (S) lock to an exclusive (X) lock.

> The update (U) lock is converted to an exclusive (X) lock to modify the data

> Concurrent shared (S) locks are allowed

Exclusive (X) - acquired for data modifications

> Prevents access to a resource from concurrent transactions.

> Ensures that multiple changes cannot be made to the same resource at the same time

> Reads can only occur using NOLOCK or read uncommitted, read committed snapshot isolation levels.

Intent (I) - acquired to establish the lock hierarchy

> Acquires on higher-level resources to protect locks on lower-level resources

> Prevents transactions from modifying/locking the higher-level resource with an incopatible lock for the lower-level lock being acquired.

If a resource is already locked when a transaction requests a lock on it, the new lock can only be acquired if it is compatible with the existing lock on the resource.
The most common locks are shown here but full compatibility matrix is available online (http://bit.ly/SQLLockCompat )

Setting a higher DEADLOCK_PRIORITY for important transactions will ensure that those transaction are not selected as the deadlock victim if a deadlock occurs with lower priority session.

Setting DEADLOCK_PRIORITY should NOT be used by developers to prevent deadlock involving SELECT statements.
The exception is where deadlocks can NOT be prevented in other ways, and it is critical that the SELECT succeeds.

Deadlock Victim - provides the "process id" of the process that was selected as the victim and rolled back.
Process-List - contains information about waitresource, spid, priority and isolationlevel.
Resource-List - contains identifying information of the process owner, waiter and lock modes that were granted.

Try/Catch blocks in TSQL can handle 1205 errors.
ADO.net can handle deadlocks when they occur by catching the SqlException 1205 error.

Custom retry logic can be implemented to reattempt the operation that was selected as a deadlock victim.
Logging of the deadlock can occur to allow for diagnosis and potential prevention in the future.