SQL Server 2008事务，需要回滚吗？

I have a stored procedure that has a BEGIN TRANSACTION and COMMIT TRANSACTION statement. Within the transaction is a select query WITH(XLOCK, ROWLOCK).

我有一个存储过程具有BEGIN TRANSACTION和COMMIT TRANSACTION语句。在事务内是一个选择查询WITH（XLOCK，ROWLOCK）。

The transaction can potentially fail due to some calculations that cause an arithmetic overflow error if out of bounds values are supplied. This error would happen before any insert/update statements.

如果提供了超出边界值的某些计算导致算术溢出错误，则事务可能会失败。在任何插入/更新语句之前都会发生此错误。

My question is, should I wrap the transaction in a TRY/CATCH and rollback or is this not really required and all locks would be released automatically if the transaction fails? My only concern here is that SQL would not release all locks of the transaction in case the transaction fails.

我的问题是，我应该将事务包装在TRY / CATCH和回滚中，还是这不是真正需要的，如果事务失败，所有锁都会自动释放？我唯一担心的是，在事务失败的情况下，SQL不会释放事务的所有锁。

Thanks,

谢谢，

Tom

汤姆

5 个解决方案

#1

Short answer: Yes.

简短回答：是的。

Whenever I use BEGIN TRANSACTION, I always include use error handling and ROLLBACK. The consequences of hitting an unanticipated (and/or unanticipatable -- you can't know how your code may need to be modified in the future) situation that leaves an open transaction on a Production server are too severe to not do it.

每当我使用BEGIN TRANSACTION时，我总是包含使用错误处理和ROLLBACK。在生产服务器上打开未完成的事件（和/或无法理解 - 您无法知道将来如何修改代码）的情况会导致生产服务器上的开放事务过于严重而无法执行此操作。

In SQL Server 2000 and earlier, you have to use @@Error logic. In SQL 2005 and up, you get to use the (far superior) TRY...CATCH... syntax.

在SQL Server 2000及更早版本中，您必须使用@@ Error逻辑。在SQL 2005及更高版本中，您可以使用（远优于）TRY ... CATCH ...语法。

#2

A much easier way is:

一种更简单的方法是：

set xact_abort on

This will cause the transaction to be rolled back automatically when an error occurs.

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

Example code:

示例代码：

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

If you execute the second segment multiple times, you'll see the transaction count increase to 2,3,4 etc. A single run of the first segment resets all transactions.

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

#3

I like Brad's approach but it needed a little clean up so you can see the error that caused the problem.

我喜欢Brad的方法，但它需要一点点清理，以便您可以看到导致问题的错误。

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

#4

TRY/CATCH isn't required to release locks. However, I think that the following template would be good for most transactions.

释放锁定不需要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

#5

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

#1