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 outermostBEGIN 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 lastCOMMIT 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.