SQLTransaction 和 T-SQL 事务
我正在使用 .NET 2.0 和 SQL Server 2005。由于历史原因，应用程序代码使用 SQLTransaction，但某些存储过程也使用 T-SQL begin/commit/rollback tran 语句。 这个想法是 DBTransaction 可以跨越许多存储过程，每个单独的存储过程控制其范围内发生的事情 - 实际上这些是嵌套事务。
代码的旧行为是，如果任何存储过程失败，应用程序逻辑也会导致外部 SQLTransaction 也回滚。 但现在我们想要改变逻辑，以便即使发生故障，外部事务也应该继续按顺序执行剩余的存储过程，然后在最后，因为我们知道发生了故障，所以我们回滚整个 SQLTransaction。
问题在于，至少就目前的编码而言，如果任何存储过程执行 ROLLBACK，则外部 SQLTransaction 似乎会丢失其连接，因此任何后续重用事务的尝试都会失败。 有没有办法可以在 T-SQL 中回滚但仍保留外部 SQLTransaction？ 我想也许保存点在这里可能会有帮助，但我还不太了解它们。
使这种情况变得复杂的是，并不总是存在外部事务，因此我不能只删除 T-SQL 回滚，即。 有时存储过程会单独执行； 有时是在交易的背景下。
切换到 TransactionScope 会让事情变得更容易吗？
感谢您的任何建议...迈克
I am using .NET 2.0 and SQL Server 2005. For historical reasons, the app code is using SQLTransaction but some of the stored procedures are also using T-SQL begin/commit/rollback tran statements. The idea is that the DBTransaction can span many stored procedures, which each individual sproc controls what's happening in its scope - in effect these are nested transactions.
The old behavior of the code was that if any of the sprocs failed, application logic would also cause the outer SQLTransaction to also rollback. But now we want to change the logic so that, even if there is a failure, the outer transaction should continue executing the remaining sprocs in its sequence, then at the end, since we know there were failures, we rollback the entire SQLTransaction.
The problem is that, at least as it is presently coded, is that if any of the sprocs does a ROLLBACK, the outer SQLTransaction appears to lose its connection, so any subsequent attempt at reusing the transaction fail. Is there a way I can rollback in T-SQL but still maintain the outer SQLTransaction? I was thinking that maybe savepoints might be helpful here, but I don't understand them very well yet.
What complicates this situation is that there is not always an outer transaction, so I can't just remove the T-SQL rollbacks, ie. sometimes a sproc is executed on its own; sometimes in the context of a transaction.
Would switching to TransactionScope make things easier?
Thanks for any suggestions...Mike
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
查看此知识库条目：
以下情况下可能会发生意外异常发生数据源错误后提交或回滚事务
回滚存储过程中的事务将导致 ADO.NET 客户端中的任何“外部”事务消失。 唯一的解决方案是将 Rollback() 调用包装在 try/catch 块中。 我认为如果发生这种情况，就不可能维持外部交易。
Take a look at this knowledgebase entry:
An unexpected exception may occur when a transaction is committed or rolled back after a data source error has occurred
Rolling back a transaction within a stored proc will cause any "outer" transaction in your ADO.NET client to disappear. The only solution is to wrap your Rollback() call in a try/catch block. I don't believe it's possible to maintain the outer transaction if that happens.
我建议您考虑将外部事务也放在存储过程中，以便维护 TSQL 中的所有嵌套（使用 EXEC 调用其他存储过程）。 SQL Server 是一个令人惊讶的丰富的开发/数据管理环境，并且允许您以 ADO 笨拙的处理方式来管理事务。 还要记住，将一堆 SQL 放在一个存储过程中几乎总是比通过 ADO 连接进行多次调用更有效。
I would suggest that you consider placing your outer transaction in a stored procedure as well so that you maintain all of your nesting within TSQL (use EXEC to call other stored procs). SQL Server is a surprisingly rich development / data management environment and will permit you to manage your transactions in ways that ADO handles clumsily. Keep in mind, too, that it is almost always more efficient to gang a bunch of SQL together in a stored proc than it is to make multiple calls over an ADO connection.
您可能有兴趣查看 IMPLICIT_TRANSACTION 基本上您可以更改存储过程的事务相关模式。 在许多情况下，这是一个更简单的解决方案。
It might interest you to look at IMPLICIT_TRANSACTION Basically with this you can change the transaction dependent mode of your stored procedure. This is an easier solution in many cases.