ITPub博客

首页 > 数据库 > SQL Server > Sqlserver的merge into或delete语句堵塞select语句,锁类型是LCK_M_IS

Sqlserver的merge into或delete语句堵塞select语句,锁类型是LCK_M_IS

原创 SQL Server 作者:lusklusklusk 时间:2021-11-16 19:54:49 0 删除 编辑

总结
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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30126024/viewspace-2842532/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Welcome to Lukes DB HOME。 Oracle OCM、Mysql OCP, 10年DBA工作经验,博客仅记录自己的一个学习过程,不代表完全准确,如有需要,欢迎转载。

注册时间:2015-02-02

  • 博文量
    471
  • 访问量
    905884