Multiple Begin Transactions

Question

I came across this today and accident and was wondering a few things. The basic code setup is

Begin Transaction 
Update Table
set column to value

Begin transaction
Update Table
set column to value

I have played with it a little and found you can not do a commit after a doing a rollback, but you can do a commit before a rollback, however the rollback negates the commit. I guess my question is, is there any purpose/use for this? I see not other then making my DBA slap me for bad code lol

Answer 1

The short answer is that the intent behind the design of nested transactions is to allow you to code reusable procedures (or blocks of code) where the 2 following situations can be handled automatically without having to write the code differently for both cases:

• You can start and end a transaction if none has been started yet.
• Or, if a transaction is already in progress, then you just participate in the on-going transaction.

So let's say you like to code all your reusable procedures in a transactional manner, like this (pseudo code):

create procedure Foo
    begin transaction

    perform DML 1
    perform DML 2
    perform DML 3
    -- other stuff

    commit transaction
end procedure

create procedure Blah
    begin transaction

    perform DML 1
    perform DML 2
    perform DML 3
    -- other stuff

    commit transaction
end procedure

But now, let's say that you now need the Blah procedure to incorporate what Foo does. Obviously, you wouldn't want to copy-paste the contents of Foo in Blah. A simple call to Foo makes more sense for reusability's sake, like this:

create procedure Blah
    begin transaction

    perform DML 1
    perform DML 2

    -- include a call to Foo here
    Foo();

    perform DML 3
    -- other stuff

    commit transaction
end procedure

In the above case, without any changes to Foo's code, the call to Blah will still behave as one big transaction, which is probably what you want.

It's exactly for cases like these that inner commits don't actually do anything. They really only serve the purpose of flagging that everything was ok up until that point. But the real commit only happens when the outer transaction commits everything.

Imagine if every commit actually committed the transaction, then, to ensure that you don't corrupt the outer transaction, you would have to add extra conditions at the beginning of every procedure to check if a transaction is already started, and only start one if none is found. So, every procedure would have to be coded something like this to ensure it's safe for calling within other procedures:

create procedure Foo
    didIStartATransaction = false
    if @@trancount = 0 then
      begin transaction
      didIStartATransaction = true
    end if

    perform DML 1
    perform DML 2
    perform DML 3
    -- other stuff

    if didIStartATransaction then
      commit transaction
    end if
end procedure

create procedure Blah
    didIStartATransaction = false
    if @@trancount = 0 then
      begin transaction
      didIStartATransaction = true
    end if

    perform DML 1
    perform DML 2
    perform DML 3
    -- other stuff

    if didIStartATransaction then
      commit transaction
    end if
end procedure

That said, nested transactions can still be dangerous if one of the procedures forgets to symmetrically start and commit a transaction.

And personally, I prefer to not have any transaction control statements in any of my procedures, and just have the calling code manage the transaction. I feel a lot safer that way.

Answer 2

Please take a look at SAVEPOINT syntax. This allows you to set points in a transaction you can rollback to.

https://msdn.microsoft.com/en-us/library/ms188378.aspx

Within there lies your answer :)