主页 / dba / 问题 / 254152
Accepted
pmdci
Asked: 2019-11-27 04:11:04 +0800 CST

回滚截断表

  • 772

我有一个执行以下操作的存储过程：

BEGIN TRANSACTION

    -- Code to delete updated records from production (dbo) table
    DELETE FROM [dbo].[factMyTable]
    WHERE exists (SELECT *
        FROM [RAW].[MyTable]
        WHERE [RAW].[MyTable].[refno] = [dbo].[factMyTable].[refno]
        AND [RAW].[MyTable].[modification_dttm] >= [dbo].[factMyTable].[modification_dttm]
        )

    -- Code to perform the append of incremental records 
    INSERT INTO [dbo].[factMyTable]
    SELECT
         [refno]
        ,[field1]
        ,[field2]
        ,[field3]
        ,[FieldN]
        ,[modification_dttm]
    FROM [RAW].[MyTable]

-- Truncate stage table and get ready for next load
TRUNCATE TABLE [RAW].[MyTable]

COMMIT TRANSACTION

正如您在上面看到的，我有一个包含在 BEGIN/COMMIT 事务块中的截断命令。但是，在插入命令中执行此存储过程时出现错误，其中设置为 NOT NULL 的字段正在接收 NULL 值。因此：

  1. 将 RAW 表中的记录插入 dbo 表的操作被回滚；但
  2. RAW 表的截断没有回滚。

这个想法是，如果插入数据时出现错误，则不应发生截断。

根据这篇文章，我们可以回滚 truncate 命令，但也许我的存储过程没有正确编写脚本。也许有一种更直接的方法可以确保只有在插入没有返回错误时才会发生截断？我该怎么办？

stored-procedures t-sql

2 个回答

  1. Best Answer
    1. 将 RAW 表中的记录插入 dbo 表的操作被回滚；但
    2. RAW 表的截断没有回滚。

    不，根本没有rollback，这是复制品。

    这xact_abort off是您default设置的选项，我创建了 2 个表，然后我打开事务结束执行 2inserts个，其中一个（第二个）失败，我从两个表中添加select @@trancount，select以便您可以更好地查看发生了什么：

    --set xact_abort on

if object_id('dbo.t1') is not null drop table dbo.t1;
if object_id('dbo.t2') is not null drop table dbo.t2;
go


create table dbo.t1 (col1 int);
insert into dbo.t1 values(1), (null);

create table dbo.t2 (col1 int not null);
go

begin transaction

    insert into dbo.t2
    values(-1);

    insert into dbo.t2
    select col1
    from dbo.t1;

    select @@trancount as [@@trancount before truncate];

    truncate table dbo.t1;

commit transaction;

select @@trancount as [@@trancount after commit];

select *
from dbo.t1;

select *
from dbo.t2;

    在此处输入图像描述

    如您所见，没有rollback制作，只有您的commit. 您将 (-1) 插入dbo.t2其中，并且该行永久存在。这是因为错误

    消息 515，级别 16，状态 2，第 18 行无法将值 NULL 插入列 'col1'，表 'dbo.t2'；列不允许空值。

    只是语句终止。第二条语句失败，因此没有插入任何行，但是 (-1) 的插入没有回滚，并且正如您在错误之后看到的那样，您transaction仍然是open。commit提交 -1 和 table的插入是你的truncation。

    现在第二个测试：取消注释set xact_abort on，这只会使statment terminating错误成为batch aborting，所有的statements内部事务都将rolled back是，一旦错误发生，执行将被中断。

    所以t1table 永远不会被截断， (-1) in 的插入t2将是rolled back.

    现在应该如何编写代码：

    set xact_abort on;

if object_id('dbo.t1') is not null drop table dbo.t1;
if object_id('dbo.t2') is not null drop table dbo.t2;
go

create table dbo.t1 (col1 int);
insert into dbo.t1 values(1), (null);

create table dbo.t2 (col1 int not null);
go

begin try
begin transaction

    insert into dbo.t2
    values(-1);

    insert into dbo.t2
    select col1
    from dbo.t1;

    select @@trancount as [@@trancount before truncate];

    truncate table dbo.t1;

commit transaction;
end try

begin catch
    select @@trancount as [@@trancount in catch before rollback];
    if @@rowcount > 0 rollback;
    throw;
end catch;

    您的代码应始终设置 xact_abort 并且它应该有try..catch块。

    你应该rollback从catch广告throw中做error

    • 4

  2. David 的参考资料很棒，但是如果您正在寻找一个实际示例来说明您应该做什么，这里有一个适合您的模块。我没有精确地复制您的示例，因为没有它您可以看到问题。

    我建立了一个示例表并在其中弹出一些数据。我故意尝试将 NULL 插入 dbo.Fact 表中，这是不允许的。

    然后我将我的语句包装在一个 TRY/CATCH 块中。在 Catch 块中，我添加了一个回滚命令和一个 throw，因此原始错误将返回给客户端。如果你省略了 throw 则不会返回任何错误。

    如果您想查看原始行为，则需要删除 TRY/CATCH 并在其中添加 BEGIN TRANSACTION 和 COMMIT。因为即使使用 try/catch SQL 也会正确回滚事务。 

    /** Build up our sample tables and some data.
    **/ 

DROP TABLE IF EXISTS dbo.RawTable;
DROP TABLE IF EXISTS dbo.Fact;

CREATE TABLE dbo.RawTable
    (
    RefNo CHAR(10) NOT NULL
    , field1 CHAR(10) NULL
    , field2 CHAR(10) NULL
    );

CREATE TABLE dbo.Fact
    (
    RefNo CHAR(10) NOT NULL
    , field1 CHAR(10) NOT NULL
    , field2 CHAR(10) NOT NULL
    );

INSERT INTO dbo.RawTable 
    (RefNo, field1, field2)
VALUES ('T1', NULL, 'T1F2')
    , ('T2', 'T2F1', 'T1F2');

SELECT RefNo, field1, field2 FROM dbo.RawTable; 

GO

BEGIN TRY

    BEGIN TRANSACTION;

    INSERT INTO dbo.Fact 
    (RefNo, field1, field2)
    SELECT RefNo, field1, field2
    FROM dbo.RawTable AS T;

    TRUNCATE TABLE dbo.RawTable;

    COMMIT;

END TRY
BEGIN CATCH

    --If we are here and there are open transactions, then rollback.
    IF @@TRANCOUNT >= 1
    BEGIN
        ROLLBACK;
    END

    --We want the error to bubble back to the client so they know something went wrong.
    ;THROW;

END CATCH

GO

--Show some data.
SELECT RefNo, field1, field2 FROM dbo.Fact; 
SELECT RefNo, field1, field2 FROM dbo.RawTable;
    • 2

相关问题