Sqlserver的merge into或delete語句堵塞select語句，鎖型別是LCK_M_IS

總結
sqlserver遇到delete刪除大量資料時，千萬不能直接刪除，刪除過程會堵塞不加with (nolock)的select語句，鎖型別是LCK_M_IS，delete刪除過程中，雖然加with (nolock)不斷查詢該表時看到該表資料是減少的，但是一旦cancel取消delete會話，這個cancel取消動作很漫長(也就是回滾會很漫長，之前刪除了多少行就需要回滾多少行)，且cancel取消delete的操作完成後，還需要手工commit否則不加with (nolock)的select還是會堵塞，且關閉這個已經cancel的SSMS視窗會提示There are uncommitted transactions. Do you wish to commit these before closing the window?，commit後再查詢該表，發現表的資料和刪除之前一樣，而 不是大家想象中的sqlserver刪除一條資料後自動提交。所以sqlserver 刪除大量資料時，最好批次刪除，刪除5000行提交一次，這樣就算後面cancel取消delete語句，也可以很快cancel取消delete會話(也就是回滾很快，因為只需要回滾近5000條資料)



案例1，說明merge into會堵塞沒有加with (nolock)的select
會話1
MERGE INTO Corporation AS TARGET USING (select left(Symbol_Bloomberg,50) AS Symbol_Bloomberg,left(LocalCode,50)AS LOCALCODE,
left(Symbol_Thomson,50)AS Symbol_Thomson,mxid,osid FROM dbo.TRKD_WON_Mapping
where osid > 3000 AND MXID IS NOT NULL) AS SOURCE ON  TARGET.Corposid = SOURCE.osid
WHEN MATCHED THEN   
UPDATE SET TARGET.LOCALSYMBOL  = SOURCE.LOCALCODE, TARGET.THOMSONSYMBOL= SOURCE.Symbol_Thomson,TARGET.MXID= SOURCE.MXID,
TARGET.ROWSTATUS= 'U';     

MERGE INTO Corporation AS TARGET USING (select left(Symbol_Bloomberg,50) AS Symbol_Bloomberg,left(LocalCode,50)AS LOCALCODE,
left(Symbol_Thomson,50)AS Symbol_Thomson,mxid,osid FROM dbo.TRKD_WON_Mapping where osid < 3000000 AND MXID IS NOT NULL) AS SOURCE
ON  TARGET.Corposid = SOURCE.osid WHEN MATCHED THEN  UPDATE SET TARGET.THOMSONSYMBOL= SOURCE.Symbol_Thomson, TARGET.MXID = SOURCE.MXID,
TARGET.ROWSTATUS= 'U';

會話2，被會話1鎖住，等待LCK_M_IS鎖
select top 1 * from corporation




案例2，說明delete會堵塞沒有加with (nolock)的select
會話1
delete from marketinginterfacedb.dbo.C3_CIRC_UPDATES   where CREATED_DT < '10/30/2021' and PROCESSED_TIMESTAMP is not null

會話2，被會話1鎖住，C3CircUpdateID是主鍵，等待LCK_M_IS鎖
select max(C3CircUpdateID) from marketinginterfacedb.dbo.C3_CIRC_UPDATES

會話3，被會話1鎖住，等待LCK_M_IS鎖
select count(*) from marketinginterfacedb.dbo.C3_CIRC_UPDATES

會話4，正常執行，沒有被會話1給鎖住
select max(C3CircUpdateID) from marketinginterfacedb.dbo.C3_CIRC_UPDATES with (nolock)

會話4，正常執行，沒有被會話1給鎖住
select count(*) from marketinginterfacedb.dbo.C3_CIRC_UPDATES with (nolock)




