Deadlock

A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock. For example:

• Transaction A acquires a share lock on row 1.
• Transaction B acquires a share lock on row 2.
• Transaction A now requests an exclusive lock on row 2, and is blocked until transaction B finishes and releases the share lock it has on row 2.
• Transaction B now requests an exclusive lock on row 1, and is blocked until transaction A finishes and releases the share lock it has on row 1.

Both transactions in a deadlock will wait forever unless the deadlock is broken by an external process. The Microsoft SQL Server Database Engine deadlock monitor periodically checks for tasks that are in a deadlock. If the monitor detects a cyclic dependency, it chooses one of the tasks as a victim and terminates its transaction with an error.

Deadlocking is often confused with normal blocking. When a transaction requests a lock on a resource locked by another transaction, the requesting transaction waits until the lock is released. By default, SQL Server transactions do not time out, unless LOCK_TIMEOUT is set. The requesting transaction is blocked, not deadlocked.

Deadlocks can also occur when a table is partitioned and the LOCK_ESCALATION setting of ALTER TABLE is set to AUTO. When LOCK_ESCALATION is set to AUTO, concurrency increases by allowing the Database Engine to lock table partitions at the partition level instead of at the TABLE level. However, when separate transactions hold partition locks in a table and want a lock somewhere on the other transactions partition, this causes a deadlock. This type of deadlock can be avoided by setting LOCK_ESCALATION to TABLE; although this setting will reduce concurrency by forcing large updates to a partition to wait for a table lock.

Deadlock demo:

T1:

Begin transaction

Update table1 set id = 5 where name = ‘Adi’

Waitfor delay ’00:00:05’

Update table2 set name = ‘Aditya’ where surname = ‘Jha’

Commit transaction

T1:

Begin transaction

Update table2 set name = ‘jon’ where surname = ‘cena’

Waitfor delay ’00:00:05’

Update table12 set id = 10 where name = ‘jon’

Commit transaction

If both the transactions are executed at the same time, then T1 locks and updates table1 whereas T2 locks and updates table2. After a delay of 5 ms, transaction A looks for the lock on table2 which is already held by T2 and T2 looks for lock on table1 which is held by T1. So both the transactions cannot proceed further, the deadlock occurs and the SQL server returns the error message 1205 for the aborted transaction.

Trace Flag 1204 and Trace Flag 1222:

When deadlocks occur, trace flag 1204 and trace flag 1222 return information that is captured in the SQL Server error log. Trace flag 1204 reports deadlock information formatted by each node involved in the deadlock. Trace flag 1222 formats deadlock information, first by processes and then by resources. It is possible to enable both trace flags to obtain two representations of the same deadlock event.