SQL Server鎖分區特性引發死結解析

標籤：des   style   blog   http   color   io   os   使用   ar   

鎖分區技術使得SQL Server可以更好地應對並發情形,但也有可能帶來負面影響,這裡通過執行個體為大家介紹,分析由於鎖分區造成的死結情形.

前段時間園友@JentleWang在我的部落格鎖分區提升並發,以及鎖等待執行個體中問及鎖分區的一些特性造成死結的問題,這類死結並不常見,我們在這裡仔細分析下.不瞭解鎖分區技術的朋友請先看下我的鎖分區那篇執行個體.

Code(執行測試指令碼時請注意執行順序,說明)

步驟1 建立測試資料

use tempdbgocreate table testdlk(id int identity(1,1) primary key,str1 char(3000))goinsert into testdlk(str1) select ‘aaa‘insert into testdlk(str1) select ‘bbb‘insert into testdlk(str1) select ‘ccc‘insert into testdlk(str1) select ‘ddd‘

步驟2 開啟 session 1 執行語句

--session 1 begin tran update testdlk set str1=‘ttt‘ where id=1---session id 55 this example---rollback tran ---manual after session 3 rollback session 1

步驟3 開啟session 2 執行語句

--session 2BEGIN TRANupdate testdlk set str1=‘abc‘ where id=2 ---update the content of id=2SELECT * FROM testdlk WITH(TABLOCKX)------ try to get X lock on the object testdlkrollback tran---session id 58 this example

步驟4 開啟session 3執行資料

--session 3BEGIN TRANupdate testdlk set str1=‘abc‘ where id=3-------update the content of id=3 SELECT * FROM testdlk WITH(TABLOCKX)--- try to get X lock on the object testdlkrollback tran---session id 59 this example

步驟5 建立指令碼的session中執行語句

select request_session_id,resource_lock_partition,resource_type,object_name(resource_associated_entity_id) as object_name,request_mode,request_status from sys.dm_tran_locks where resource_database_id=2 and resource_type=‘OBJECT‘select session_id,blocking_session_id,wait_type,resource_description from sys.dm_os_waiting_tasks where blocking_session_id is not null

步驟6 session 1中rollback

Rollback session 1--when session 1 rollback then session 3 deadlock

當session 1復原時,session2 session 3造成死結,session 3犧牲.

原因分析.

通過步驟四我們可以得到相應的會話的鎖,及相關等待情況1-1

                                                                圖1-1

 

可以看到session 1(圖中55)由於只是更新id=1的列,所以它會在key上加排它鎖(圖中未列出,感興趣朋友可以自行查看),而在Object testdlk中某個鎖分區中圖中為鎖分區1加上意向排它鎖.

Session 2(圖中58)由於更新了id=2的列,所以會在相應Key上加獨佔鎖定,並在某個鎖分區中加意向獨佔鎖定(IX),於此同時由於此事務下面查詢有表級TABLOCKX Hint,此時58會嘗試在表級上獨佔鎖定(X鎖).由於X鎖需要在所有鎖分區中獲得,此時58在鎖分區0中獲得X鎖,但由於鎖分區1中有55獲得了意向獨佔鎖定(IX),所以58在鎖分區1中嘗試擷取X鎖時狀態未Convert,被55阻塞.

Session 3(圖中59)由於更新了id=3的列,所以會在相應key上加獨佔鎖定,同時在某個鎖分區中加意向獨佔鎖定(IX),於此同時由於此事務下面查詢同樣有表級TABLOCKX Hint,這時59也會嘗試在表的所有分區中擷取X鎖.由於58已經獲得鎖分區0的X鎖,所以當59嘗試擷取鎖分區0的X鎖時,就會被58阻塞,狀態為Wait.

問題來了,當55復原時,其上面的鎖也將被釋放.此時58,59都試圖獲得表級的所有分區X鎖,而又同時在鎖分區中持有IX鎖,這時死結就不可避免了.

死結視圖1-2所示.

 

                                              圖1-2

 

問題解決

經過分析,可以看出是由於鎖分區的特性導致IX與不同spid中的X互斥導致,那如果能禁用鎖分區特性不就沒有在個別分區上的IX這回事兒了嗎.這裡介紹一個啟動標記 trace flag 1229,可以禁用鎖分區特性.我們可以通過組態管理員中添加啟動標記,也可以在command啟動時加上響應參數.應當注意當使用組態管理員時,我們應在啟動參數末尾配置”-T1229”,如果使用command,這時是t1229(大小寫區分)

這裡我用win中command啟用

Code

Net start mssqlserver /t1229

重新啟動後重複上述執行個體,死結就不在出現了.

注:此執行個體只為說明由於鎖分區造成的死結情形,實際生產中此類情形卻是罕見的,除非遇到這類情形並且沒有更好的規避方式,一般我們還是建議預設此特性.這對提升並發是很有協助的.

關於鎖分區特性.

通過微軟的線上文檔可以得知,只有當CPU的邏輯數大於等於16時,才會預設開啟此特性,而授權又是按照CPU收費的.問題來了,當CPU小於16我如果想利用此特性是否可以呢?這個再給大家介紹一個啟動跟蹤標記 trace flag 1228.當有兩個及以上邏輯CPU時就會啟動鎖分區特性.不過我們使用時清楚自己的使用情境,是否會因此TF得到好處.由於這是無官方文檔記錄的特性,使用應只針對特定需求,並應謹慎.

結語:SQL Server或是其他資料庫系統中任何一個特性的引入總會適應大多數情境,但也會伴隨著特定情境的弊端出現,清楚其所帶來的利弊併合理使用,使得SQL Server適應情境,我們也能適應SQL Server.

SQL Server鎖分區特性引發死結解析