Deadlocks occur when two or more processes block each other such that they enter a blocking chain that cannot be resolved without the system’s intervention. With no intervention, processes involved in a deadlock would have to wait indefinitely for one another to relinquish their locks.
SQL Server automatically detects deadlock situations and resolves them by terminating the transaction that did less work. The transaction that was chosen as the deadlock victim will receive error 1205. You can trap such an error with exception-handling code and determine a course of action. Exception handling with deadlock examples is described in the next chapter.
SQL Server gives you a tool to control precedence between sessions in terms of which will ...