[大数据]Sqlserver的merge into或delete语句堵塞了不加with (nolock)的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