批次刪除大量資料時應搭配合適索引來降低Blocking

日跟有位PASS好友聊到當他分批大量刪除資料時會有Blocking造成,因此他想在delete的時候加入rowlock hint來減少Blocking的發生。
他表示delete的where條件式並沒有索引可以用,我當下是建議可以建立合適索引後再來刪資料,資料刪得快相對也比較不會發生Blocking了。

我事後想想SQL的引擎在刪大量資料時到底是如何運作? 是找到一筆就砍一筆,還是全部找到後標記完再一次砍呢? 
如下圖所示,我先建立一張80萬筆的資料表。

 

然後我開啟一個交易,刪除id=100000的資料然後不關閉交易。

 

此時我用sp_lock看一下,session id=59的鎖住3個資源,如下圖紅色圈選處。

 

接下來我們就模擬大量刪除資料,我的作法是刪除id除以9後餘數是1的資料,這樣也會刪除id=100000的資料,因此就會被Blocking住了。

 

我用sp_who2來看的確會看見Session 53被 Session 59給Blocking了,如下圖所示。

 

下圖中可以看見Session 53鎖住的頁面最大值是1:823這頁。

 

此時我再開第三個Session來砍id=190000這一筆資料。

 

然後我結束剛剛哪筆Session 59的交易,也就是id=100000的那一筆交易。

 

此時我們再看一下Session 53的狀況,可以發現他改被另一個Session Blocking住了,而Session 53的鎖住資源最大值也由1:823這頁變成1:1229這頁(如下圖紅色圈選處)。

由上面簡易LAB可以發現SQL引擎會將所有欲刪除的資料全部鎖住後再一起刪除,並不是找一筆就刪一筆,因此當SQL要刪資料都得透過Scan整張Table才能找到資料時,那勢必會需要耗費較多的IO及時間,一旦整個Delete的時間拉長,相對鎖住的資源就會增加(如果發生鎖定升級的話就有可能Lock住整張Table),因此也就較容易發生Blocking的狀況。

因此建立合適索引來加快刪資料的動作是可以降低Blocking的情況。

我是ROCK

rockchang@mails.fju.edu.tw