sql - Multiple Begin Transactions -

i came across today , accident , wondering few things. basic code setup is

begin transaction  update table set column value  begin transaction update table set column value 

i have played little , found can not commit after doing rollback, can commit before rollback, rollback negates commit. guess question is, there purpose/use this? see not other making dba slap me bad code lol

the short answer intent behind design of nested transactions allow code reusable procedures (or blocks of code) 2 following situations can handled automatically without having write code differently both cases:

• you can start , end transaction if none has been started yet.
• or, if transaction in progress, participate in on-going transaction.

so let's code reusable procedures in transactional manner, (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 need blah procedure incorporate foo does. obviously, wouldn't want copy-paste contents of foo in blah. simple call foo makes more sense reusability's sake, this:

create procedure blah     begin transaction      perform dml 1     perform dml 2      -- include call foo here     foo();      perform dml 3     -- other stuff      commit transaction end procedure 

in above case, without changes foo's code, call blah still behave 1 big transaction, want.

it's cases these inner commits don't anything. serve purpose of flagging ok until point. real commit happens when outer transaction commits everything.

imagine if every commit committed transaction, then, ensure don't corrupt outer transaction, have add conditions @ beginning of every procedure check if transaction started, , start 1 if none found. so, every procedure have coded ensure it's safe calling within other procedures:

create procedure foo     didistartatransaction = false     if @@trancount = 0       begin transaction       didistartatransaction = true     end if      perform dml 1     perform dml 2     perform dml 3     -- other stuff      if didistartatransaction       commit transaction     end if end procedure  create procedure blah     didistartatransaction = false     if @@trancount = 0       begin transaction       didistartatransaction = true     end if      perform dml 1     perform dml 2     perform dml 3     -- other stuff      if didistartatransaction       commit transaction     end if end procedure 

that said, nested transactions can still dangerous if 1 of procedures forgets symmetrically start , commit transaction.

and personally, prefer not have transaction control statements in of procedures, , have calling code manage transaction. feel lot safer way.