What are Dead Locks in SQL Server

A Dead locks occurs when two transactions have lock on separate object and each transaction requests a lock on the other transaction's object.

For example:-

• Transaction A holds a share lock on row 1.
• Transaction B holds a share lock on row 2.
• Transaction A  requests an exclusive lock  on row 2, but it can not be granted until Transaction B releases the share lock.
• Transaction B requests an exclusive  lock on row 1 but it can not granted until Transaction A releases the shared lock.

Each transaction must wait for the other to release the lock.

A Dead lock can occur when several long running transactions executes concurrently in the same data base.
A Dead lock can also occur as a result of the order in the optimizer processes a complex query, such as join, in which you can not necessarily control the  the order of processing.