Multiple Starter Transactions
I ran into this today and by accident, and I was wondering something. Basic code setup
Begin Transaction
Update Table
set column to value
Begin transaction
Update Table
set column to value
I played around with it a bit and found that you cannot commit after doing a rollback, but you can commit before rolling back, however rollback cancels the commit. I guess my question is, is there a purpose / use for this? I see no other and then make my DBA spank me for bad code lol
source to share
The short answer is that the design goal of nested transactions is so that you can code reusable procedures (or blocks of code) where the following two situations can be handled automatically, without having to write code differently for both:
- You can start and end a transaction if it is not already running.
- Or, if the transaction is already in progress, you are simply participating in the current transaction.
So say you like to code all your reusable procedures in a transactional way, 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 now you need a procedure Blah
to turn on what it does Foo
. Obviously, you don't want to copy the content Foo
to Blah
. A simple call Foo
makes more sense for reuse, for example:
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 code changes, the Foo
call Blah
will still behave like one big transaction, which is probably what you want.
It is for such cases that the inner commit
actually does nothing. They really only serve to point out that everything was in order up to this point. But the real commit only happens when the outside transaction commits everything.
Imagine if each commit actually commits a transaction, then to ensure that you don't corrupt the external transaction, you would need to add additional conditions at the beginning of each procedure to check if the transaction has actually started, and only start one if not one not found. Thus, each procedure must be coded in such a way that it is safe to call 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
However, nested transactions can be dangerous if one of the procedures forgets to symmetrically start and commit a transaction.
And personally, I prefer not to have any transaction control statements in any of my procedures, but just have the invocation code to control the transaction. I feel much safer.
source to share
Pay attention to the syntax SAVEPOINT
. This allows you to set points in a transaction that you can rollback to.
https://msdn.microsoft.com/en-us/library/ms188378.aspx
Inside is your answer :)
source to share
What you are doing is a nested transaction .
This is what happens: If you rollback an internal transaction, you will also rollback the most remote transaction. On the other hand, if you are committing an internal transaction, your data modification does not occur until the most recent transaction is completed.
More details here .
Obviously, using a nested transaction in your code doesn't make sense. But think about this code:
BEGIN TRAN
UPDATE MyTable
SET Col1 = 'Val1';
EXEC dbo.SomeStoredProcedureUsingTransactions;
IF @@TRANCOUNT > 0
COMMIT TRAN;
source to share