时间戳

首页 > 解决方案 > 尽管我有“提交交易”声明，但未提交交易

sql - 尽管我有“提交交易”声明，但未提交交易

问题描述

我正在使用 SQL Azure 并尝试对大表进行批量条件删除，示例：

DECLARE
    @LargestKeyProcessed BIGINT =1,
    @NextBatchMax BIGINT,
    @msg varchar(max) ='';
 
WHILE (@LargestKeyProcessed <= 1000000)
BEGIN
 Begin Transaction
 SET @NextBatchMax = @LargestKeyProcessed + 50000;
  DELETE From mytable
  WHERE Id > @LargestKeyProcessed AND Id <= @NextBatchMax And some logic
  SET @LargestKeyProcessed = @NextBatchMax;
    set @msg=''+@LargestKeyProcessed;
  RAISERROR(@msg, 0, 1) WITH NOWAIT
  Commit Transaction
END

命令成功执行后，我关闭选项卡，但 SSMS 说有未提交的事务，尽管提交语句在每次迭代中。此外，数据库大小似乎保持不变。

我恳请您的支持来解释为什么会发生这种情况

非常感谢

标签： sqlsql-serverazure-sql-databasessms

解决方案

我想你可以尝试添加SET IMPLICIT_TRANSACTIONS OFF到 sql 中。如下，看看是否解决了您的问题。

DECLARE
    @LargestKeyProcessed BIGINT =1,
    @NextBatchMax BIGINT,
    @msg varchar(max) ='';
 
WHILE (@LargestKeyProcessed <= 1000000)
BEGIN
 SET IMPLICIT_TRANSACTIONS OFF  
 Begin Transaction
 SET @NextBatchMax = @LargestKeyProcessed + 50000;
  DELETE From mytable
  WHERE Id > @LargestKeyProcessed AND Id <= @NextBatchMax And some logic
  SET @LargestKeyProcessed = @NextBatchMax;
    set @msg=''+@LargestKeyProcessed;
  RAISERROR(@msg, 0, 1) WITH NOWAIT
  Commit Transaction
END

推荐阅读