一個特殊的SQL Server阻塞案例分析

上周，在SQL Server資料庫下麵遇到了一個有意思的SQL阻塞（SQL Blocking）案例。其實個人對SQL Server的阻塞還是頗有研究的。寫過好幾篇相關文章。 至於這裡為什麼要總結一下這個案例，因為這個案例有點意思：

1：使用DMV視圖捕獲到的Blocking SQL為一個查詢語句（這個不是真正引起阻塞的源頭SQL語句），等待事件為LCK_M_S。

2：出現阻塞的會話非常多，阻塞的量非常大，使用DMV視圖始終捕獲不到相關表的DML操作語句。捕獲到全是SELECT語句。

我們知道，在SQL Server中SELECT查詢是不會阻塞SELECT查詢的（不瞭解這個問題的，具體可以參考博客SQL Server中SELECT會真的阻塞SELECT嗎？ )。而且被阻塞的語句的等待類型為LCK_M_S，那麼可以肯定：那個阻塞的源頭會話中，存在對該表的DML操作，而且事務由於某些原因未提交。只是那個會話最後執行的SQL語句為一個SELECT查詢。因為有時候，我們根本不能定位到SQL阻塞的源頭SQL語句，具體參考我的博客“為什麼資料庫有時候不能定位阻塞（Blocker）源頭的SQL語句”，此處不做重覆贅述。

我們用一個簡單的例子來模擬這個真實的案例，如需所示，首先創建一個測試表：

CREATE TABLE TEST (id    INT);

GO

INSERT INTO TEST VALUES(1);

然後再通過2個會話，模擬這樣的阻塞案例，如下所示：

會話A：

SET IMPLICIT_TRANSACTIONS ON;

GO

INSERT INTO TEST VALUES(2)

GO

SELECT  * FROM TEST WHERE ID =1;

註意：先設置開啟隱式事務，第一次執行插入數據語句，然後執行SELECT查詢。

   會話B：

SELECT * FROM TEST WHERE id=1;

   會話C:

SELECT wt.blocking_session_id                    AS BlockingSessesionId

      ,sp.program_name                        AS Blocking_ProgramName

      ,COALESCE(sp.LOGINAME, sp.nt_username)    AS Blocking_HostName    

      ,ec1.client_net_address                    AS ClientIpAddress

      ,db.name                                AS DatabaseName        

      ,wt.wait_type                            AS WaitType                    

      ,ec1.connect_time                        AS BlockingStartTime

      ,wt.WAIT_DURATION_MS/1000                AS WaitDuration

      ,ec1.session_id                            AS BlockedSessionId

      ,h1.TEXT                                AS BlockedSQLText

      ,h2.TEXT                                AS BlockingSQLText

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 tl.lock_owner_address = wt.resource_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 

在會話C中查看SQL阻塞情況，如下截圖所示，我們看到是SELECT查詢阻塞了SELECT查詢。

如上測試所示，因為是構造模擬案例，開啟了“上帝視角”，所以你不會覺得有什麼問題，實際情況是：應用程式是一個Java應用程式，而且是O/R Mapping框架（com.j256.ormlite），我將上面情況反饋給開發、Support人員，明確告訴他們阻塞的會話肯定有一個DML操作。讓他們查找定位代碼。但是諸多原因、因素疊加在一起（外包項目；溝通問題；對資料庫的阻塞的的理解），溝通的效果讓人很是無語。只能拿出確切的證據。由於那個框架開啟了隱性事務（事後跟蹤、分析發現的），而且UPDATE語句非常快，你使用DMV視圖定位到的阻塞源頭都是SELECT語句。顯然這個不是我想要的。

於是，我打算使用SQL Server Profiler裡面的“Blocked process report”事件來定位阻塞的源頭，在跟蹤之前，修改'blocked process threshold (s)'的值。如下所示，

exec sp_configure 'show advanced options',1;

reconfigure with override

exec sp_configure 'blocked process threshold (s)',4

reconfigure with override

然後設置SQL Server Profiler的相關選項和過濾條件。就像我下麵的測試的一樣，Blocked process report依然無法定位到阻塞的源頭SQL語句，如下所示：