Deadlock

Deadlock is a situation where two or more transactions wait for completion of each-other for resources but none of them completes.

or

Deadlock occur between the transactions when 2 or more translations permanently block each-other in such a ways that each transaction having a lock on the resource where the other tasks are trying to lock.


In SQL Server, deadlock-detractor thread monitors the deadlock situation. Once the situation  occurs, SQL Server rollback  the transaction which is less expensive with error message "Aborted Transaction error message 1205" and roll-forward the transaction which is more expensive compare to the other one.

Types of Deadlocks in SQL Server:

 In SQL Server, there are 6 types of deadlocks are there...

1. Read-Write Deadlock
2. Write-Write Deadlock
3. Parallelism Deadlock
4. Key Lookup Deadlock
5. Rang-scans and Serializable Deadlock
6. Partition Escalation Deadlock