案例2，說明sqlserver的delete大量刪除資料時不是真正的刪除一行就自動提交了
案例2，如果會話1不是kill而是cancel取消，等cancel取消完畢後，則第6步不加 with (nolock)的select還是被堵塞，這個時候關閉這個已經cancel的SSMS視窗會提示There are uncommitted transactions. Do you wish to commit these before closing the window?，只有commit或關掉了這個cancel的cake，第六步的不加 with (nolock)的select才能正常跑，且count(*)結果還是表最初的值

C3_CIRC_UPDATES表原來292033754行資料
會話1
delete from marketinginterfacedb.dbo.C3_CIRC_UPDATES  where CREATED_DT < '10/30/2021' and PROCESSED_TIMESTAMP is not null

會話2，看起來在delete過程中，表的資料在不斷減少
select count(*) from dbo.C3_CIRC_UPDATES with (nolock) where CREATED_DT < '10/30/2021' and PROCESSED_TIMESTAMP is not null
waitfor delay '00:00:30'
select count(*) from dbo.C3_CIRC_UPDATES with (nolock) where CREATED_DT < '10/30/2021' and PROCESSED_TIMESTAMP is not null
waitfor delay '00:00:30'
select count(*) from dbo.C3_CIRC_UPDATES with (nolock) where CREATED_DT < '10/30/2021' and PROCESSED_TIMESTAMP is not null
waitfor delay '00:00:30'
select count(*) from dbo.C3_CIRC_UPDATES with (nolock) where CREATED_DT < '10/30/2021' and PROCESSED_TIMESTAMP is not null
會話2可以正常執行，四個count(*)顯示如下結果
257243065
251250136
244925679
238778815

會話1執行20分鐘，kill會話1

會話3，會話1 剛開始處於cancel過程中，結果232428365行，看起來在delete過程中，表的資料在不斷減少
select count(*) from dbo.C3_CIRC_UPDATES with (nolock)  where CREATED_DT < '10/30/2021' and PROCESSED_TIMESTAMP is not null
232428365

會話4,會話1已經kill了30分鐘還在cancel過程中，結果292033754行，說明被刪除的行並沒有被自動提交
select count(*) from dbo.C3_CIRC_UPDATES with (nolock)  where CREATED_DT < '10/30/2021' and PROCESSED_TIMESTAMP is not null
292033754

會話5,會話1 kill過程中
select count(*) from dbo.C3_CIRC_UPDATES where CREATED_DT < '10/30/2021' and PROCESSED_TIMESTAMP is not null
--被回滾的會話1堵塞，等待LCK_M_IS鎖

會話6,會話1 kill完畢，會話1回滾耗時30分鐘，結果292033754行
select count(*) from dbo.C3_CIRC_UPDATES where CREATED_DT < '10/30/2021' and PROCESSED_TIMESTAMP is not null
292033754




案例3
C3_CIRC_UPDATES表原來292033754行資料

會話1
USE [marketinginterfacedb]
GO
DECLARE @r INT;
Print 'C3_CIRC_UPDATES:'
SET @r = 1;
WHILE @r > 0
BEGIN
  BEGIN TRANSACTION;
  DELETE TOP (5000) from marketinginterfacedb.dbo.C3_CIRC_UPDATES   where CREATED_DT < '10/30/2021' and PROCESSED_TIMESTAMP is not null
  SET @r = @@ROWCOUNT;
  Print CAST(@r  AS VARCHAR(10)) + ' rows deleted'
  COMMIT TRANSACTION;
END

會話2，不堵塞，可以正常執行
select max(C3CircUpdateID) from marketinginterfacedb.dbo.C3_CIRC_UPDATES

會話3，不堵塞，可以正常執行，直接結果291543754
select count(*) from marketinginterfacedb.dbo.C3_CIRC_UPDATES
291543754

cancel取消會話1，發現會話1可以快速被cancel完畢，不會等待很久

會話4，會話1回滾完畢，結果291138754，比初始值292033754小了
select count(*) from dbo.C3_CIRC_UPDATES with (nolock)  where CREATED_DT < '10/30/2021' and PROCESSED_TIMESTAMP is not null
291138754