SQL Server transactions, locks, and blocking

Check for blocking:

1. SYS. dm_tran_locks (sp_lock or syslockinfo must be used in sqlserver2000)

Code SELECT
Request_session_id AS spid,
Resource_type AS restype,
Resource_database_id AS dbid,
Resource_description AS res,
Resource_associated_entity_id AS resid,
Request_mode AS mode,
Request_status AS status
FROM sys. dm_tran_locks;

2. query SYS. dm_exec_connections to obtain conflicting connection information. query SYS. dm_exec_sessions to obtain conflicting session information. (Sp_who/sp_who2 or system table sysprocesses can be used in sqlserver2000)

Select * From SYS. dm_exec_connections
Where session_id in (53, 51 ); Select * From SYS. dm_exec_sessions
Where session_id in (53, 51 );

For details, see chapter 9th of sqlserver2005 inside-program.

Select * From SYS. dm_exec_requests
Where blocking_session_id> 0;