使用事务 - 最佳实践

Question

使用事务 - 最佳实践

sql-servert-sqlstored-procedurestransactions

4

假设我在 SQL Server 中有一个存储过程：

create procedure BULKINSERT
AS
 INSERT INTO TABLEB (
 SELECT NAME, ID From TableA
)
GO

一个简单的操作，从表A读取数据并插入到表B。如果我有100万条记录要插入到表B中，如果由于任何原因导致一条记录失败，那么在这种情况下我应该使用事务吗？

我应该回滚整个操作吗？

- user1400915

请在事务中使用 TRY CATCH。 - RoMEoMusTDiE

1

由于一个语句总是被视为原子操作，而在这里你只有一个语句，要么向TABLEB插入100万条记录，要么不插入。然而，如果出现错误，你可能希望处理它，在这种情况下，你应该将你的INSERT语句放入一个TRY块中，并添加一个CATCH块来处理错误时该怎么做。 - MK_

1

@MK_ 您是正确的。您应该将您的评论发布为答案。 - Zohar Peled

3个回答

2

如所述，单个语句即为一次事务。

按TABLEB的主键对插入进行排序以减缓碎片化。

需要注意的是不要多次插入相同的数据。
如果想要防止这种情况，请使用主键左连接。

declare @Ta table (id int identity primary key, name varchar(10));
declare @Tb table (id int          primary key, name varchar(10));
insert into @Ta values ('name'), ('name'), ('name'), ('name'), ('name'), ('name'), ('name'), ('nameL');

insert into @Tb (id, name)
select id, name from @Ta order by id;

select * from @Tb;

insert into @Tb (id, name)
select ta.id, ta.name 
from @Ta ta 
left join @Tb tb 
  on tb.id = ta.id 
where tb.id is null 
order by ta.id;

select * from @Tb;

如果您想更新已经存在的数据，请使用合并命令进行搜索。

- paparazzo

2

由于一个语句始终被视为原子性操作，而您在此处只有一个语句，要么插入1百万条记录到TABLEB中，要么不插入。但是，如果出现错误，您可能希望处理该错误，在这种情况下，应将INSERT放入TRY块中，并添加CATCH块来处理错误时要执行的操作。

要了解更多信息，请查阅以下来源作为良好起点：

- MK_

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- gotqn · Accepted Answer

您可以使用以下模板编写存储过程:

SET NOCOUNT, XACT_ABORT ON;

    BEGIN TRY

        BEGIN TRANSACTION;
        -- CODE BLOCK GOES HERE
        COMMIT TRANSACTION;

    END TRY
    BEGIN CATCH 

       IF @@TRANCOUNT > 0
       BEGIN
          ROLLBACK TRANSACTION
       END;

       -- GET ERRORS DETAILS OR THROW ERROR

    END CATCH;

SET NOCOUNT, XACT_ABORT OFF;

更多细节：

XACT_ABORT - 指定当 Transact-SQL 语句引发运行时错误时，SQL Server 是否自动回滚当前事务；
如果您需要有关错误的信息 (ERROR_MESSAGE, ERROR_LINE, ERROR_NUMBER, ERROR_PROCEDURE, ERROR_SEVERITY, ERROR_STATE)

这是一种处理事务的通用技术。我建议阅读 Erland Sommarskog 的以下文章：