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
SQL Server transaction be careful when doing rollback (reprint)