SAVE TRANSACTION 与 BEGIN TRANSACTION (SQL Server) 如何很好地嵌套事务 [英] SAVE TRANSACTION vs BEGIN TRANSACTION (SQL Server) how to nest transactions nicely

问题描述

我有一个需要设置保存点的存储过程，以便在某些情况下，它可以撤消所做的一切并将错误代码返回给调用者，或者接受/提交它并将成功返回给调用者.但是无论调用者是否已经开始事务，我都需要它来工作.该文档在这个主题上非常令人困惑.以下是我认为可行的方法，但我不确定所有后果.

I have a stored procedure that needs to set a save point so that it can, under certain circumstances, undo everything it did and return an error code to the caller, or accept/commit it and return success to the caller. But I need it to work whether the caller has already started a transaction or not. The doc is extremely confusing on this subject. Here is what I think will work, but I'm not certain of all the ramifications.

问题是 - 这个 Stored Procedure (SP) 被其他人调用.所以我不知道他们是否已经开始交易...即使我要求用户开始交易才能使用我的SP，我仍然对Save Points的正确使用有疑问...

The thing is - this Stored Procedure (SP) is called by others. So I don't know if they've started a transaction or not... Even if I require users to start a transaction to use my SP, I still have questions about the proper use of Save Points ...

我的 SP 将测试交易是否正在进行，如果没有，则使用 BEGIN TRANSACTION 开始交易.如果交易已经在进行中，它将改为使用 SAVE TRANSACTION MySavePointName 创建一个保存点，并保存我所做的事实.

My SP will test if a transaction is in progress, and if not, start one with BEGIN TRANSACTION. If a transaction is already in progress, it will instead create a save point with SAVE TRANSACTION MySavePointName, and save the fact this is what I did.

然后如果我必须回滚我的更改，如果我之前做了一个BEGIN TRANSACTION，那么我将ROLLBACK TRANSACTION.如果我做了保存点，那么我将ROLLBACK TRANSACTION MySavePointName.这个场景似乎很有效.

Then if I have to roll back my changes, if I did a BEGIN TRANSACTION earlier, then I will ROLLBACK TRANSACTION. If I did the save point, then I will ROLLBACK TRANSACTION MySavePointName. This scenario seems to work great.

这里是我有点困惑的地方 - 如果我想保留我已经完成的工作，如果我开始一个事务，我将执行 COMMIT TRANSACTION.但是如果我创建了一个保存点呢?我尝试了 COMMIT TRANSACTION MySavePointName，但随后调用者尝试提交其事务并出现错误:

Here is where I get a little confused - if I want to keep the work I've done, if I started a transaction I will execute COMMIT TRANSACTION. But if I created a save point? I tried COMMIT TRANSACTION MySavePointName, but then the caller tries to commit its transaction and gets an error:

COMMIT TRANSACTION 请求没有对应的 BEGIN TRANSACTION.

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

所以我想知道 - 可以回滚保存点(有效:ROLLBACK TRANSACTION MySavePointName 不会回滚调用者的事务).但也许永远不需要提交"它?它只是留在那里，以防您需要回滚到它，但是一旦提交(或回滚)原始事务就会消失?

So I'm wondering then - a save point can be rolled back (that works: ROLLBACK TRANSACTION MySavePointName will NOT roll back the caller's transaction). But perhaps one never needs to "commit" it? It just stays there, in case you need to roll back to it, but goes away once the original transaction is committed (or rolled back)?

如果有更好"的方式来嵌套"交易，也请说明一下.我还没有想出如何与 BEGIN TRANSACTION 嵌套，而只是回滚或提交我的内部事务.似乎ROLLBACK 总是会回滚到最上面的事务，而COMMIT 只是减少@@trancount.

If there is a "better" way to "nest" a transaction, please shed some light as well. I haven't figured out how to nest with BEGIN TRANSACTION but only rollback or commit my internal transaction. Seems ROLLBACK will always roll back to the top transaction, while COMMIT simply decrements @@trancount.

推荐答案

我相信我现在已经想通了，所以我会回答我自己的问题...

I believe I've figured this all out now, so I will answer my own question...

如果您想在 http://geekswithblogs.net/bbiales/archive/2012/03/15/how-to-nest-transactions-nicely---quotbegin-transactionquo​​t-vs-quotsave.aspx

所以我的 SP 以这样的方式开始，如果没有就开始一个新事务，但如果一个已经在进行中，则使用一个保存点:

So my SP starts with something like this, to start a new transaction if there is none, but use a Save Point if one is already in progress:

DECLARE @startingTranCount int
SET @startingTranCount = @@TRANCOUNT

IF @startingTranCount > 0
    SAVE TRANSACTION mySavePointName
ELSE
    BEGIN TRANSACTION
-- …

然后，当准备提交更改时，您只需要在我们自己启动事务时提交:

Then, when ready to commit the changes, you only need to commit if we started the transaction ourselves:

IF @startingTranCount = 0
    COMMIT TRANSACTION

最后，仅回滚您目前所做的更改:

And finally, to roll back just your changes so far:

-- Roll back changes...
IF @startingTranCount > 0
    ROLLBACK TRANSACTION MySavePointName
ELSE
    ROLLBACK TRANSACTION

这篇关于SAVE TRANSACTION 与 BEGIN TRANSACTION (SQL Server) 如何很好地嵌套事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！