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
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
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行数据
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
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;
会话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 Welcome to Lukes DB HOME。 Oracle OCM、Mysql OCP， 10年DBA工作经验，博客仅记录自己的一个学习过程，不代表完全准确，如有需要，欢迎转载。

注册时间： 2015-02-02

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员