手機版
你好,游客 登錄 注冊
背景:
閱讀新聞

記錄SQL Server中一次無法重現的死鎖

[日期:2019-09-24] 來源:cnblogs.com/wy123  作者:wy123 [字體: ]

平時遇到的死鎖,絕大多數情況下,都可以根據當時的場景進行重現,然后具體分析解決,下文這個死鎖幾次嘗試測試模擬,均沒有成功重現
在嘗試用profile跟蹤加鎖順序之后,大概可以推斷到當時死鎖發生的原因,但是仍有無法重現,為了避免不必要的麻煩,這里用測試表的方式,盡可能還原嘗試的場景,來做進一步的分析。
死鎖發生的場景如下(暫不論表設計合不合理,索引合不合理,sql語句寫法合不合理,分析死鎖是主要目的,解決死鎖是另外一回事)

目標表為TestDeadLock,大概結構如下
1,TestDeadLock表為堆表(有自增列的主鍵,但是主鍵nonclustered),col2.col3上的索引分別是idx_col2,idx_col3,Col2無重復,Col3上重復值較多,表數據量不會很多,幾千行或者幾萬行的樣子,
2,存在并發按照多個不同的Col2字段刪除的情況(delete from TestDeadLock where col2 in (x,y,z);)

create table TestDeadLock
(
    id int identity(1,1) primary key nonclustered,
    col2 varchar(30),
    col3 varchar(30),
    col4 varchar(30)
)

1,session1 執行delete from TableA where col2 in (x,y,z);
2,session2 執行delete from TableA where col2 in (l,m,n);
其中,刪除的目標列條件 in (x,y,z);與in (l,m,n);中的數據在Col2這個字段的值上無重復,無交叉,但是多個Col2條件上對應的Col3這個字段值是一樣的

session1與session2發生死鎖,xml_deadlock_report顯示session1是持有col2上的key級別的U鎖,等待col3上key級別的U鎖,session1是持有col3上的key級別的U鎖,等待col2上key級別的U鎖
如下是xml_deadlock_report鎖等待的信息,敏感信息用XXXXXXXXXX和YYYYYYYYYYYYY替代了,其中XXXXXXXXXX類似如上的idx_col3 索引Id,YYYYYYYYYYYY類似于如上的idx_col2索引Id

<resource-list>
    <keylock hobtid="XXXXXXXXXXXXXX" dbid="6" objectname="" indexname="" id="lock12fe62f80" mode="U" associatedObjectId="XXXXXXXXXXXXXX">
    <owner-list>
        <owner id="process——2" mode="U" />
    </owner-list>
    <waiter-list>
        <waiter id="process——1" mode="U" requestType="wait" />
    </waiter-list>
    </keylock>
    <keylock hobtid="YYYYYYYYYYYYY" dbid="6" objectname="" indexname="" id="lock126403100" mode="U" associatedObjectId="YYYYYYYYYYYYY">
    <owner-list>
        <owner id="process——1" mode="U" />
    </owner-list>
    <waiter-list>
        <waiter id="proces——2" mode="U" requestType="wait" />
    </waiter-list>
    </keylock>
</resource-list>

先說我自己的理解:
理論上說,兩個delete的session都會走Col2上的索引,兩個語句對于其目標數據的加鎖順序是一致的,不會出現死鎖的情況,
當然只是臆測,因為sql語句沒有加任何鎖提示,數據量小的時候,任何一種執行計劃都是有可能的。
但是僅僅從死鎖的語句,是無法拿到當時的執行計劃的,也就無法證實當死鎖發生的時候,雙方用的哪一種執行計劃。

構造測試表以及測試數據,其中:對于col3,盡管重復值非常多,仍然有一個索引(再次說明,這里暫拋開索引合不合理,語句合不合理這一說)

create table TestDeadLock
(
    id int identity(1,1) primary key nonclustered,
    col2 varchar(30),
    col3 varchar(30),
    col4 varchar(30)
)

create index idx_col2 on TestDeadLock(col2)
create index idx_col3 on TestDeadLock(col3)


declare @i int = 0
while @i<200000
begin
    insert into TestDeadLock values (concat('X0000000000',@i),cast(rand()*10 as int),'test')
    set @i = @i+1
end

測試表的索引對象Id

以delete from TestDeadLock where col2 in ( 'X00000000003','X000000000020')為例,這里先拿到其偽列Id

理論上,這句sql的執行,會走col2 上的索引進行查找,然后再進行刪除(delete本來就是先查找再刪除的過程),測試case也是預期地,走了col2 上的索引

查看鎖的申請與釋放過程

可以發現

1,刪除多條數據的時候,是一條一條加鎖然后刪除的

