本文是「Microsoft 知識庫」下列文件的 SQL Server 7.0 更新版，適用於 SQL
Server 6.x：
162361?
(http://support.microsoft.com/kb/162361/
)
INF：瞭解和解決 SQL Server 6.x 封鎖問題
請注意：如需取得最新內容，請參考英文 KB
224453?
(http://support.microsoft.com/kb/224453/EN-US/
)
。
上述文件中，大部分的資訊都已經過更新，納入《SQL Server 7.0
線上叢書》的＜瞭解並避免封鎖＞(Understanding and Avoiding Blocking)
主題內。請在往下閱讀之前，先仔細複習這些資訊，因為文中將不再重複說明。本文的重點在於，如何監視 SQL Server
擷取適當的系統資訊，以及如何分析這些資訊，以便成功解決封鎖問題。
本文使用與上述資訊定義相同的標準術語。在此處的討論中，「連線」一詞意指單一登入的資料庫工作階段。每個連線都會以系統處理序識別碼 (SPID)
顯示。儘管這裡所述的每個 SPID 不是一般觀念中所指的個別處理序內容，但在這裡通常是指「處理序」。確切地說，每個 SPID
都含有伺服器資源，以及提供指定用戶端請求之單一連線服務時所需的資料結構。單一的用戶端應用程式可能有一或多個連線。從 SQL Server
的觀點來看，在單一用戶端電腦上單一用戶端應用程式的多個連線，或來自多個用戶端應用程式或多部用戶端電腦的多個連線之間並沒有差異。一個連線可能會封鎖其他連線，無論是從同一個應用程式，或是兩部不同用戶端電腦上的個別應用程式所發出的。
對於使用鎖定式同步存取的關聯式資料庫管理系統 (RDBMS) 來說，封鎖是一種無可避免的特徵。在 SQL Server
上，當一個 SPID 持有特定資源的鎖定，但另一個 SPID 試圖對同一個資源取得衝突的鎖定類型時，便會發生封鎖的現象。一般而言，第一個 SPID
鎖定資源的時間相當短。當它解除鎖定時，第二個連線即可取得該資源的鎖定，以便繼續處理下去。這種情況相當常見，在一天之內可能發生許多次，對系統效能不致產生明顯的影響。
查詢的持續時間與交易內容，決定了鎖定的長度及對其他查詢的影響。如果查詢沒有在交易時間內執行 (且未使用任何鎖定提示)，則只有在實際上被讀取時，SELECT
陳述式才會鎖定在一個資源上，而非整個查詢期間均能持有。至於 INSERT、UPDATE 和 DELETE
等陳述式，鎖定可以維持整個查詢的時間，如此不但能保持資料的一致性，必要時並可復原查詢。
對於在交易期間所執行的查詢，鎖定持有的期間是由查詢的類型、交易隔離層級，以及查詢是否使用鎖定提示等因素所決定。如需鎖定、鎖定提示和交易隔離層級的說明，請參閱《SQL
Server 7.0 線上叢書》的下列主題：
- ＜瞭解 SQL Server 中的鎖定＞(Understanding Locking in SQL
Server)
- ＜鎖定架構＞(Locking Architecture)
- ＜鎖定相容性＞(Lock Compatibility)
- ＜鎖定提示＞(Locking Hints)
- ＜變更 Oracle 與 SQL Server 的預設鎖定行為＞(Changing Default Locking
Behavior in Oracle and SQL Server)
當鎖定與封鎖到達對系統效能有不良影響的程度時，其原因通常如下：
- 某個 SPID
持有一組資源的鎖定相當久才釋放。這一類的封鎖過一陣子就會自行解除，但可能會讓系統效能降低。
- 某個 SPID
持有一組資源的鎖定相當久且不釋放。這一類的封鎖無法自行解除，導致這些資源無法被存取。
在第一種情況中，封鎖的問題在 SPID 釋放鎖定之後即可自行解決。但是不同的 SPID
可能對不同的資源產生封鎖，因此目標會一直變動。這些情況要使用 SQL Server Enterprise Manager 或個別的 SQL
查詢來進行疑難排解是相當困難的。反倒是第二種情況的外在表現較為一致，因此比較容易診斷出來。
收集封鎖資訊
如果要減輕疑難排解封鎖問題的難度，資料庫管理員可以使用 SQL 指令碼來持續監視 SQL Server
的鎖定與封鎖狀態。這些指令碼可提供一段時間內特定執行個體 (Instance) 的快照集，讓您對問題有通盤的了解。如需使用 SQL
指令碼監視封鎖的說明，請參閱「Microsoft 知識庫」中的下列文件：
251004?
(http://support.microsoft.com/kb/251004/
)
INF：如何監視 SQL Server 7.0 封鎖
271509?
(http://support.microsoft.com/kb/271509/
)
INF：如何監視 SQL Server 2000 封鎖
本文中的指令碼將執行下列工作。如果可能，我們也提供從 Enterprise Manager 或特定
SQL 查詢中取得這項資訊的方法。
- 辨識封鎖鏈結開頭的 SPID。
除了使用上述文件的指令碼之外，您還可以依照下列步驟，使用 SQL Enterprise Manager
找出封鎖鏈結的開頭：
- 展開伺服器群組，然後展開伺服器。
- 展開 [管理]，然後展開 [目前活動]。
- 展開 [鎖定 / 處理序識別碼]。SPID 及其封鎖資訊便會顯示在詳細資料窗格中。封鎖其他資源的 SPID 將會顯示成「(封鎖)」。
然而，請注意，有時必須使用查詢而非 Enterprise Manager，因為某些類型的 tempdb
封鎖問題可能會使您無法執行使用暫存資料表作業的查詢。使用直接查詢可讓您進行必要控制以避免此問題。 - 尋找封鎖 SPID 正在執行的查詢。
指令碼方式使用下列查詢來決定特定 SPID 所發出的命令：
DBCC INPUTBUFFER (<spid>)
- 展開伺服器群組，然後展開伺服器。
- 展開 [管理]，然後展開 [目前活動]。
- 按一下 [處理序資訊]。SPID 便會顯示在詳細資訊窗格中。
- 按兩下封鎖 SPID，查看 SPID 前次所執行的 Transact-SQL 命令批次。
- 尋找保持封鎖 SPID 的鎖定類型。
您可以執行 sp_lock 這個系統預存程序，以便找出這項資訊。或者，您也可以下列方式使用 SQL Enterprise Manager：
- 展開伺服器群組，然後展開伺服器。
- 展開 [管理]，然後展開 [目前活動]。
- 展開 [鎖定 / 處理序識別碼]。SPID 及其持有的鎖定資訊便會顯示在詳細資料窗格中。
- 尋找封鎖 SPID 的交易巢狀層級和處理序狀態。
您可以使用 @@TRANCOUNT 全域變數查出某個 SPID 的交易巢狀層級。不過，您也可以利用查詢 sysprocesses 資料表的方式，從 SPID 外部查出，方法如下：
SELECT open_tran FROM SYSPROCESSES WHERE SPID=<blocking SPID number>
go
您也可以使用 DBCC OPENTRAN
database_name 檢查是否有任何長期的開啟交易存在資料庫中。
收集 SQL Server Profiler 追蹤資訊
除了上述資訊以外，通常還需要擷取伺服器上活動的 Profiler 追蹤，才能查看 SQL Server 上的封鎖問題。如果
SPID 在交易之內執行多個陳述式，只有最後一個陳述式會出現在 DBCC INPUTBUFFER
輸出中。然而，也可能是因為先前所執行的其中一個命令導致鎖定未解除。Profiler 追蹤可讓您看到在目前交易期間內，SPID
執行過的所有命令。請依照下列步驟設定 SQL Server Profiler 以擷取追蹤。
- 開啟 SQL Server Profiler。
- 在 [工具] 功能表上，按一下 [選項]。
- 確定已選取 [所有事件類別] 與 [所有資料行] 選項。
- 按一下 [確定]。
- 在 [檔案] 功能表上，指向 [新增]，然後按一下 [追蹤]。
- 在 [一般] 索引標籤上，指定要擷取資料的追蹤名稱與檔案。
- 在 [事件] 索引標籤上，將下列事件類型新增至追蹤：
摺疊此表格展開此表格
| 標題 | 要新增的事件 | 說明 |
|---|
| 錯誤和警告 | Exception | 此事件表示已發生例外狀況。嚴重性小於 25
的例外狀況，代表 SQL Server 會傳回錯誤訊息給用戶端。嚴重性 25 的例外狀況屬於內部 SQL Server
例外狀況，必須依下列方式加以篩選。 |
| 雜項 | Attention | 此事件表示已發出注意事項訊號。注意事項訊號的一般原因是用戶端取消或查詢逾時。 |
| 工作階段 | Connect | 此事件表示已建立新的連線。 |
| 工作階段 | Disconnect | 此事件表示用戶端已中斷連線。 |
| 工作階段 | Existing Connection | 此事件表示啟動 SQL
Profiler 追蹤時已有連線存在。 |
| TSQL | RPC:Starting | 此事件表示已啟動遠端程序呼叫 (RPC)
的執行。 |
| TSQL | SQL:BatchStarting | 此事件表示已啟動
Transact-SQL 批次的執行。 |
| 預存程序 | SP:StmtStarting | 此事件表示預存程序中的陳述式開始執行。預存程序名稱會出現在此事件開頭的文字中。 |
此外，您還可以加入以下事件以供進一步參考。如果您是在一個大量生產的環境中執行，可能只需要用到上述事件，因為這些已經足夠用來疑難排解封鎖問題。加入下列事件能夠更快地找出問題的源頭，但也會加重系統的負擔及加大追蹤的輸出。
摺疊此表格展開此表格
| 標題 | 要新增的事件 | 說明 |
|---|
| 雜項 | Execution Plan | 此事件顯示已執行的 Transact-SQL
陳述式之計劃樹狀目錄。 |
| 交易 | DTCTransaction | 此事件會追蹤兩個以上資料庫或伺服器之間的
Microsoft Distributed Transaction Coordinator (MS DTC) 交易。 |
| 交易 | SQLTransaction | 此事件會追蹤 SQL
BEGIN、SAVE、COMMIT 及 ROLLBACK TRANSACTION 陳述式。 |
| TSQL | RPC:Completed | 此事件表示已完成遠端程序呼叫 (RPC)
的執行。 |
| TSQL | SQL:BatchCompleted | 此事件表示已完成
Transact-SQL 批次的執行。 |
| 預存程序 | SP:StmtCompleted | 此事件表示預存程序中的陳述式開始執行。 |
- 在 [資料行] 索引標籤上，請確定包括下列資料行：Start Time (開始時間)、End Time (結束時間)、Connection ID
(連線識別碼)、SPID、Event Class (事件類別)、Text (文字)、Integer Data (整數資料)、Binary Data
(二進位資料)、Application Name (應用程式名稱)、NT User Name (NT 使用者名稱) 以及 SQL User Name (SQL
使用者名稱)。如果您額外加入上述第二個表格中的事件，請再加入下列資料行：Duration (期間)、CPU、Reads (讀取) 和 Writes
(寫入)。
- 在 [篩選器] 索引標籤中，排除 SQL Server 內部例外狀況。在 [追蹤事件條件] 方塊中選取 [嚴重性]，並在 [最大值] 方塊中輸入 24。然後，按一下 [確定]。
如需有關監視 SQL Server 傳送至用戶端之錯誤的詳細資訊，請參閱「Microsoft
知識庫」中的下列文件：
199037?
(http://support.microsoft.com/kb/199037/
)
How to capture error messages that are sent to clients from an instance of SQL Server
如需有關使用 Profiler 的詳細資訊，請參閱《SQL Server 線上叢書》。
找出並解決常見的封鎖案例
藉由檢查上述資訊，幾乎均能判斷大部份封鎖問題的原因。本文的其餘部分將討論如何使用此資訊找出和解決一些常見的封鎖案例。以下的討論假設您使用了文章編號
Q251004 (先前已參考到) 中的封鎖指令碼來擷取封鎖 SPID 的相關資訊，並以上述事件建立一個 Profiler 追蹤。
檢視封鎖指令碼的輸出
- 檢查 sysprocesses 輸出以決定封鎖鏈結的開頭。
如果沒有替封鎖指令碼指定快速模式，指令碼輸出中會出現一個標題為 SPIDs at the head of
blocking chains (在封鎖鏈結開頭的 SPID) 的區段，其中列出了封鎖其他人的 SPID：
SPIDs at the head of blocking chains
spid
------
9
10
spid status blocked
9 sleeping 0
10 sleeping 0
11 sleeping 13
12 sleeping 10
13 sleeping 9
14 sleeping 12
- 檢查 sysprocesses 輸出，從中找出封鎖鏈結開頭的 SPID 之相關資訊。
評估下列 sysprocesses 欄位是非常重要的：
- 狀態
這個資料行可讓您很快得知特定 SPID 的狀態。一般而言，sleeping 狀態代表 SPID 已完成執行，正在等候應用程式發出另一個查詢或批次。而 runnable 狀態代表 SPID 目前正在處理某個查詢。下表簡短說明了各個狀態值。
摺疊此表格展開此表格
| 狀態 | 意義 |
|---|
| 背景 | SPID 正在執行背景工作。 |
| Sleeping (睡眠中) | SPID 目前沒有在執行。這通常代表 SPID
正在等候應用程式送出的命令。 |
| Runnable (可執行) | SPID 目前正在執行。 |
| Dormant (休眠) | 和 Sleeping 類似，但 Dormant 還表示 SPID
完成某個 RPC 事件之後被重設。重設會清除 RPC 事件中所使用的資源。這是正常狀態，此時 SPID 可用且正等候進一步的命令。 |
| Rollback (復原) | SPID 正在復原某個交易。 |
| Defwakeup | 表示 SPID 正在等待正被釋放的資源。waitresource 欄位應該指出有問題的資源。 |
| Spinloop | 處理序試圖取得用來對 SMP 系統進行同步存取控制的單一執行緒存取鎖
(Spinlock)，目前處於等候狀態。 |
- Open_tran
這個欄位會告訴您 SPID 的交易巢狀層次。如果這個值大於 0，代表 SPID
正在進行開啟交易，可能持有由交易內任何陳述式所取得的鎖定。 - Lastwaittype、waittype 和 waittime
lastwaittype 欄位告訴您 SPID 前一個或目前的 waittype。這個欄位是 SQL Server 7.0 版所新增，為 waittype 欄位 (為保留的內部二進位資料行) 的字串表示。如果 waittype 是 0x0000，就代表 SPID 目前並未等候任何資源，lastwaittype 值則指出 SPID 前次具有的 waittype。如果 waittype 的值不是零，lastwaittype 值就代表 SPID 目前的 waittype。
如需 lastwaittype 與 waittype 各個值的簡短說明，請參閱「Microsoft 知識庫」中的下列文件：
244455?
(http://support.microsoft.com/kb/244455/
)
Definition of sysprocesses waittype and lastwaittype columns for SQL Server 7.0
waittime 值可用來判斷 SPID 是否有進步。當對 sysprocesses 資料表的查詢在 waittime 資料行所傳回的值小於先前對 sysprocesses 的查詢之 waittime 值時，代表先前的鎖定已取得並釋放，現正等候新的鎖定 (採用非零的
waittime)。要驗證這種情形，可以比較 sysprocesses 輸出之間的 waitresource 值。 - Waitresource
此欄位指出某個 SPID 正在等候的資源。下表列出常見的 waitresource 格式及其代表意義：
摺疊此表格展開此表格
| 資源 | 格式 | 範例 |
|---|
| 表格 | DatabaseID:ObjectID | TAB:5:261575970
此處的資料庫 ID 5 為 pubs 範例資料庫，物件 ID 261575970 為 titles 資料表。 |
| 分頁 | DatabaseID:FileID:PageID | PAG:5:1:104
此處的資料庫 ID 5 是 pubs，檔案 ID 1 是主要資料檔，分頁 104 是屬於 titles 資料表的一個分頁。 |
| 機碼 | DatabaseID:ObjectID:IndexID (Hash value
for index key) | KEY:5:261575970:1 (5d0164fb1eac)
此處的資料庫 ID 5 是 pubs，物件 ID 261575970 是 titles 資料表，索引鍵 ID 1 是叢集索引，雜湊值代表特定資料列的索引鍵值。 |
- 其他資料行
其餘的 sysprocesses 資料行也能提供找出問題根源的資料。其用途視問題發生的環境而定。例如，您可以判斷問題是否只發生在某些用戶端
(hostname)、某些網路程式庫 (net_library)，或是在 SPID 發出最後一個批次之時 (last_batch) 等等。如需所有 sysprocesses 資料行的簡短說明，請參閱《SQL Server 7.0 線上叢書》的＜sysprocesses
(T-SQL)＞主題。
注意：SUID 資料行並不包含在封鎖指令碼輸出中，因為它是一個衍生資料行，它的存在只是為了保持回溯相容性。它不會被 SQL Server
內部使用，查詢這個資料行將使得系統效能降低 (因為它是衍生資料行)，因此未被納入。
- 檢查 DBCC INPUTBUFFER 輸出。
對於任何位於封鎖鏈結開頭或具有非零 waittype 的 SPID 來說，封鎖指令碼會執行 DBCC
INPUTBUFFER 來決定該 SPID 目前的查詢：
DBCC INPUTBUFFER FOR SPID 9
EventType Parameters EventInfo
-------------- ---------- --------------------------------------------
Language Event 0 update titles set title = title
注意： 由於封鎖指令碼包含許多步驟，因此 SPID 可能會出現在第一個部份，作為封鎖鏈結的開頭，但在 DBCC INPUTBUFFER
查詢執行之前它就不會封鎖，且 INPUTBUFFER 也不會擷取。這表示該 SPID
的封鎖已自行釋放，這可能是也可能不是一個問題。此時，您可以使用快速版的封鎖指令碼，以確保在 inputbuffer (輸入緩衝區) 清除之前可將它擷取
(雖然不保證一定成功)，或者從該時段檢視 Profiler 資料，以判斷 SPID 所執行的查詢。
摺疊此表格展開此表格
| 資料列 | DatabaseID:FileID:PageID:Slot(row) | RID:5:1:104:3
此處的資料庫 ID 5 是 pubs，檔案 ID 1 是主要資料檔，分頁 104 是屬於 titles 資料表的一個分頁，插槽 3 代表資料列在分頁上的位置。 |
| 編譯 | DatabaseID:ObjectID | TAB:5:834102012
[[COMPILE]] |
此處的資料庫 ID 5 是
pubs，但物件 ID 834102012 是一個預存程序。這代表 SPID 正在等著編譯預存程序的計劃。
檢視 Profiler 資料
有效地檢視 Profiler
資料在解決封鎖問題時極為有用。最重要的是要瞭解您不需查看所擷取的全部內容；只需選擇性地檢視即可。Profiler 提供的功能，可協助您有效檢視已擷取的資料。在
[屬性] 對話方塊上 (按一下
[檔案] 功能表上的
[屬性])，Profiler 可讓您移除資料行或事件、依資料行分組 (排序)
以及套用篩選器，進而限制要顯示的資料。您可以搜尋整個追蹤或僅搜尋特定欄位以尋找特定值 (按一下
[檔案] 功能表上的
[尋找])。您也可以將 Profiler 資料儲存至 SQL Server 資料表 (在
[檔案] 功能表上，指向
[另存新檔]，再按一下
[資料表])，然後再針對該資料表執行 SQL 查詢。
請務必
只在先前儲存的追蹤檔案上執行篩選。如果您在使用中的追蹤上執行這些步驟，可能會失去自追蹤啟動後所擷取到的資料。請先將使用中追蹤儲存到檔案或資料表
(按一下
[檔案] 功能表上的
[另存新檔])，然後在您繼續之前重新開啟它 (按一下
[檔案] 功能表上的
[開啟])。當您使用已儲存的追蹤檔案時，篩選的動作並不會永遠移除篩選掉的資料，它只是不加以顯示。您可以視需要新增和移除事件和資料行，協助您將焦點集中在搜尋上。
應該檢視的內容：- 哪些命令會讓封鎖鏈結開頭的 SPID 在目前交易內執行？
從追蹤資料內篩選出位於封鎖鏈結開頭的特定 SPID
(在 [檔案] 功能表中按一下 [屬性]，然後在 [篩選器] 索引標籤中指定 SPID 值)。此時就可以檢查在它鎖定其他 SPID 之前所執行過的命令。如果納入 Transaction
(交易) 事件，更可輕易地找出交易的開始時間。否則您也可以在 Text (文字) 資料行中搜尋 BEGIN、SAVE、COMMIT 或 ROLLBACK TRANSACTION 等作業。請使用 sysprocesses 資料表的 open_tran 值來確保您已擷取到所有交易事件。知道執行過的命令與交易內容，即可判斷出某個 SPID 為何持有鎖定。
請記住，您可以移除事件和資料行。您不需要同時檢查開始和結束事件，只要擇一檢查即可。如果封鎖 SPID 不是預存程序，請移除 SP:Starting 或 SP:Completed 事件；SQLBatch 和 RPC 事件中即會顯示程序呼叫。只有在需要參閱該層級的詳細資料時，才需要檢視 SP 事件。 - 對於位於封鎖鏈結開頭的 SPID 之查詢持續多久？
如果您納入了已完成的事件，則 Duration (持續期間) 資料行將顯示查詢執行時間。這有助於找出造成封鎖的長時間查詢。如果想得知此查詢為何執行得這麼慢，請檢視 CPU、Read (讀取) 與 Writes (寫入) 等資料行，以及 Execution Plan 事件。
常見封鎖案例的類別
下表列出了常見的症狀及可能的原因。
Scenario (案例) 資料行的數字對應到本文稍後＜常見的鎖定案例與解決方式＞一節的編號。
Waittype、
Open_Tran 和
Status 等資料行代表
sysprocesses 資訊。
Resolves? 資料行則指出此封鎖是否能自行解除。
摺疊此表格展開此表格
| 案例 | Waittype | Open_Tran | 狀態 | Resolves? | 其他徵狀 |
|---|
| 1 | 非零 | >= 0 | runnable
(可執行) | 是，當查詢完成時。 | Physical_IO、CPU 及/或 Memusage
等資料行的內容會隨著時間而增多。此查詢結束時，其持續時間可能會相當長。 |
| 2 | 0x0000 | >0 | sleeping
(睡眠中) | 否，但 SPID 可被清除。 | 在 Profiler 追蹤內可能可以看到這個 SPID
的注意事項訊號，指出發生了查詢逾時或取消的情形。 |
| 3 | 0x0000 | >= 0 | runnable
(可執行) | 否。必須在用戶端擷取所有資料列或關閉連線後才能釋放。SPID 可被清除，但可能要花上 30 秒的時間。 | 如果 open_tran = 0，且 SPID 在交易隔離層級為預設值時 (READ COMMMITTED) 持有鎖定，也是一個可能的原因。 |
| 4 | Varies | >= 0 | runnable
(可執行) | 否。必須在用戶端擷取所有資料列或關閉連線後才能釋放。SPID 可被清除，但可能要花上 30 秒的時間。 | hostname 資料行 (位於封鎖鏈結開頭的 SPID 之 sysprocesses) 會與 SPID 所封鎖的相同。 |
| 5 | 0x0000 | >0 | rollback
(復原) | 是的。 | 在 Profiler 追蹤內可能可以看到這個 SPID
的注意事項訊號，指出發生了查詢逾時或取消的情形，或者僅僅是發出了一個復原陳述式。 |
| 6 | 0x0000 | >0 | sleeping
(睡眠中) | 最終會發生。當 Windows NT 判斷此工作階段已非作用中，SQL Server 連線即會切斷。 | sysprocesses 中的 last_batch 值會比目前時間早得多。 |
常見的封鎖案例與解決方式
下列案例具有上表所列的特性。這一節將儘可能提供詳細資訊，以及解決方案。
- 查詢正常執行，但執行時間太長所造成的鎖定
解決方式：
此類封鎖問題的解決方式，是想辦法將查詢最佳化。事實上，此類的封鎖問題可能只是效能問題，需要您以此法追蹤。如需有關疑難排解速度過慢之特定查詢的詳細資訊，請參閱「Microsoft
知識庫」中的下列文件：
243589?
(http://support.microsoft.com/kb/243589/
)
在 SQL Server 7.0 或更新版本上的執行緩慢查詢疑難排解
如需全面性的應用程式效能疑難排解，請參閱「Microsoft 知識庫」中的下列文件：
224587?
(http://support.microsoft.com/kb/224587/
)
HOW TO：SQL Server 應用程式效能疑難排解
如果您的查詢執行甚久，封鎖了其他使用者且無法最佳化，則可考慮將它從 OLTP
環境移到決策支援系統。 - 由無法追蹤交易巢狀層級的休眠 SPID 引起的封鎖
此類型的封鎖通常可由睡眠中或是等待命令的 SPID 加以識別，但其交易巢狀階層 (@@TRANCOUNT，來自
sysprocesses 的 open_tran) 大於零。這個問題會發生在應用程式遭遇查詢逾時，或者發出取消但未同時發出必要的數量的 ROLLBACK 及/或 COMMIT
陳述式的情形之下。當 SPID 收到查詢逾時或取消時，它會終止目前的查詢和批次，但不會自動復原或認可交易。應用程式應為此負責，因為 SQL Server
無法只因為單一查詢被取消，就假設整個交易必須復原。查詢逾時或取消，在 Profiler 追蹤內會以該 SPID 的一個 ATTENTION
訊號事件出現。
如果要示範這種情形，請從 Query Analyzer 發送下面這個簡單查詢：
BEGIN TRAN
SELECT * FROM SYSOBJECTS S1, SYSOBJECTS S2
-- Issue this after canceling query
SELECT @@TRANCOUNT
ROLLBACK TRAN
解決方式：
- 應用程式必須適當管理交易巢狀層次，否則可能會在查詢遭取消後造成封鎖問題。這可以下列數個方式之一完成：
- 在用戶端應用程式的錯誤處理常式中，在任何錯誤之後送出 IF @@TRANCOUNT > 0
ROLLBACK
TRAN，即使用戶端應用程式不認為交易已開啟。這是必須的，因為在批次期間呼叫的預存程序可能在用戶端應用程式不知道的情況下已啟動了交易。請注意，某些狀況，例如取消查詢，會使程序從目前的陳述式之後就無法執行，所以即使程序有理由檢查
IF @@ERROR <> 0 並且中止交易，在此情況下，此復原程式碼將不會執行。
- 請使用 SET XACT_ABORT ON
進行連線，或在任何開始交易但未在錯誤之後清除的預存程序中使用。發生執行時期錯誤時，這項設定會中止任何已開啟的交易並將控制交回給用戶端。請注意，在造成錯誤的陳述式之後的
T-SQL 陳述式將不會執行。
- 如果在將連線釋放為集區之前，在開啟連線並執行一些查詢的應用程式 (例如 Web 應用程式)
中使用了連接共用，則暫時停用連接共用可以在用戶端應用程式修改為可適當處理錯誤之前幫忙減緩這個問題。藉由停用連接共用，釋放連線可能會造成 SQL Server
連線的實體登出，而使伺服器復原任何開啟的交易。
- 如果連接共用已啟用且目的伺服器為 SQL Server 2000，將用戶端電腦更新為 MDAC 2.6
或更新版本可能會有幫助。此版 MDAC 元件新增了程式碼到 ODBC 驅動程式和 OLE DB 提供者，所以在重新使用連線之前，連線會被「重設」。對
sp_reset_connection 的呼叫會中止任何伺服器啟始的交易 (由用戶端應用程式所啟始的 DTC 交易不受影響)、重設預設資料庫、SET
選項等等。請注意，連線在從連線集區中被重新使用之前不會重設，所以使用者可以開啟某項交易，然後再將該連線釋放到連線集區，但它可能會有幾秒鐘無法重新使用，在那段期間交易仍會保持開啟。如果連線無法重新使用，連線逾時且從連線集區中移除時，交易就會被中止。因此，用戶端應用程式最好中止其錯誤處理常式中的交易，或使用
SET XACT_ABORT ON 避免可能的延遲。
- 事實上，此類的封鎖問題可能只是效能問題，需要您以此法追蹤。如果查詢執行時間能夠縮短，則查詢逾時或取消的情形便不會發生。重要的一點是，一旦發生逾時或取消的情形，應用程式必須能夠處理，但您也可以從檢查查詢效能中獲益。
如需有關疑難排解速度過慢之特定查詢的詳細資訊，請參閱「Microsoft 知識庫」中的下列文件：
243589?
(http://support.microsoft.com/kb/243589/
)
在 SQL Server 7.0 或更新版本上的執行緩慢查詢疑難排解
如需全面性的應用程式效能疑難排解，請參閱「Microsoft 知識庫」中的下列文件：
224587?
(http://support.microsoft.com/kb/224587/
)
HOW TO：SQL Server 應用程式效能疑難排解
如果您的查詢執行甚久，封鎖了其他使用者且無法最佳化，則可考慮將它從 OLTP
環境移到決策支援系統。
- 某個 SPID 對應的用戶端應用程式未將所有結果資料列擷取至完成，因此造成封鎖
在傳送查詢到伺服器後，所有應用程式必須立即擷取所有結果列才能完成。如果有應用程式沒有擷取所有結果列，鎖定可能就會留在資料表上，封鎖其他使用者。若您使用以無障礙方式傳送
SQL 陳述式到其他伺服器的應用程式時，應用程式必須擷取所有結果列，如果沒有
(如果無法設定成這樣做)，您可能無法解決封鎖問題。如果要避免這個問題發生，您可將設計不良的應用程式限制在報告或決策支援資料庫之內。
解決方式：
應用程式必須重新撰寫，使其能夠擷取所有結果資料列至完成。 - 由於分散式主從架構鎖死造成的封鎖
與傳統鎖死情況不同，分散式鎖死無法使用 RDBMS Lock Manager
偵測。這是因為死結所牽涉的資源中，只有一個是 SQL Server 鎖定。死結的另一面屬於用戶端應用程式層級，SQL Server
對它不具控制權。以下為兩個發生此狀況的範例，以及應用程式可如何避免此狀況的方法。
- 具有單一執行緒的用戶端/伺服器分散式死結
如果用戶端具有多個開啟的連線以及單一執行緒，就可能發生下列分散式死結。簡單的說，dbproc 一詞是指用戶端連線結構。
SPID1------鎖定時封鎖------->SPID2
/\ (等待將結果
| 寫回用戶端)
| |
| | 伺服器端
| ================================|==================================
| <-- 單一執行緒 --> | 用戶端
| \/
dbproc1 <------------------- dbproc2
(等待擷取 (在 dbproc1 上有效封鎖，等待
下一資料列) 執行單一執行緒執行)
- 用戶端/伺服器分散式死結，每個連線具有一個執行緒
即使用戶端每個連線都有個別的執行緒存在，此種分散式死結仍舊有各種變體，如下圖所示。
SPID1------鎖定時封鎖-------->SPID2
/\ (網路寫入時等待) 伺服器端
| |
| |
| INSERT |SELECT
| ================================|==================================
| <-- 每個 dbproc 的執行緒 --> | 用戶端
| \/
dbproc1 <-----資料列------- dbproc2
(等待 (在 dbproc1 上封鎖，等待
插入) 其從緩衝區讀取該資料列)
範例 A 和 B
都是應用程式開發人員應該注意到的基本問題。他們必須撰寫應用程式來適當處理這些情況。
解決方式：
有兩種可靠的解決方案，便是使用查詢逾時或繫結連線。
- 查詢逾時
當提供查詢逾時的時候，如果發生分散式鎖死，當逾時發生時，就會破解鎖死。請參閱
DB-Library 或 ODBC 文件取得使用查詢逾時的詳細資訊。 - 繫結連線
這個功能可供用戶端將多個連線繫結在單一個交易空間中，這樣連線就不會彼此封鎖。如需詳細資訊，請參閱《SQL Server
線上叢書》的＜使用繫結連線＞(Using Bound Connections) 主題。
- 由在「金色」或復原狀態中的 SPID 所造成的封鎖
遭刪除的資料修改查詢、或從使用者定義交易外部所做的取消會被復原。這樣做也會發生用戶端電腦重新啟動、其網路工作階段中斷的副作用。同樣地，被選取為鎖死受害者的查詢也會復原。復原資料修改查詢的速度通常無法像當初套用變更那麼快。例如，如果
DELETE、INSERT 或 UPDATE
已經執行了一個小時，可能也要花上至少一個小時來復原。這是可預期的行為，因為所做的變更需要完全復原，否則將為危及資料庫中交易和實體的完整性。因為這是一定會發生的，SQL
Server 將此 SPID 標示為「金色」或復原狀態 (表示這是無法刪除或無法選取作為鎖死受害者)。這種情況可由觀察 sp_who 輸出來辨識，可能代表 ROLLBACK 命令。sysprocesses 的 Status 資料行會指出 ROLLBACK 狀態，它也會出現在 sp_who 輸出或 SQL Enterprise Manager 的 [目前活動] 畫面上。
解決方式：
您必須等待 SPID 完成復原變更的動作。
如果伺服器在作業中途被關閉，資料庫重新啟動時會處於復原模式，將無法進行存取，直到處理完所有開啟的交易為止。每次交易的啟動復原和執行時期復原費時大略相同，資料庫在此期間無法存取。因此，強迫伺服器關機以在復原模式中修復
SPID，將會有反效果。
如果要避免這種情形，請儘量不要在尖峰時間在 OLTP 系統上執行大批的 INSERT、UPDATE 或
DELETE 作業。可能的話，此種作業最好在活動量小的時候進行。 - 由孤兒連線造成的封鎖
如果用戶端應用程式設陷或用戶端工作站重新啟動時，在某些情況下，伺服器的網路執行階段可能無法立即取消。從伺服器的觀點，用戶端仍然顯示為存在，任何取得的鎖定必須保留。如需詳細資訊，請參閱《SQL
Server 7.0 線上叢書》的＜孤兒連線＞(Orphaned Connections) 主題：
解決方式：
如果用戶端應用程式中斷連線時未適當地清除其資源，您可以使用 KILL 命令來終止 SPID。KILL
命令會使用 SPID 值做為輸入。例如，如果要清除 SPID 9，只需發出下列命令即可：
注意： KILL 命令可能需要 30 秒的時間才能完成，因為 KILL 命令需要進行各種檢查。
封鎖問題與應用程式的關聯
當面對封鎖問題時，一般傾向於注意伺服器端的微調和平台問題。然而，這樣做通常無法解決問題，也會花費原本該用於檢查用戶端應用程式和其送出的查詢的時間和精力。這表示無論應用程式對所作資料庫呼叫顯露的可見度層級為何，封鎖問題都需要經常檢查由應用程式實際送出的
SQL
陳述式，以及應用程式對於查詢取消、連線管理、擷取所有結果列等的實際行為。如果開發工具不允許對連線管理、查詢取消、查詢逾時、結果擷取等明確控制，可能無法解決封鎖問題。在為
SQL Server 選取應用程式開發工具前必須詳加檢視此潛在因素。特別是對於業務很重要的 OLTP 環境。
在資料庫和應用程式的設計和建構階段中，必須非常謹慎小心。尤其，應為每個查詢評估資源耗用、隔離層級和交易路徑長度。每個查詢和交易規模應該愈小愈好。必須遵守良好的連線管理規範。如果沒有切實作到，在使用者量少的情況下，應用程式的效能可能尚可接受。一旦使用者數量規模變大，效能也會急遽下降。
有了合適的應用程式和查詢設計，Microsoft SQL Server
能夠在單一伺服器上同時支援數以千計的使用者，並且難得發生封鎖情況。如需詳細資訊，請參閱《SQL Server 7.0
線上叢書》的＜應用程式設計＞(Application Design) 與＜瞭解並避免封鎖＞(Understanding and Avoiding
Blocking) 等主題。可成功達到這種數量使用者的網站，通常都使用了這些主題中所描述的技術。
參考
如需詳細資訊，請參閱下列手冊：
如需詳細資訊，請參閱下列 Microsoft Training & Certification 課程：
Windows Live
Facebook
Twitter
Linkedin
Digg it
Yahoo
Delicious
StumbleUpon
Yammer
Reddit
Technorati
FriendFeed
Email
回此頁最上方
