SQLServer 事务、锁、阻塞

检查阻塞：

1. sys.dm_tran_locks (SQLServer2000中需要使用sp_lock或系统表syslockinfo)

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. 查询sys.dm_exec_connections视图可以得到发生冲突的连接信息；查询sys.dm_exec_sessions视图可以得到发生冲突的会话信息。（SQLServer2000中可以使用sp_who/sp_who2或系统表sysprocesses)

SELECT * FROM sys.dm_exec_connections
WHERE session_id IN(53, 51);

SELECT * FROM sys.dm_exec_sessions
WHERE session_id IN(53, 51);

详见SQLServer2005 Inside - Program第9章

SELECT * FROM sys.dm_exec_requests
WHERE blocking_session_id > 0;