What is Deadlock?

What is Deadlock? 

A deadlock occurs when users try to place exclusive locks on each other’s objects.

Ex:- User1 places an exclusive lock on Table1 and then tries to place an exclusive lock on Table2. User2 already has an exclusive lock on table2, and User2 tries to put an exclusive lock on Table1. This condition causes endless loop of waiting for the locks to be released.

The Database engine picks one of the victim (users) and kills their query and send a error message to users “You are the victim of a deadlock and try again later”.

Deadlock Information Tools

1. 1. Trace Flags:- DBCC TRACEON (1204) & DBCC TRACEON (1222). When these trace flags is enabling, the deadlock information captured by the SQL Server error log.

   2. Deadlock graph event in SQL Profiler:- SQL Server Profiler graphically representation of tasks and resources involved in a deadlock. (Lock:Deadlock and Lock:Deadlock chain events in the Locks events)

   3. System View:- We can find the blocking sessions by writing the following query

Select session_id, status, blocking_session_id from sys.dm_exec_requests

where blocking_session_id > 0

Resolving Deadlock:- After find the session causing the problem we can use KILL command.

> KILL process_id