SQL Server 2008事务,是否需要回滚?

简短回答:是的。

每当我使用BEGIN TRANSACTION时 总是

在SQL Server 2000及更早版本中,必须使用@@Error逻辑。在SQL 2005及更高版本中,您可以使用(更高级的)TRY…CATCH。。。语法。

更简单的方法是:

set xact_abort on

这将导致在发生错误时自动回滚事务。

示例代码:

set xact_abort on
begin transaction
select 1/0
go
print @@trancount -- Prints 0

set xact_abort off
begin transaction
select 1/0
go
print @@trancount -- Prints 1

如果您多次执行第二个段,您将看到事务计数增加到2、3、4等。第一个段的一次运行将重置所有事务。

begin try
    begin transaction;

    ...

    commit transaction;
end try
begin catch
    declare @ErrorMessage nvarchar(max), @ErrorSeverity int, @ErrorState int;
    select @ErrorMessage = ERROR_MESSAGE() + ' Line ' + cast(ERROR_LINE() as nvarchar(5)), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
    rollback transaction;
    raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState);
end catch

TRY/CATCH 不是 释放锁。但是,我认为下面的模板对大多数事务都是好的。

BEGIN TRY
    BEGIN TRAN
    ...
    IF (@@error <> 0)
       ROLLBACK TRAN
END TRY
BEGIN CATCH
    ROLLBACK TRAN
END CATCH
--BEGIN FINALLY (doesnt exist, which is why I commented it out)    
    IF (@@trancount > 0)
       COMMIT TRAN
--END FINALLY

begin transaction; -- you don't want to hit catch block if begin transaction will fail
begin try

     ... updates/inserts/selects ...

   commit transaction; -- the last statement in try code block is always commit
end try
begin catch
   rollback transaction; -- first step before other error handling code is rollback  transaction
   declare @ErrorMessage nvarchar(max), @ErrorSeverity int, @ErrorState int;
   select @ErrorMessage = ERROR_MESSAGE() + ' Line ' + cast(ERROR_LINE() as nvarchar(5)),  @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
   raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState);
end catch