【茶包射手日記】只涉及單一資料表的Deadlock

在我原本狹隘的SQL知識裡,Deadlock發生情境需要兩個Process A與B跟兩個Table X與Y搭配演出: A鎖定住X想更新Y,B鎖定Y等著要更新X,產生無解的僵持,再由SQL二者擇一選為犠牲者,令其失敗來成立另一個Process。

直到最近處理一起Deadlock案例,才又長了見識。一個處理流水序號的Stored Procedure,讀取與更新對象只限同一Table,並不構成井底之蛙心中的Deadlock成立要件: 兩個鎖定對象、相反的讀取/更新順序,但Deadlock卻硬生生地發生了!

試著用以下方式模擬重現問題。以下的SQL指令,會在一個Transaction中先讀取LockLab的特定計數欄位,再將其更新加1,為確保不會有Phantom Read及Non-Repeatable Read,隔離層級拉高到Serializable(關於隔離層級: 小朱有篇鎖定使用的藝術 (Part 2) - 隔離層次 (Isolation Level)可參考)。為故意製造Deadlock,指令中再加入WAITFOR拖長Transaction的時間到10秒,同時開兩個SSMS執行,就能輕易讓二者強碰相咬。(真實案例因執行時間很短,數千到上萬次才會發生一次Deadlock)

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN
DECLARE @i INT;
SELECT @i = Seq FROM LockLab WHERE Code='JEFF' AND YearMon='201212'
SET @i = @i + 1;
WAITFOR DELAY '00:00:10'
UPDATE LockLab SET Seq = @i WHERE Code = 'JEFF' AND YearMon='201212'
--其他程式邏輯(略)
COMMIT TRAN

執行結果如下圖所示,同時執行兩份SSMS,其中一個成功,另一個因Deadlock被選為犠牲者:

用SQL Profiler調出事故現場軌跡圖。兩個Process都放了Shared Lock(S)在PK_LockLab上,當要更新再對PK_LockLab放上Exclusive Lock(X)時形成對峙,造成Deadlock!!

原本腦中死板板地只有兩個Process加兩個Table的典型Deadlock案例,百思不得其解,兩組完全相同的SQL指令對同一個資料表先讀後寫,順序完全一致,怎麼會冒出Deadlock? 思索好久才恍然大悟,是鎖定升級造成的!! 初期的SELECT動作,因宣告了SERIALIZABLE隔離層級,SELECT時對PK_LockLab放上了Shared Lock;之後要UPDATE時,再升級成Exlusive Lock,但此時另一個Process已放了Shared Lock,故要等待對方釋放Lock才能繼續。然而不久之後,對方也想放Exclusive Lock,卻卡在前Process的Shared Lock。碰! Deadlock!!!

想通了這點,問題其實不難解。在此情境下,我們可在SELECT時透過UPDLOCK提示要求SQL直接使用Update Lock(U),避開先S後X的兩階段鎖定過程,便能排除形成Deadlock的條件。將T-SQL改成以下寫法,就能避免Deadlock囉! (但第二個執行的Process需等待第一個Process執行完畢才能SELECT成功,故總共要20秒才能執行完畢,合理。)

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN
DECLARE @i INT;
SELECT @i = Seq FROM LockLab (UPDLOCK) WHERE Code='JEFF' AND YearMon='201212'
SET @i = @i + 1;
WAITFOR DELAY '00:00:10'
UPDATE LockLab SET Seq = @i WHERE Code = 'JEFF' AND YearMon='201212'
--其他程式邏輯(略)
COMMIT TRAN

在MSDN Lock Modes說明中,也提到了這點:

更新 (U)鎖定模式
用於可更新的資源上。防止當多個工作階段正在讀取、鎖定及後來可能更新資源時發生常見的死結

回頭想想,過去咬定Deadlock"一個巴掌拍不響"的迷思,恐怕曾導致自己在處理Deadlock議題時多次誤入歧途而不自知,難免心頭一驚。但至少今天起對Deadlock的形成情境又有了新的認識,猶未晚矣~

歡迎推文分享:
Published 20 December 2012 03:40 PM 由 Jeffrey
Filed under: , , ,
Views: 35,878



意見

# Phoenix said on 20 December, 2012 10:22 PM

反過來寫說不定也可以

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRAN

DECLARE @i INT;

UPDATE LockLab SET Seq = Seq + 1 WHERE Code = 'JEFF' AND YearMon='201212'

