【茶包射手日記】只涉及單一資料表的Deadlock
16 | 52,558 |
在我原本狹隘的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的形成情境又有了新的認識,猶未晚矣~
Comments
# by Phoenix
反過來寫說不定也可以 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
# by Jeffrey
to Phoenix, 您提的兩種做法應都可行,感謝補充!
# by 過路人
請問大大~ 多個Process同時對單一資料表先查詢後更新但是每一個process處理的where條件不同這樣會形成DeadLock嗎? EX:select * from tableA where id=??? update tableA Set b=xxx where id=??? 謝謝
# by Jeffrey
to 過路人,個人淺見,文中範例是因為兩個Process都試著對PK_LockTab這個Index放Shared Lock及Exclusive Lock而造成Deadlock,鎖定範圍是Key Lock(只限Index中的一筆),故兩筆不同資料的鎖定對象應不同,不致發生文中所說的單一Table Deadlock。
# by Ted
版主你好 目前程式用C# 湊SQL字串傳給資料庫,程式執行的時候會彈出死結的例外,看了你的文章才知資料庫並不單純,內部還有一套運作機制。 SQL格式如下 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN TRANSACTION; update XXX set XXX COMMIT TRANSACTION; 單純只有更新資料(update)同一張資料表就會出現死結的例外,看了版主的文章、同事建議與網路文章,將update更改為 update XXX with (udplock,rowlock) set情況有稍微改善,但還是無法避免死結例外,請教版主有沒有更好的寫法? 可以完全避免死結例外。
# by Jeffrey
to Ted, 已知Transaction中UPDATE對象只有一個Table,其中UPDATE指令有幾筆?多方同時執行更新時,UPDATE的資料筆數有重疊嗎? 還需要更多的細節才好推想問題來源及對策。
# by Ted
抱歉現在才回應,之前工作壓力大寫的問題問得語無倫次,還以為版主不回應了。 每一個Transaction中 UPDATE的Table只有一個Table 每一個Transaction中只有一個UPDATE指令,欄位更新股市資訊如成交價、最高價、交易時間…..等。 之前舊版的程式更新的欄位比較少,新版程式多更新五個欄位,就冒出死結犧牲者的例外。 我這邊程式是開三個執行緒寫入資料庫,有可能出現三個執行緒寫同一個欄位的情況。 資料庫同事那邊也有一段SQL程式負責Table有更新的時候,複製整個Table的資料。之前有測試過不執行這段程式就不會有死結犧牲者例外彈出,但有問題還是要一起解決。 怪的是同樣的程式,使用另外一個資料庫都不會有死結犧牲者的例外出現,就只有這一個資料庫會出現死結犧牲者的例外。 這個問題很難,版主不在現場也無法了解全貌,只要版主願意指點一二就感激不盡了。
# by Jeffrey
to Ted, 單一UPDATE指令,只更新單一Table的多個欄位還出現Deadlock,的確是很詭異的狀況。我想到的調查方向是開啟SQL Profiler,再故意觸發Deadlock,期望能捕捉到如文章中的Deadlock事故現場完整軌跡,才能深入研究。
# by Lucy
請問一下,SQL預設的 ISOLATION LEVEL是Read Committed,如果使用(UPDLOCK)的這種方式解決deadlock是需要將level設成SERIALIZABLE嗎?還是內文這樣設只是為了要容易模擬deadlock場景? 謝謝!
# by Jeffrey
to Lucy, 文章裡的單資料表Deadlock的確與交易設成SERIALIAZABLE有關,而問題關鍵在於先放Shared Lock再升級成Exclusive Lock造成死鎖,改用Update Lock則可避開問題。在實際應用上,有時就會需要使用SERIALZABLE,確保「我用到的資料在我Commit前不准其他人讀寫」,更多鎖定層級細節可參考小朱的文章:https://dotblogs.com.tw/regionbbs/2011/04/17/sql_server_isolation_level
# by 棉花
黑大您好: 目前接手了一個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都不太熟 最後爬文爬到這篇,想請教黑大有什麼修改建議
# by 棉花
目前我先嘗試在update加上with (UPDLOCK),觀察看看還有沒有同樣的問題發生
# by Jeffrey
to 棉花,只有單一 Procedure 在執行,用 Cursor 跑 Update 卻發生 update B 與 update A 同時執行的狀況有些超乎想像。在我的認知裡,用 While 迴圈跑指令,前一個完成才會跑下一個,不應該出現前後 Update 平行執行的狀況(如有錯請指正)。 非常確定兩個 Update 動作都來自同一連線嗎? 會不會當時有其他程式在運作產生干擾? 如果不想費太多腦筋,可比照文章的做法,使用 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 並在 SELECT 時加上 (UPDLOCK),應可確保資料操作期間不受干擾。
# by 棉花
to: 黑大 tree這個table是一個暫存用table,在當下只會有一個連線、一隻procedure去對資料做update 全部資料處理結束後會匯出成報表,並將暫存table的資料清空 我在select上加上UPDLOCK時還是有同樣的問題 但將TRANSACTION ISOLATION LEVEL由SNAPSHOT改為READ COMMITTED後,出問題的次數大幅度下降 找到幾篇文章後,我猜可能跟SNAPSHOT有關 目前朝向SNAPSHOT的運作機制研究中,也謝謝您回答 參考文章: https://dotblogs.com.tw/rockchang/2015/02/15/149491 https://dotblogs.com.tw/rockchang/2015/02/16/149494 http://abcg5.pixnet.net/blog/post/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
# by 小高
請教黑哥, 若更新動作無須參考原資料表的值,例如單純將某個欄位更新成0或1,這時候也需要鎖定嗎?若需要的話,建議怎麼鎖定?也是使用UPDLOCK?
# by Jeffrey
to 小高,沒有參考原值直接覆寫固定值的情境,我不覺得會出現 Deadlock,不需額外處理。