曲演雜壇--為什麼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

執行結果：

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

--沒啥高大上的東西，弄點科普的貼子，以便發圖，哇咔咔！！！