在SQL Server資料庫或OACLE資料庫當中，通常一個會話持有某個資源的鎖，而另一個會話在請求這個資源，就會出現阻塞（blocking）。這是DBA經常會遇到的情況。當出現SQL語句的阻塞時，很多人想查看阻塞的源頭（哪個SQL語句阻塞了哪個SQL），這樣方便直觀、簡潔明瞭的定位問題。但是很多時... ...
在SQL Server資料庫或OACLE資料庫當中，通常一個會話持有某個資源的鎖，而另一個會話在請求這個資源，就會出現阻塞（blocking）。這是DBA經常會遇到的情況。當出現SQL語句的阻塞時，很多人想查看阻塞的源頭（哪個SQL語句阻塞了哪個SQL），這樣方便直觀、簡潔明瞭的定位問題。但是很多時候，很多場景，我們通過SQL語句並不能或者說不容易定位到阻塞者（Blocker）的SQL語句，當然我們可以很容易找到被阻塞的SQL語句，以及它在等待的鎖資源。下麵我們先分析一下SQL Server資料庫的這類場景，然後分析一下ORACLE資料庫的這類場景。如有不足的地方，敬請指出。
在SQL Server當中，我們先準備下麵測試環境（測試用的表和數據）。
USE Test;GOCREATE TABLE Test
(
ID INT , NAME VARCHAR(12));
INSERT INTO TestVALUES (1000, 'Kerry');
INSERT INTO TestVALUES(1001, 'Jimmy');
場景1：我們構造這樣一個簡單的場景，例如如下：
在會話81中執行下麵SQL語句
BEGIN TRAN
UPDATE Test SET NAME='Tina' WHERE ID=1000;
在會話72中執行下麵SQL語句
SELECT * FROM TEST;
在另外一個會話視窗執行下麵語句，查看阻塞（blocker）者和被阻塞者的SQL語句（這裡能夠定位到阻塞者（blocker）的SQL語句）。如下所示
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 BlockingSQLTextFROM sys.dm_tran_locks AS tl WITH(NOLOCK)
INNER JOIN sys.databases AS db WITH(NOLOCK)
ON db.database_id = tl.resource_database_idINNER JOIN sys.dm_os_waiting_tasks AS wt WITH(NOLOCK)
ON tl.lock_owner_address = wt.resource_addressINNER JOIN sys.dm_exec_connections ec1 WITH(NOLOCK)
ON ec1.session_id = tl.request_session_idINNER JOIN sys.dm_exec_connections ec2 WITH(NOLOCK)
ON ec2.session_id = wt.blocking_session_idLEFT OUTER JOIN master.dbo.sysprocesses AS sp WITH(NOLOCK)
ON SP.spid = wt.blocking_session_idCROSS 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
但是這個場景是一個非常理想化的場景，實際場景中,可能會話81接下來會去執行其它SQL語句，它並不會一直停留在這個SQL語句上，例如，我們在會話81中執行SELECT GETDATE();這個SQL語句
BEGIN TRAN
UPDATE Test SET NAME='Tina' WHERE ID=1000;
SELECT GETDATE();
如上所示，此時查到的Blocker者的SQL語句為"SELECT GETDATE();", 而這個SQL其實和被阻塞的SQL沒有半毛關係。即使使用sp_WhoIsActive這樣專業的SQL亦是如此。
當然我們可以查看其等待的鎖對象信息，這也是我們所能追蹤、捕獲的。如下所示：
<Database name="Test"><Locks>
<Lock request_mode="S" request_status="GRANT" request_count="1" /></Locks>
<Objects>
<Object name="Test" schema_name="dbo"><Locks>
<Lock resource_type="OBJECT" request_mode="IS" request_status="GRANT" request_count="1" />
<Lock resource_type="PAGE" page_type="*" request_mode="IS" request_status="GRANT" request_count="1" />
<Lock resource_type="RID" page_type="*" request_mode="S" request_status="WAIT" request_count="1" />
</Locks>
</Object></Objects>
</Database>
這種場景，如果只是某個會話發出的即席查詢，那麼你幾乎已經很難捕獲到阻塞的源頭UPDATE Test SET NAME='Tina' WHERE ID=1000這個SQL語句了。除非你結合其它一些手段，逆向推斷。
場景2：上面查找SQL阻塞的SQL語句，有時候只能定位到某一個存儲過程或一大段即席查詢SQL。
例如，下麵一個構造的存儲過程，一個用戶正在一個會話當中執行它，
CREATE PROCEDURE PRC_TEST
AS BEGIN BEGIN TRAN TR1
UPDATE Test SET NAME='YourName' WHERE ID=1000;
SELECT * FROM sys.sysprocesses WHERE spid=@@SPID;
WAITFOR DELAY '00:00:20';
COMMIT TRAN TR1;
ENDGO
另外一個用戶在另外一個會話執行下麵查詢SQL語句
SELECT * FROM TEST;
查看阻塞的歷史記錄
你會看到捕獲的是整個存儲過程，當然這個測試案例很容易知道是那個SQL語句阻塞了，實際的存儲過程可能業務很複雜，SQL語句也非常多，你想從一個存儲過程裡面找到阻塞者（Blocker）的SQL語句其實是非常麻煩的。需要你仔細甄別，當存儲過程的業務邏輯複雜，SQL語句非常多時，這是一個頭痛的事情。
其實遇到這些場景，我們大可不必一定要查看阻塞這（Blocker）的具體SQL，我們只需要查看被阻塞者，等待的鎖對象資源的相關信息即可，你可以大致判斷到底是一個什麼類型的SQL導致了這類阻塞。
那麼我們接下來看看ORACLE資料庫場景吧。我們先準備一個測試環境（測試表和相關數據）
CREATE TABLE "TEST"."TEST"
( "ID" NUMBER,
"NAME" VARCHAR2(12)
);
INSERT INTO TESTSELECT 1001, 'jimmy' FROM DUAL UNION ALL
SELECT 1002, 'Kerry' FROM DUAL;
COMMIT;
接下來我們在會話視窗一執行下麵SQL：
[oracle@DB-Server ~]$ sqlplus test/test
SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 30 10:16:43 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
