Microsoft® SQL Server 2012 Unleashed

Locking Contention and Deadlocks

In the grand scheme of things, the most likely culprits of SQL Server application performance problems are typically poorly written queries, poor database and index design, and locking contention. Whereas the first two problems result in poor application performance, regardless of the number of users on the system, locking contention becomes more of a performance problem as the number of users increases. It is further compounded by increasingly complex or long-running transactions.

Locking contention occurs when a transaction requests a lock type on a resource that is incompatible with an existing lock type on the resource. By default, the process waits indefinitely for the lock resource to become available. ...