WAITFOR DELAY '00:00:10'

SELECT @i = Seq FROM LockLab WHERE Code='JEFF' AND YearMon='201212'

--其他程式邏輯(略)

COMMIT TRAN

利用UPDATE OUTPUT

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRAN

UPDATE LockLab SET Seq = Seq + 1

OUTPUT inserted.Seq

WHERE Code = 'JEFF' AND YearMon='201212'

--其他程式邏輯(略)

COMMIT TRAN

# Jeffrey said on 21 December, 2012 03:38 AM

to Phoenix, 您提的兩種做法應都可行,感謝補充!

# 過路人 said on 25 April, 2014 02:09 AM

請問大大~

多個Process同時對單一資料表先查詢後更新但是每一個process處理的where條件不同這樣會形成DeadLock嗎?

EX:select * from tableA where id=???

    update tableA Set b=xxx where id=???

謝謝

# Jeffrey said on 26 April, 2014 12:59 AM

to 過路人,個人淺見,文中範例是因為兩個Process都試著對PK_LockTab這個Index放Shared Lock及Exclusive Lock而造成Deadlock,鎖定範圍是Key Lock(只限Index中的一筆),故兩筆不同資料的鎖定對象應不同,不致發生文中所說的單一Table Deadlock。

# Ted said on 18 May, 2016 10:53 PM

版主你好

目前程式用C# 湊SQL字串傳給資料庫,程式執行的時候會彈出死結的例外,看了你的文章才知資料庫並不單純,內部還有一套運作機制。

SQL格式如下

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION; update  XXX  set  XXX

COMMIT TRANSACTION;

單純只有更新資料(update)同一張資料表就會出現死結的例外,看了版主的文章、同事建議與網路文章,將update更改為 update XXX with (udplock,rowlock) set情況有稍微改善,但還是無法避免死結例外,請教版主有沒有更好的寫法? 可以完全避免死結例外。

# Jeffrey said on 20 May, 2016 06:43 PM

to Ted, 已知Transaction中UPDATE對象只有一個Table,其中UPDATE指令有幾筆?多方同時執行更新時,UPDATE的資料筆數有重疊嗎?

還需要更多的細節才好推想問題來源及對策。

# Ted said on 25 May, 2016 02:03 AM

抱歉現在才回應,之前工作壓力大寫的問題問得語無倫次,還以為版主不回應了。

每一個Transaction中 UPDATE的Table只有一個Table

每一個Transaction中只有一個UPDATE指令,欄位更新股市資訊如成交價、最高價、交易時間…..等。

之前舊版的程式更新的欄位比較少,新版程式多更新五個欄位,就冒出死結犧牲者的例外。

我這邊程式是開三個執行緒寫入資料庫,有可能出現三個執行緒寫同一個欄位的情況。

資料庫同事那邊也有一段SQL程式負責Table有更新的時候,複製整個Table的資料。之前有測試過不執行這段程式就不會有死結犧牲者例外彈出,但有問題還是要一起解決。

怪的是同樣的程式,使用另外一個資料庫都不會有死結犧牲者的例外出現,就只有這一個資料庫會出現死結犧牲者的例外。

這個問題很難,版主不在現場也無法了解全貌,只要版主願意指點一二就感激不盡了。

# Jeffrey said on 25 May, 2016 10:46 AM

to Ted, 單一UPDATE指令,只更新單一Table的多個欄位還出現Deadlock,的確是很詭異的狀況。我想到的調查方向是開啟SQL Profiler,再故意觸發Deadlock,期望能捕捉到如文章中的Deadlock事故現場完整軌跡,才能深入研究。

# Lucy said on 03 June, 2016 03:41 AM

請問一下,SQL預設的 ISOLATION LEVEL是Read Committed,如果使用(UPDLOCK)的這種方式解決deadlock是需要將level設成SERIALIZABLE嗎?還是內文這樣設只是為了要容易模擬deadlock場景? 謝謝!

# Jeffrey said on 03 June, 2016 09:11 AM

to Lucy, 文章裡的單資料表Deadlock的確與交易設成SERIALIAZABLE有關,而問題關鍵在於先放Shared Lock再升級成Exclusive Lock造成死鎖,改用Update Lock則可避開問題。在實際應用上,有時就會需要使用SERIALZABLE,確保「我用到的資料在我Commit前不准其他人讀寫」,更多鎖定層級細節可參考小朱的文章:dotblogs.com.tw/.../sql_server_isolation_level

