曲演杂坛--为什么SELECT语句会被其他SELECT阻塞？

很多刚入门的DBA在捕获阻塞得时候，会问这么一个问题“为什么这个SELECT语句被那个SELECT语句阻塞了，难道不是共享锁么？”

 

让我们来做个小测试，首先准备一些测试数据：

--======================================
--准备测试数据
SELECT 
ROW_NUMBER()OVER(ORDER BY object_id) AS RID,
name AS C1
INTO TB003
FROM sys.all_columns
GO
CREATE UNIQUE CLUSTERED INDEX IDX_RID ON TB003(RID)

注意上面创建的表中RID是唯一聚集索引，因此如果我们按照RID来进行更新或查询，会加行锁。

首先开启一个事务来修改数据：

--==============================
--开启事务，修改一条数据不提交
--使得该回话长期持该行上的X锁
BEGIN TRAN

UPDATE TB003
SET C1='ABC'
WHERE RID=110

然后再分别开启两个回话，进行数据查询：

SELECT * FROM TB003 WHERE RID=110

可以看到两个查询都处于运行状态，迟迟没有返回数据，使用dm_exec_request来查看阻塞：

--====================================
--查看正在执行的SQL的阻塞情况
SELECT
R.session_id,
R.command,
R.blocking_session_id,
R.wait_type,
R.wait_resource
FROM sys.dm_exec_requests AS R
WHERE R.session_id>55
AND R.session_id<>@@SPID

查询结果：

于是就有人开始疑问了，为啥SELECT阻塞SELECT呢？

其实这只是一个先来后到的问题，会话62执行时请求锁KEY: 10:72057594043695104 (8c752d5f60d8)，发现锁被会话59占着，然后就开始等，会话63这时候冒出来，也请求同样的锁，发现锁被59占着，也开始等，只是由于会话62已经做沙发上等着，于是老老实实搬个板凳坐着等，由于怨恨会话62先到占了沙发，所以把会话62列为自己的阻塞对象，期待这会话62早点被干掉，让自己坐上沙发。。。。

以上逗逼一下，只是告诉各位小伙伴，blocking_session_id这个值有点不靠谱，主要还得看资源情况，谁真正持有资源谁才是真正阻塞别人的！

 

如果想看阻塞的真正源头，可以使用下面脚本：

--=================================================================
--查看阻塞链
WITH T1 AS (
        SELECT   S.session_id ,
                ISNULL(RS.blocking_session_id , 0) AS blocking_session_id ,
                CAST('' AS NVARCHAR(200)) AS BlockStep ,
                0 AS BlockNum
        FROM     [sys].[dm_exec_sessions] AS S WITH ( NOLOCK )
        LEFT JOIN [sys].[dm_exec_requests] RS WITH ( NOLOCK )
        ON       S.session_id = RS.session_id
        WHERE    S.session_id IN (
                SELECT  RS1.blocking_session_id
                FROM    [sys].[dm_exec_requests] RS1 )
                AND ISNULL(RS.blocking_session_id , 0) = 0
        UNION ALL
        SELECT   RS.session_id ,
                RS.blocking_session_id ,
                CAST(( '-->'
                        + CAST(RS.blocking_session_id AS NVARCHAR(200))
                        + T1.BlockStep ) AS NVARCHAR(200)) AS BlockStep ,
                1 + T1.BlockNum AS BlockNum
        FROM     [sys].[dm_exec_requests] RS
        INNER JOIN T1
        ON       RS.blocking_session_id = T1.session_id
        )
SELECT session_id ,
    blocking_session_id ,
    ( CASE WHEN T1.BlockStep = ''
            THEN 'KILL ' + CAST(T1.session_id AS NVARCHAR(200))
            ELSE T1.BlockStep
        END ) AS BlockStep ,
    BlockNum
FROM   T1

执行结果：

 

--===============================================

--没啥高大上的东西，弄点科普的贴子，以便发图，哇咔咔！！！