2,對于第一條記錄(32a1976b7833),也即col2 = 'X000000000089'的記錄,刪除的加鎖過程如下

  2.1 對(32a1976b7833),即col2 = 'X000000000089'的記錄記錄所在的page加共享排它鎖,對(32a1976b7833)記錄所在的行加U鎖

  2.2  對(32a1976b7833)記錄對應的主鍵所在的page加IX鎖,主鍵行加RID級別的U鎖

  2.3 對2.2 對(32a1976b7833)記錄對應的RID所在的page加IX鎖,主鍵行加RID級別的X鎖

  2.4 對2.2 對(32a1976b7833)記錄對應的主鍵所在的page加IX鎖,主鍵行加RID級別的U鎖

    2.5 對2.2 對(32a1976b7833)記錄對應的主鍵所在的page加IX鎖,主鍵行加KEY級別的X鎖

  2.6 釋放KEY與Page級別的X鎖和IX鎖

  2.7 重復2.1對(32a1976b7833)記錄所在的page加共享排它鎖,對(32a1976b7833)記錄所在的行加U鎖

  2.8 釋放(32a1976b7833)以及其所在page的X鎖和IX鎖

  2.9 對(d12bea8cbd9f)這個記錄,也即Col3字段上的索引依次加page上的IX鎖,key上的X鎖

  2.10(反向)依次釋放Col3 key上的X鎖,page上的IX鎖

  2.11 依次釋放上述其他的鎖

簡而言之,遵循兩段鎖協議(2PL),以行為基礎,加鎖與釋放所過程獨立,互不干擾。
因為走了Col2上的索引,這個過程大概是:先申請Col2上的U鎖,找到其RID和主鍵索引,然后依次刪除這RID和主鍵索引,然后再刪除Col2上索引的key,最后刪除對應的Col3上的索引key
最后釋放所有上面申請的鎖

上述是刪除多條數據其中一條數據的加鎖以及釋放鎖的過程,很清楚的看到,Col2上的U鎖只是在第一步申請的,Col3上根本沒有申請U鎖,而是直接申請的X鎖,然后刪除,然后再釋放
因為死鎖雙方的數據是互不交叉的,U鎖又是單獨只在Col2索引上申請的,那么為什么會出現死鎖雙方相互等待Col2與Col3上的U鎖,從而造成死鎖?
之前沒有想明白,是因為就存在一種想當然的推斷過程,兩個session的刪除語句都走col2上的索引,當然不會出現兩個session相互申請Col2與Col3上的U鎖
一旦存在Session1走Col2上的索引,Session2走Col3上的索引,才有可能出現ession相互申請Col2與Col3上的U鎖的可能性

對于Session1和Session2

1,session1 執行delete from TableA where col2 in (x,y,z);
2,session2 執行delete from TableA where col2 in (l,m,n);

理論上說,或者相當然地說,都會走col2上的索引,但是不能完全肯定一定都會走Col2上的索引,或許有可能走全表掃描,或者有可能走Col3上的索引掃描
比如如下的強制索引提示,走任何一種執行計劃,都是可能的,盡管可能會在主觀上認為某些執行計劃是不好的,但是這個語句在沒有任何索引提示的時候,不能臆測一定會走col2上的索引
否則不會出現session雙方持有了Col2索引上的U索引,申請Col3索引上的U鎖,否則這個死鎖就解釋不通。

實際上,上述死鎖,有可能是一個執行計劃走了Col2上的索引查找方式刪除,需要先在Col2索引上加U鎖
一個是走了走了全表掃描造成的,類似于delete t from TestDeadLock t with(index(0)) where Col2 in ( 'X000000000089','X000000000095')的執行計劃
后者先在Col3上加U鎖,然后找到其對應的RID,主鍵索引,Col2上的索引,依次加U鎖,加X索引,這樣才潛在死鎖的可能性

寫不下去了,鉆研SQL Server的人實在太少了,如果是MySQL,一定會有大神回去做深入的分析,這個case筆者多次嘗試重現它,包括使用Python多線程的方式模擬當時的場景,都無疾而終,無法重現
發生死鎖的這個真實情況下的場景,也不會經常出現,筆者也只是偶爾撈到死鎖的xml_deadlock_report嘗試作分析,均無果。

這個死鎖,是筆者遇到的不多的無法重現或者模擬出來的死鎖,但愿有高手感興趣的話,進一步做分析嘗試,即便是推翻筆者猜測的結論,得出更有說服力的結果。

以上。

linux
相關資訊       SQL Server死鎖 
本文評論   查看全部評論 (0)
表情: 表情 姓名: 字數

       

評論聲明
  • 尊重網上道德,遵守中華人民共和國的各項有關法律法規
  • 承擔一切因您的行為而直接或間接導致的民事或刑事法律責任
  • 本站管理人員有權保留或刪除其管轄留言中的任意內容
  • 本站有權在網站內轉載或引用您的評論
  • 參與本評論即表明您已經閱讀并接受上述條款
彩票投注骗局