What is DeadLock?
When the two user processes have locks on separate objects and each user wants a lock on the other’s process then deadlock will occur.
For EX: User 1 has Lock on object “History” whereas User 2 has Lock on “HistoryDetails” and User 2 wants Lock on “History” and user 1 wants Lock on “HistoryDetails”. such case SQL Server will end up with deadlock by choosing the user who will be a deadlock victim.
When the Lock Manager detected Deadlock by using deadlock detection algorithm the following things will happen by SQL Server once the Deadlock detected.
1. Lock manager will choose one of the user process as a deadlock victim
2.Lock Manager will initiate 1205 error message to the client
3.Lock Manager will kill the Victim Process and free up the resources and allow the other process to continue.