BEGIN TRANSACTION (TSQL Statement)

Starts an explicit transaction.

Syntax

BEGIN { TRAN | TRANSACTION }

Remarks

This statement starts an explicit transaction.

All modifications that subsequent statements will make to tables (insertions, deletions and updates of records) will be recorded in the journal of this transaction.

If a statement raises an error, the batch aborts immediately. If a transaction is still active, a rollback is automatically performed and the pending changes are discarded.

The function @@TRANCOUNT returns the current nesting level of transaction.
If no BEGIN TRANSACTION has been encountered, @@TRANCOUNT is 0, which indicates that the statements run in autocommit mode.

Transactions can be nested. Each BEGIN TRANSACTION just increments @@TRANCOUNT.

When COMMIT TRANSACTION is encountered:

If no transaction is active (@@TRANCOUNT is 0), an error is raised.
If @@TRANCOUNT == 1, the changes recorded by the transaction since the outermost BEGIN TRANSACTION are made permanent.
Then, @@TRANCOUNT is set to 0.
Else, we are inside a nested transaction. @@TRANCOUNT is just decremented.

When ROLLBACK TRANSACTION is encountered:

If no transaction is active (@@TRANCOUNT is 0), an error is raised.
Else, all changes recorded by the transaction since the outermost BEGIN TRANSACTION are canceled, even if we are deep inside a nested transaction. Then, @@TRANCOUNT is set to 0.

Avoid Nested Transactions

Don’t nest transactions. It is too easy to get lost with the transaction nesting level.

A BEGIN TRANSACTION inside an active transaction doesn’t start a new transaction.
It just increments the counter of @@TRANCOUNT.
A COMMIT TRANSACTION inside a nested transaction doesn’t make the changes permanent.
It just decrements the counter of @@TRANCOUNT.
Only the last COMMIT TRANSACTION at the outermost level make the changes permanent.
Besides, the ROLLBACK TRANSACTION statement really discards all changes, even if it is buried deep inside many nested transaction levels, and immediately resets the nesting level to 0.

This will make your script a real mess. So, don’t do that !

Keep it Simple !

Just put a BEGIN TRANSACTION at the top of your batch and a COMMIT TRANSACTION at the end.

If a statement raises an error, or if you raise an error with THROW, the batch will abort immediately and an automatic rollback will be performed, discarding all the changes.

BEGIN TRANSACTION

... Put your statements here (INSERT, SELECT, etc).
... The statements IF, WHILE, GOTO, SET @variable, etc are also allowed.

COMMIT TRANSACTION

Besides, you never need to use ROLLBACK TRANSACTION. It is better to raise an error with THROW, which will terminate the batch and automatically perform a rollback.

This way, all the changes produced by your batch will be applied, or none of them if an error occurred.

Examples

CREATE TABLE parent (id         INT NOT NULL IDENTITY( 100, 1) PRIMARY KEY,
                     first_name VARCHAR(20),
                     name       VARCHAR(20)
                    );

CREATE TABLE child  (id         INT NOT NULL IDENTITY(5000, 1) PRIMARY KEY,
                     parent_id  INT NOT NULL,
                     first_name VARCHAR(20)
                    );
GO

DECLARE @id BIGINT;

BEGIN TRAN;
INSERT INTO parent (first_name, name) VALUES ('Paul', 'SMITH');
SET @id = SCOPE_IDENTITY();
INSERT INTO child (parent_id, first_name) VALUES (@id, 'John');
INSERT INTO child (parent_id, first_name) VALUES (@id, 'Jack');
INSERT INTO child (parent_id, first_name) VALUES (@id, 'Jill');
COMMIT;

BEGIN TRAN;
INSERT INTO parent (first_name, name) VALUES ('Vladimir', 'POPOV');
SET @id = SCOPE_IDENTITY();
INSERT INTO child (parent_id, first_name) VALUES (@id, 'Anastasia');
INSERT INTO child (parent_id, first_name) VALUES (@id, 'Taysia');
COMMIT;

BEGIN TRAN;
INSERT INTO parent (first_name, name) VALUES ('Jean', 'DUBOIS');
COMMIT;

BEGIN TRAN;
INSERT INTO parent (first_name, name) VALUES ('Mario', 'ROSSI');
SET @id = SCOPE_IDENTITY();
INSERT INTO child (parent_id, first_name) VALUES (@id, 'Isabella');
INSERT INTO child (parent_id, first_name) VALUES (NULL, 'Anna');  -- error, batch aborts
COMMIT;

BEGIN TRAN;
INSERT INTO parent (first_name, name) VALUES ('Franz', 'MULLER');
SET @id = SCOPE_IDENTITY();
INSERT INTO child (parent_id, first_name) VALUES (@id, 'Hans');
INSERT INTO child (parent_id, first_name) VALUES (@id, 'Greta');
COMMIT;

GO

SELECT * FROM parent;
SELECT * FROM child;

The result is:

2016/03/26 03:18:07 --- executing batch example.sql:20 ---
(1 row(s) affected)  <-- Paul SMITH

(1 row(s) affected)  <--     John

(1 row(s) affected)  <--     Jack

(1 row(s) affected)  <--     Jill

(1 row(s) affected)  <-- Vladimir POPOV

(1 row(s) affected)  <--     Anastasia

(1 row(s) affected)  <--     Taysia

(1 row(s) affected)  <-- Jean DUBOIS

(1 row(s) affected)  <-- Mario ROSSI

(1 row(s) affected)  <--     Isabella

2016/03/26 03:18:07 27:12 [ERROR_GENERAL/ERROR_TABLE_COLUMN_DOESNT_ALLOW_NULL/ERROR_BATCH_ABORT/1]
<Column "parent_id" doesn't allow NULL.>   <-- Anna
2016/03/26 03:18:07 return code: -1

2016/03/26 03:18:07 --- executing batch example.sql:57 ---
id         |first_name     |name           |
-----------+---------------+---------------+
        100|Paul           |SMITH          |
        101|Vladimir       |POPOV          |
        102|Jean           |DUBOIS         |

(3 row(s) affected)

id         |parent_id  |first_name     |
-----------+-----------+---------------+
       5000|        100|John           |
       5001|        100|Jack           |
       5002|        100|Jill           |
       5003|        101|Anastasia      |
       5004|        101|Taysia         |

(5 row(s) affected)

2016/03/26 03:18:07 return code: 0

The transaction for Mario ROSSI and his daughters has failed. The batch has aborted and this pending transaction has been automatically rolled back, cancelling all changes it has recorded.

Guide

Reference