# 棉花 said on 16 November, 2017 08:07 PM

黑大您好:

目前接手了一個procedure,裡面有一段是做tree的彙總計算

裡面的結構長這樣:

           A

         /   \

       B     C

     /    \

   D      E

id  parent_id     value

--  -----------    -------

A   NULL               0

B   A                     0

C   A                 100

D   B                 200

E   B                 300

procedure中使用cursor做彙總計算

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

begin tran

declare c_sum cursor local fast_forward for

select id, parent_id, value

 from tree

order by id desc;

fetch next from c_sum into @m_id, @m_parent_id, @m_value;

while @@FETCH_STAUS = 0 begin

   update tree

        set  value =  value + @m_value

     where id = @m_parent_id;

   fetch next from c_sum into @m_id, @m_parent_id, @m_value;

end;

COMMIT;

理論上計算完的結果要長這樣:

id  parent_id     value

--  -----------    -------

A   NULL            600

B   A                 500

C   A                 100

D   B                 200

E   B                 300

但實際上線到客戶那邊時,有時卻出現這樣的結果:

id  parent_id     value

--  -----------    -------

A   NULL            100

B   A                 500

C   A                 100

D   B                 200

E   B                 300

原本以為是procedure跑到一半就跳掉,但用SQL Profiler看卻發現procedure有正確執行

寫入log後發現update B  與 update A幾乎在同時執行

依據log的結果初步推測是update B時,value尚未寫入,而此時cursor已經跑到B那一筆,最後抓到舊的value

我想要在update結束前把該筆資料lock住,等到update結束後才能select

但不知道lock該下在cursor上還是update上,以及該用哪種lock

去網路上爬文都找不太到相關的案例,而同事對SQL Server都不太熟

最後爬文爬到這篇,想請教黑大有什麼修改建議

# 棉花 said on 16 November, 2017 08:16 PM

目前我先嘗試在update加上with (UPDLOCK),觀察看看還有沒有同樣的問題發生

# Jeffrey said on 17 November, 2017 04:38 AM

to 棉花,只有單一 Procedure 在執行,用 Cursor 跑 Update 卻發生 update B  與 update A 同時執行的狀況有些超乎想像。在我的認知裡,用 While 迴圈跑指令,前一個完成才會跑下一個,不應該出現前後 Update 平行執行的狀況(如有錯請指正)。

非常確定兩個 Update 動作都來自同一連線嗎? 會不會當時有其他程式在運作產生干擾?

如果不想費太多腦筋,可比照文章的做法,使用 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 並在 SELECT 時加上 (UPDLOCK),應可確保資料操作期間不受干擾。

# 棉花 said on 17 November, 2017 04:53 AM

to: 黑大

tree這個table是一個暫存用table,在當下只會有一個連線、一隻procedure去對資料做update

全部資料處理結束後會匯出成報表,並將暫存table的資料清空

我在select上加上UPDLOCK時還是有同樣的問題

但將TRANSACTION ISOLATION LEVEL由SNAPSHOT改為READ COMMITTED後,出問題的次數大幅度下降

找到幾篇文章後,我猜可能跟SNAPSHOT有關

目前朝向SNAPSHOT的運作機制研究中,也謝謝您回答

參考文章:

dotblogs.com.tw/.../149491

dotblogs.com.tw/.../149494

abcg5.pixnet.net/.../115713535-%E8%B3%87%E6%96%99%E5%88%97%E7%89%88%E6%9C%AC%E6%8E%A7%E5%88%B6---%E9%9A%94%E9%9B%A2%E5%B1%A4%E7%B4%9A

你的看法呢?

(必要的) 
(必要的) 
(選擇性的)
(必要的) 
(提醒: 因快取機制,您的留言幾分鐘後才會顯示在網站,請耐心稍候)

5 + 3 =

搜尋

Go

<December 2012>
SunMonTueWedThuFriSat
2526272829301
2345678
9101112131415
16171819202122
23242526272829
303112345
 
RSS
創用 CC 授權條款
【廣告】
twMVC
最新回應

Tags 分類檢視
關於作者

一個醉心技術又酷愛分享的Coding魔人,十年的IT職場生涯,寫過系統、管過專案, 也帶過團隊,最後還是無怨無悔地選擇了技術鑽研這條路,近年來則以做一個"有為的中年人"自許。

文章典藏
其他功能

這個部落格


Syndication