IT工程師數位筆記本

      SQL SERVER 查詢性能優化——分析事務與鎖（一）

      SQL SERVER 查詢性能優化——分析事務與鎖（二）

      SQL SERVER 查詢性能優化——分析事務與鎖（三）

上接SQL SERVER 查詢性能優化——分析事務與鎖（四）

（四）未檢測到的分布式死鎖

        某應用程序持有數據庫資源，開啟事務之后又與用戶交互，而在與用戶的交互過程中出現了錯誤，導致數據庫資源遲遲不能釋放。SQL SERVER 2005/2008 動態管理視圖sys.dm_exec_requests提供相關信息，該SESSION_ID的status字段值為“sleeping”，wait_type為“NULL”值。如果是SQL 2005則可以通過Microsoft SQL Server Management Studio管理工具中的“活動監視器--》進程信息”視圖，該進程的“開啟事務字段”顯示非“0”值。如下圖。

      在SQL 2005(2008)中執行代碼，即SQL SERVER 查詢性能優化——分析事務與鎖（二）中的“例一”，也就是下面的代碼，得到如下圖。

select spid 進程,STATUS 狀態, 登錄帳號=SUBSTRING(SUSER_SNAME(sid),1,30)

,用戶機器名稱=SUBSTRING(hostname,1,12)

,是否被鎖住=convert(char(3),blocked)

,數據庫名稱=SUBSTRING(db_name(dbid),1,20),cmd 命令,waittype as 等待類型

,last_batch 最后批處理時間,open_tran 未提交事務的數量

from master.sys.sysprocesses

--列出鎖住別人（在別的進程中blocked字段中出現的值）但自己未被鎖住(blocked=0)

Where spid in (select blocked from master.sys.sysprocesses) and blocked=0

      由于應用程序持有事務，而且應用程序出錯之后，沒對事務的相應處理，也沒有需要等待的資源，但持有事務，與前一種（三）情況類似，但通過SQL PROFILER工具進行跟蹤，卻無法發現任何錯誤事件。

建議解決方式

     應用程序所造成的分布式死鎖，很難加以跟蹤分析，需要程序開發人員自行記錄該應用程序的行為，比較多用戶情況下，在進行哪些工作之后，系統就遲滯無法正常執行下去。這需要程序開發人員保持良好的開發習慣：事務越晚開啟越好，使用資源越少越好，一旦開啟了事務遲早關閉，事務執行過程中不要與用戶有任何交互，要輸入的參數或內容應該在開啟事務之前就應該輸入完畢，對相關數據的各種校驗也要在開啟事務之前進行校驗，事務應該只是在往數據庫中插入更新數據時開啟，插入更新完畢之后，就立即關閉。

（五）鎖定數據粒度太低或太高

        用戶設置不當的鎖定粒度時，如果設置事務一律使用Row lock或table lock均可能產生問題，或是當系統資源使用過度，也很容易產生被鎖定的情形。

建議解決方式

       可以通過SQL PROFILER 觀察“TextData”字段所呈現的SQL語句，觀察該應用程序是否設置了鎖定提示，若想要暫時停止鎖定提示造成的影響，可以通過以下語句

       Dbcc traceon(8755)

或以SQL SERVER 激活參數-T 8755 來停止鎖定提示功能，若有改善，可以重新考慮從應用程序從新移除鎖定提示的可能性。

（六）Compile Blocking

         此現象是由于編譯存儲過程導致被鎖定，在master.sys.sysprocesses視圖中或sp_lock存儲過程中觀察到的等待資源字段中的內容是“COMPILE”，或者使用SQL PROFILER 錄制過程中出現大量的“SP:REComplie”事件。由于重新編譯需要耗費CPU資源，所以，此種鎖定是在一長串的被鎖定連接中，單一鎖定者鎖定時間不長，但整個鏈接各點都有一點耗時，所以在鏈接尾端的被鎖定者需要等待較長時間。同時會出現CPU的使用率比較高。

        當存儲過程中使用了緩存數據表，而該緩存數據表還需要設置結構，如需要要設置主鍵或者利用緩存數據表開打開游標，則每次調用該存儲過程進，都會要求重新編譯。或這個存儲過程是當應用程序執行時，常常會被調用的熱門存儲過程，就會出現Compile Blocking的狀況出現。

       但存儲過程第一次使用時，也會需要編譯，所以不要一看到是在等待編譯，就識以為是COMPILE Blocking現象。

建議解決方式

      使用sp_executesql執行語句，即使用sp_executesql執行SQL語句，SQL語句不會編譯為存儲過程執行計劃的一部分，因此在執行該類語句時，SQL SERVER 會自由的使用高速緩存中的現有語句計劃，或者在執行階段建立新的執行計劃，不管任何一種情況，調用存儲過程的計劃都不會受影響，也不必進行重新編譯。

      EXECUTE語句也有相同的效果，但不建議你使用。因為使用EXECUTE沒有使用SP_EXECUTESQL語句的效率高，因為前者不允許查詢參數化。

