SQL Server 2008 事务，需要回滚吗? [英] SQL Server 2008 Transaction, rollback required?

问题描述

我有一个包含 BEGIN TRANSACTION 和 COMMIT TRANSACTION 语句的存储过程.在事务中有一个选择查询WITH(XLOCK, ROWLOCK).

如果提供的值越界，交易可能会由于某些计算而失败，这些计算会导致算术溢出错误.此错误会在任何插入/更新语句之前发生.

我的问题是，我应该将事务包装在 TRY/CATCH 中并回滚，还是真的不需要这样做，如果事务失败，所有锁都会自动释放?我唯一担心的是，如果事务失败，SQL 不会释放事务的所有锁.

谢谢，

汤姆

解决方案

简短回答:是的.

每当我使用 BEGIN TRANSACTION 时，我总是包括使用错误处理和 ROLLBACK.遇到意外(和/或不可预料——您无法知道将来可能需要如何修改您的代码)情况导致在生产服务器上留下未处理事务的后果太严重了，不能不这样做.

在 SQL Server 2000 及更早版本中，您必须使用 @@Error 逻辑.在 SQL 2005 及更高版本中，您可以使用(远超)TRY...CATCH... 语法.

I have a stored procedure that has a BEGIN TRANSACTION and COMMIT TRANSACTION statement. Within the transaction is a select query 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.

Thanks,

Tom

解决方案

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.

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 2008 事务，需要回滚吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！