Q
How can I programmatically detect whether a given connection is blocked?

A
A connection is blocked when it requires an object that another connection has a lock on. You can use the system stored procedure sp_lock to retrieve information about the current locks in SQL Server, and you can use the server process ID (SPID) to filter the information that sp_lock returns. To determine whether a given process is waiting for the release of a locked resource, you can execute the sp_GetBlockInfo procedure that follows.

Note: You must execute the procedure before the timeout.

USE master
GO
CREATE PROCEDURE sp_GetBlockInfo
@BlockedSPID as int
AS
IF EXISTS (select *
FROM master.dbo.syslockinfo
WHERE req_spid = @BlockedSPID
AND req_status = 3)
SELECT sli1.req_spid AS SPID,
SUBSTRING (u.name, 1, 8) As Mode,
DB_NAME(sli1.rsc_dbid) AS [Database], OBJECT_NAME(sli1.rsc_objid) AS [Table],
sli1.rsc_Text AS [Resource]
FROM master.dbo.syslockinfo sli1
JOIN master.dbo.spt_values u
ON sli1.req_mode + 1 = u.number
AND u.type = 'L'
JOIN
master.dbo.syslockinfo sli2
ON sli1.rsc_dbid = sli2.rsc_dbid
AND sli1.rsc_objid = sli2.rsc_objid
AND sli1.rsc_text = sli2.rsc_text
WHERE sli2.req_spid = @BlockedSPID
AND sli1.req_status = 1
AND sli1.req_spid <> @BlockedSPID
AND sli2.req_status = 3
ELSE
SELECT CAST(1 as int) AS SPID,
SUBSTRING ('', 1, 8) AS Mode,
DB_NAME(NULL) AS [Database],
OBJECT_NAME(NULL) AS [Table],
CAST(NULL AS nchar(32)) AS [Resource]
WHERE 1=2
GO

The sp_GetBlockInfo procedure tells you the lock mode, the database and object names of the locked resource, and in the case of a blocking chain, which SPID is the root blocker. If the process is not blocked, sp_GetBlockInfo returns an empty recordset.

You can also detect blocks by checking for error 1222, "Lock request time out period exceeded." The LOCK_TIMEOUT setting controls how long a process will wait for locks to be released before timing out. When the lock timeout occurs, SQL Server sends error 1222 to the application. In SQL Server 7.0, this error aborts the statement but does not cause the batch to roll back, so you can look for the Transact-SQL system variable @@ERROR and determine where locks exist.