三、基本原則：

     1. 事務不可以跨批處理，語句越短越好，事務期間不要與用戶進行交互

     2. 小心處理逾時放棄，或者執行錯誤等情況。

    3. 正確建立索引。可以參考本人前面的相關文章。

    （如SQL Server 查詢性能優化——創建索引原則（一）

      SQL Server 查詢性能優化——覆蓋索引（一）等系列文章）

    4. 數據表最好有聚集索引，而且聚集索引的鍵值不要太大，因為所有的非聚集索引存儲的都是聚集索引的鍵值。不要使用經常需要進行更新的字段做為聚集索引的鍵值，因為聚集索引一旦進行了變更，則所有的非聚集索引也要跟著進行變更，導致大量的鎖定。索引建少了，影響查詢效率，建多了，浪費維護的資源與降低新增、修改、刪除的效率，所以建好索引之后，要小心觀察SQL SERVER 使用索引的情況，將多余的索引刪除，對于數據密度大，或者查詢條件鑒別率太低的字段不要建立索引。

     5. 盡量不要激活Implicit Transaction，以免它長時間的持有事務。

     6. 盡量降低事務隔離級別

     7. 進行壓力測試以了解當大用戶量時，交互將造成何種程度的鎖定問題。

  四、 防止與處理死鎖

        1.盡量避免或盡快處理鎖定，當鎖定與被鎖定過多時，就可能造成死鎖

        2.訪問資源的順序要相同。例如連接A先訪問資源1，然后訪問資源2，而連接B的訪問順序與之相反，則可能發生死鎖。不要在開啟事務的情況下，調用外部程序，容易造成分布式死鎖。

        3.讓不同的連接使用相同的鎖定。或兩條連接因為修改相同的資源而互相鎖定，如果你的系統對于更新數據的正確性不做強制性要求，可以考慮使用sp_getbindtoken和sp_bindsession兩個系統存儲過程，讓連接共享鎖定，則兩條連接同時更新數據，也就可能造成數據更新遺失。

例：

use Test

go

create proc sp_upd_OPINION

@OPINIONID varchar(20),

@bindToken varchar(255) output

as

exec sp_getbindtoken @bindToken output

update WBK_OPINION set OPINION_VALUE='true'

where OPINION_ID=@OPINIONID

go

create proc sp_upd_OPINION2

@OPINIONID varchar(20),

@bindSession varchar(255) output

as

exec sp_bindsession @bindSession 

update WBK_OPINION set OPINION_VALUE='False'

where OPINION_ID=@OPINIONID

 

go

----在第一個連接中執行

declare @bindToken varchar(255)

begin tran

exec sp_upd_opinion 'PreEntryIDUse',@bindToken output

select * from WBK_OPINION

select @@trancount  --事務數量為

select @bindToken

----在第二個連接中執行

---其中@binToken是由第一個連接執行完畢之后，而獲取的

begin tran

exec sp_upd_opinion2 'PreEntryIDUse',@bindToken 

select * from WBK_OPINION

select @@trancount  --事務數量為

 

---在第三個連接中執行以下語句，由于不在同一個事務之內，所以會被鎖定

update WBK_OPINION set OPINION_VALUE='true'

where OPINION_ID='PreEntryIDUse' 

 

 

rollback tran  ---回滾

         1. 你可以根據以上代碼，自行編碼相應的測試示例，通過Management studio分別使用三條連接來執行更新示例代碼，你會發現享有相同TOKEN的兩條連接會一同更新，而其獲取的@@TRANCOUNT系統變量也是一樣的。而不在同一事務中的其他連接則會被鎖住。@@TRANCOUNT也與前述的事務無關。

        2.提交不同的數據訪問路徑。如果兩條不同連接的SQL語句，因為搶相同索引而導致死鎖，可以考慮為不同的訪問語句建立不同的索引，通過索引提示強制讓兩條連接訪問各自的索引。或者是兩條不同的連接訪問相同的數據表，如果引用不同的索引，但各自的訪問順序正彼此交錯，形成死鎖，則可強制兩條連接使用相同的索引，以維護訪問先后秩序。

      不管如何，采用此類解決方式時，都要考慮額外的性能損耗，因為你通過索引提示強制了索引訪問，讓查詢優化程序不能憑借數據的特性使用最佳的索引。

五、發生死鎖后的處理

       通過設置SET DEADLOCK_PRIORITY LOW，讓不重要的事務自動放棄，并在這些連接執行的業務邏輯中，加上針對死鎖的錯誤處理。

      事實上，在非常復雜的高并發量的系統中，要完全預防死鎖，或者要知道什么樣的用戶在特殊的訪問次序中會發生死鎖，是非常困難的。所以應用程序應該對死鎖錯誤“1205”要有相應的處理，以完成原有的業務邏輯的處理或是善后清除處理。