SQL Server transaction be careful when doing rollback (reprint)

After careful study, it is still a bit complicated to find the explicit transaction (display transaction) inside SQL Server. Here are some summaries:

Commit TRANSACTION commits all nested transaction modifications. However, if the nested transaction contains rollback TRAN to save point, then the portion after save point will be revert out.

Delete  fromdbo.numbertablebegin TranOUT1Insert  intoDbo.numbertableValues(1)     Insert  intoDbo.numbertableValues(2)          begin Traninn1Insert  intoDbo.numbertableValues(3)          Insert  intoDbo.numbertableValues(4)     Save TranInn1savepointInsert  intoDbo.numbertableValues(5)     rollback TranInn1savepointCommit Traninn1Commit TranOut1

@ @TRANCOUNT can be used to record the current session transaction number, for nested transaction, each BEGIN TRANSACTION Let it add one, each commit Tran will let it minus one. So inside the statement you can check whether the current is in a transaction by a SELECT @ @TRANCOUNT. If the current @ @TRANCOUNT is 0, then a statement error will occur if you call commit or rollback. inside the nested transaction, the rollback is very special, it will directly set the @ @TRANCOUNT to 0.

begin Tran begin Tran begin Tran Print @ @trancount rollback Tran Print @ @trancount

For nested transaction, the rollback is very special. If nested, rollback transaction is not the name with transaction, and it is only the outermost transaction name. Rollback discards all nested transaction modifications before the rollback statement. But rollback after the update will still be submitted, because: after rollback, @ @trancount is 0, then rollback after the statement is not explicit transaction, belongs to Autocmmit Transaction, automatic submission.

Delete  fromdbo.numbertablebegin TranT1Insert  intoDbo.numbertableValues(1)          begin TranT2Insert  intoDbo.numbertableValues(2)     rollback Tran     Print 'After rollback in Innert transaction, the transaction count is:'+cast(@ @trancount,varchar(5))     Insert  intoDbo.numbertableValues(3)--Commit TranSelect *  fromDbo.numbertable

The stored procedure can also begin TRANSACTION, if the place where the stored procedure is called also BEGIN TRANSACTION, then this is also a nested transaction, if rollback in the stored procedure, the results are the same as above. But there's a special place where the execution of the stored procedure ends up comparing the @ @trancount at the end of the execution of the stored procedure with the @ @trancount value, if not, SQL Server gives a message like "Transaction count After EXECUTE indicates, a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0. " This given message does not affect the subsequent execution.

CREATE PROCEDURE [dbo].[Addnumber]        asBEGIN     begin Tran           Insert  intoDbo.numbertableValues(1)          Insert  intoDbo.numbertableValues(2)          Insert  intoDbo.numbertableValues(3)     rollback TranENDDelete  fromdbo.numbertablebegin TranOUT1execDbo.addnumberPrint @ @trancountInsert  intoDbo.numbertableValues(3)Select *  fromDbo.numbertable

If you do rollback in the stored procedure, then do commit or rollback outside is not effective and will error, because nested transaction internal transaction once called rollback,@@ Trancount is 0, in the outside commit or rollback will be directly error. For example, the following SP, I imagine the outermost rollback, it is wrong, because the SP inside the statement rollback. The value 3 is always inserted in the final table.

 delete  from   dbo.numbertable  begin  tran   OUT1  exec   Dbo.addnumber  print  @ @trancount  insert  into  dbo.numbertable values  (3  )  rollback  tran   OUT1  select  *  from  dbo.numbertable 

So for nested transaction, if the internal transaction once rollback, it will leave a big hole for the outer transaction. In order to solve this problem, there are two kinds of solutions:

1. In the external transaction check @ @trancount, if this value is consistent with your code begin TRAN, it means that the internal transaction does not have rollback, it can continue to commit or rollback.

Delete  fromdbo.numbertablebegin TranT1Insert  intoDbo.numbertableValues(1)          begin TransactionT2Insert  intoDbo.numbertableValues(2)     rollback Tran      if @ @trancount = 1      begin          Insert  intoDbo.numbertableValues(3)          Commit Tran     End

2. Inside all internal transaction, only commit, not rollback. What if it has to be rollback? Save point can be useful. For example, SP changed to look like this:

ALTER PROCEDURE [dbo].[Addnumber]         asBEGIN     begin Tran      Save TranppInsert  intoDbo.numbertableValues(1)          Insert  intoDbo.numbertableValues(2)          Insert  intoDbo.numbertableValues(3)     rollback TranppCommit TranEND begin TranOUT1execDbo.addnumberPrint @ @trancountInsert  intoDbo.numbertableValues(3)Commit TranOut1

Original link

SQL Server transaction be careful when doing rollback (reprint)