ROLLBACK TRANSACTION behavior

We have four statements to wok with transaction:

1. BEGIN TRANSACTION
2. COMMIT TRANSACTION
3. SAVE TRANSACTION
4. ROLLBACK TRANSACTION

BEGIN TRANSACTION: Starts a new transaction. Each BEGIN TRAN statement increment the @@trancount value by 1. Let us execute the below script:

COMMIT TRANSACTION: commits the innermost transaction and decrement the @@trancount by 1.

See below the result of following script:

CREATE TABLE TestTran (ID int, Col1 VARCHAR(25))

GO

BEGIN TRAN T1

INSERT INTO TestTran VALUES(@@trancount, 'Begin tran 1')

BEGIN TRAN T2

INSERT INTO TestTran VALUES(@@trancount, 'begin tran 2')

COMMIT TRAN T2

INSERT INTO TestTran VALUES(@@trancount, 'commit tran 2')

COMMIT TRAN T1

GO

SELECT * FROM TestTran

Result is as following:

SAVE TRANSACTION: It does not start a new transaction so the value of @@trancount is not affected. It is just a savepoint in transaction to rollback a portion of transaction. Continuing the above script execute the below script:

 

Result is as following:



ROLLBACK TRANSACTION: The behavior of rollback is most interesting and most powerful. It shows two types of behavior:

1. If used with savepoint name (as in above example) then rollback the portion of transaction upto that savepoint.

2. If used without savepoint name then rollbacks up to outermost transaction and @@trancount is set to 0.

In continuing the above, execute the below script:

TRUNCATE TABLE TestTran

BEGIN TRAN T1

INSERT INTO TestTran VALUES(@@trancount, 'begin tran 1')

BEGIN TRAN T2

INSERT INTO TestTran VALUES(@@trancount, 'begin tran 2')

ROLLBACK TRAN

COMMIT TRAN T1        --returns error that no corresponding BEGIN TRAN..

SELECT * FROM TestTran    --returns zero records

In this script ROLLBACK TRAN rolled back not only the transaction T2 but also the first transaction T1.

TRUNCATE TABLE TestTran

BEGIN TRAN T1

INSERT INTO TestTran VALUES(@@trancount, 'Begin tran 1')

SAVE TRAN S1

INSERT INTO TestTran VALUES(@@trancount, 'save tran 1')

ROLLBACK TRAN S1

INSERT INTO TestTran VALUES(@@trancount, 'Rollback to savepoint s1')

COMMIT TRAN T1

SELECT * FROM TestTran