Deadlock detection works by detecting circular chains of lock requests. Periodically, Microsoft® SQL Server™ scans for sessions that are waiting on a lock request. During this initial scan, SQL Server flags each waiting session. If a flagged session is still waiting the next time the periodic check occurs, a recursive deadlock search starts. If this recursive deadlock search detects a circular chain of lock requests, the least expensive transaction for SQL Server to undo is chosen as the deadlock victim.
SQL Server ends a deadlock by automatically choosing the user (the deadlock victim) that can break the deadlock. SQL Server rolls back the deadlock victim’s transaction, notifies the user’s application (by returning error message number 1205), cancels the user’s current request, and then allows the transaction of the nonbreaking user to continue.
Alternatively, setting DEADLOCK_PRIORITY LOW for a session causes that session to be chosen as the deadlock victim instead. This option controls how this session reacts in a deadlock situation. If set to LOW, this session becomes the preferred victim when it is involved in a deadlock situation.
Deadlocks affect only a small number of transactions, so this periodic detection mechanism reduces the overhead of deadlock detection.