查找阻塞语句

--会话1开启事务更新数据尚未提交
USE AdventureWorks2008;
GO
BEGIN TRANSACTION;
    -- 修改1
    -- 休假时间减8
    UPDATE HumanResources.Employee
        SET VacationHours = VacationHours - 8
        WHERE BusinessEntityID = 4;

--会话2读取会话1中修改的行
USE AdventureWorks2008;
GO
BEGIN TRANSACTION;
    -- 查询1
    -- 这个查询会被会话1阻塞
    SELECT BusinessEntityID, VacationHours
        FROM HumanResources.Employee
        WHERE BusinessEntityID = 4;

select spid,kpid,blocked,waittime,lastwaittype,waitresource,dbid,login_time,last_batch,open_tran,status,loginame
from sys.sysprocesses where spid in(53,54)
select session_id,most_recent_session_id,connect_time,last_read,last_write,client_net_address
from sys.dm_exec_connections where session_id in(53,54)

select top 1 * from AdventureWorks2008.dbo.DatabaseLog
--USE AdventureWorks2008R2;
--GO
--BEGIN TRANSACTION;
    -- 查询1
    -- 这个查询会被会话1阻塞
    SELECT BusinessEntityID, VacationHours
        FROM AdventureWorks2008.HumanResources.Employee
        WHERE BusinessEntityID = 4;

SELECT ec1.session_id                            AS BlockedSessionId      
      ,db.name                                   AS DatabaseName
      ,wt.wait_type                              AS WaitType
      ,ec1.last_read                             AS BlockedTime
      ,wt.wait_duration_ms/1000                  AS [WaitDuration(s)]
      ,ec1.client_net_address                    AS BlockedClientAddress      
      ,h1.text                                   AS BlockedSQLText
      ,wt.blocking_session_id                    AS BlockingSessionId
      ,h2.text                                   AS BlockingSQLText
      ,sp.program_name                           AS BlockingProgramName
      ,COALESCE(sp.loginame, sp.nt_username)     AS BlockingLoginame      
FROM sys.dm_tran_locks  AS tl WITH(NOLOCK)
INNER JOIN sys.databases AS db  WITH(NOLOCK)
  ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt  WITH(NOLOCK)
  ON wt.resource_address = tl.lock_owner_address
INNER JOIN sys.dm_exec_connections  ec1 WITH(NOLOCK)
  ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections  ec2 WITH(NOLOCK)
  ON ec2.session_id = wt.blocking_session_id
LEFT OUTER JOIN master.dbo.sysprocesses AS sp WITH(NOLOCK)
  ON sp.spid = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2

--会话2在可重复读下读取数据
set transaction isolation level repeatable read
go
USE AdventureWorks2008;
GO
BEGIN TRANSACTION;
    -- 查询2
    -- 休假时间为48
    SELECT BusinessEntityID, VacationHours
        FROM HumanResources.Employee
        WHERE BusinessEntityID = 4;

--会话1开启事务更新会话2中读取的行
USE AdventureWorks2008;
GO
BEGIN TRANSACTION;
    -- 修改2
    -- 这个更新会被会话2阻塞
    UPDATE HumanResources.Employee
        SET VacationHours = VacationHours - 8
        WHERE BusinessEntityID = 4;

SELECT ec1.session_id                            AS BlockedSessionId      
      ,db.name                                   AS DatabaseName
      ,wt.wait_type                              AS WaitType
      ,ec1.last_read                             AS BlockedTime
      ,wt.wait_duration_ms/1000                  AS [WaitDuration(s)]
      ,ec1.client_net_address                    AS BlockedClientAddress      
      ,h1.text                                   AS BlockedSQLText
      ,wt.blocking_session_id                    AS BlockingSessionId
      ,h2.text                                   AS BlockingSQLText
      ,sp.program_name                           AS BlockingProgramName
      ,COALESCE(sp.loginame, sp.nt_username)     AS BlockingLoginame      
FROM sys.dm_tran_locks  AS tl WITH(NOLOCK)
INNER JOIN sys.databases AS db  WITH(NOLOCK)
  ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt  WITH(NOLOCK)
  ON wt.resource_address = tl.lock_owner_address
INNER JOIN sys.dm_exec_connections  ec1 WITH(NOLOCK)
  ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections  ec2 WITH(NOLOCK)
  ON ec2.session_id = wt.blocking_session_id
LEFT OUTER JOIN master.dbo.sysprocesses AS sp WITH(NOLOCK)
  ON sp.spid = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2