SQL Server automatically detects and resolves deadlocks. A deadlock occurs when two users have locks on separate objects and each user wants a lock on the other's object. Each user waits for the other to release their lock. A deadlock typically occurs when several long-running transactions execute concurrently in the same database.
SQL Server ends a deadlock by automatically choosing the user that can break the deadlock. SQL Server rolls back the breaking user's transaction, notifies the user's application (with message number 1205), cancels the user's current request, and then allows the nonbreaking user's process to continue.
In a multiuser situation, each application should check for message 1205. This indicates that the transaction was rolled back. If so, the user's application must restart the transaction.
Note To reduce the chance of a deadlock, minimize indexing (and the possibility of index locking) on appropriate tables, shorten transactions and transaction times, and use resources in the same order in all transactions.
SQL Server automatically prevents lock starvation. Lock starvation occurs when read transactions can monopolize a table or page, forcing a write transaction to wait indefinitely. SQL Server handles read and write transactions on a first-come first-served basis, which prevents lock starvation.