Troubleshooting Locking
Two locking problems that an application may encounter are deadlocking and blocking.
Deadlocking
Deadlocking is a condition that can occur on any system with multiple users, not only on a relational database management system (RDBMS). A deadlock occurs when two users (or sessions) 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. Microsoft® SQL Server™ detects when two connections have gotten into a deadlock. One of the connections is chosen as a deadlock victim. The connection's transaction is rolled back and the application receives an error.
If deadlocks become a common occurrence and their rollbacks are causing excessive performance degradation, you may need to perform more in-depth investigation. Use trace flag 1204. For example, this command starts SQL Server from the command prompt and enables trace flag 1204:
c:\mssql\binn\sqlservr -T1204
All messages will now appear in the console screen where SQL Server was started and in the error log.
Deadlocking can also occur when using distributed transactions. For more information about resolving deadlocks with distributed transactions, see Transactions Architecture.
Blocking
An unavoidable characteristic of any lock-based concurrent system is that blocking may occur under some conditions. Blocking happens when one connection holds a lock and a second connection wants a conflicting lock type. This forces the second connection to either wait or block on the first.
In this topic, the term connection refers to a single logged-on session of the database. Each connection appears as a system process ID (SPID). Each of these SPIDs is often referred to as a process, although it is not a separate process context in the usual sense. Rather, each SPID consists of the server resources and data structures necessary to service the requests of a single connection from a given client. A single client application may have one or more connections. From the perspective of SQL Server, there is no difference between multiple connections from a single client application on a single client computer and multiple connections from multiple client applications or multiple client computers. One connection can block another connection, regardless of whether they emanate from the same application or separate applications on two different client computers.
To eliminate deadlocking or blocking problems, contact your system administrator. The system administrator should check the waittype, waittime, lastwaittype, and the waitresource columns of sysprocesses to see what activities each SPID is performing.