日跟有位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引擎會將所有欲刪除的資料全部鎖住後再一起刪除，並不是找一筆就刪一筆(2021-12-01修正，修正說明在下面)因此當SQL要刪資料都得透過Scan整張Table才能找到資料時，那勢必會需要耗費較多的IO及時間，一旦整個Delete的時間拉長，相對鎖住的資源就會增加(如果發生鎖定升級的話就有可能Lock住整張Table)，因此也就較容易發生Blocking的狀況。
(2021-12-01說明補充)以下是朋友對於大量異動資料時，SQL對於資料鎖定的做法，可以知道SQL是逐筆鎖定並做異動，Commit後再Release所有鎖
一、對TABLE加 IX 鎖
二、對受影響PAGE加 IU 鎖
三、依條件逐筆掃瞄ROW，先加 U 鎖，若不符where條件即釋放U，若這個PAGE已全部掃完，則釋放page IU鎖
四、若符where條件即對受影響PAGE 改加 IX 鎖 ，再對ROW加X鎖，然後修改資料。
註：某筆改完後可以觀察到隨即釋放ROW的X鎖，若這個PAGE已全部掃完，則釋放page IX鎖，但這個釋放ROW及Page的鎖，均是mode=0-null所以不會真的釋放，對ROW及Page來說是無意義的，但聽說會被用於鎖定升級，只是機制尚不清楚。
五、下一筆（二、三、四循環）
六、commit後反向釋放各種鎖
因此建立合適索引來加快刪資料的動作是可以降低Blocking的情況。
我是ROCK
rockchang@mails.fju.edu.tw
