Transactions (TSQL Statement)

A transaction is a unit of work that is performed against a database.

Essentially, it is a journal recording a sequence of insertions, deletions and updates of records in one or many tables.

Transactions are either autocommit or explicit.

Autocommit Transactions

By default, each single statement that modifes a table runs inside its own private transaction, the autocommit transaction.

An autocommit transaction is implicitly committed when the statement completes successfully.

All statements running when @@TRANCOUNT is 0 are in autocommit mode.

Explicit Transactions

If several statements are grouped inside a transaction by enclosing them between a BEGIN TRANSACTION and COMMIT TRANSACTION statements, they run inside an explicit transaction.

An explicit transaction is committed only when the statement COMMIT TRANSACTION at the outermost transaction level is encountered.

Logfile

When the statements belonging to the transaction (autocommit or explicit) modify the content of tables, these changes are first recorded in a temporary storage, the logfile (also called a journal).

Then, on commit, all changes become permanent on disk: changes are committed.
Else, if a statement inside the transaction has failed, the batch terminates and all changes made so far by this transaction are canceled: changes are rolled back.

Only statements that modifiy tables such as INSERT, UPDATE, DELETE, etc, write changes to the logfile. Statements like SELECT, PRINT, SET @variable, CREATE TABLE, etc, don’t write anything to the logfile, as they don’t change the content of any table.

Rollback on Error

RSQL aborts the batch immediately when an error occurs. If a transaction (autocommit or explicit) is still active, it is automatically rolled back.

To have the same behaviour with MS SQL Server, you must put SET XACT_ABORT ON at the top of all your MS SQL Server batches.

Server Crash Protection

When a server reboots or RSQL restarts after a crash, RSQL reads the logfiles to ensure that the databases are in a consistent state:

if non-committed pending transactions exist, the changes are discarded (a rollback is performed).
if committed pending transactions exist, the logfile is read and changes are applied to the tables (this is called a roll-forward).

This verification is done at RSQL startup. This way, you will never find half-cooked modifications in the databases, even in case of crashes.

Transaction Can Cross Batch Boundaries

A transaction can span multiple batches.

You can issue a BEGIN TRANSACTION in a batch, and issue the corresponding COMMIT TRANSACTION or ROLLBACK TRANSACTION in a subsequent batch.

As long as a transaction is active, locks and resources are not released.

Examples (autocommit transactions)

IF OBJECT_ID('t') IS NOT NULL
    DROP TABLE t;
GO

CREATE TABLE t (id INT NOT NULL PRIMARY KEY, name VARCHAR(30));
GO

INSERT INTO t VALUES(10, 'Fred');
INSERT INTO t VALUES(11, 'Cathy');
INSERT INTO t VALUES(12, 'Alice');
INSERT INTO t VALUES(12, 'Maria');  -- primary key duplicate error. Execution of the batch aborts here.
INSERT INTO t VALUES(14, 'Hayley'); -- this statement is not run
GO

SELECT * FROM t;

The result is:

2016/03/24 22:51:26 --- executing batch example.sql:11 ---
(1 row(s) affected)           <-- Fred, committed

(1 row(s) affected)           <-- Cathy, committed

(1 row(s) affected)           <-- Alice, committed

2016/03/24 22:51:27 5:12 [ERROR_ARITH/ERROR_DUPLICATE_KEY_FOUND/ERROR_BATCH_ABORT/1]  <-- batch aborts
<Duplicate key found, for constraint "$sysidx$0" in table "trashdb.dbo.t">
2016/03/24 22:51:27 return code: -1

2016/03/24 22:51:27 --- executing batch example.sql:18 ---
id         |name           |
-----------+---------------+
         10|Fred           |
         11|Cathy          |
         12|Alice          |

(3 row(s) affected)

2016/03/24 22:51:27 return code: 0

In this example, each INSERT runs inside its own autocommit transaction.

So, all records inserted before the statement that failed have been committed.

Examples (explicit transaction)

IF OBJECT_ID('t') IS NOT NULL
    DROP TABLE t;
GO

CREATE TABLE t (id INT NOT NULL PRIMARY KEY, name VARCHAR(30));
GO

INSERT INTO t VALUES(5,  'Alex');   -- this statement runs in its own autocommit transaction

BEGIN TRANSACTION;                  -- start of an explicit transaction
INSERT INTO t VALUES(9,  'John');
INSERT INTO t VALUES(10, 'Fred');
COMMIT;                             -- make permanent all the changes recorded by the transaction

BEGIN TRANSACTION;                  -- start of an explicit transaction
INSERT INTO t VALUES(11, 'Cathy');
INSERT INTO t VALUES(12, 'Alice');
INSERT INTO t VALUES(12, 'Maria');  -- primary key duplicate error. Execution of the batch aborts here.
INSERT INTO t VALUES(14, 'Hayley');
COMMIT;                             -- make permanent all the changes recorded by the transaction

GO

SELECT * FROM t;

The result is:

2016/03/27 18:34:03 --- executing batch example.sql:17 ---
(1 row(s) affected)           <-- Alex, committed
                              ---------- start of transaction --------------
(1 row(s) affected)           <-- John, temporarily stored in the logfile

(1 row(s) affected)           <-- Fred, temporarily stored in the logfile
                              ---------- commit transaction --------------
                              ---------- start of transaction --------------
(1 row(s) affected)           <-- Cathy, temporarily stored in the logfile

(1 row(s) affected)           <-- Alice, temporarily stored in the logfile

2016/03/27 18:34:03 12:8 [ERROR_ARITH/ERROR_DUPLICATE_KEY_FOUND/ERROR_BATCH_ABORT/1]  <-- batch aborts,
<Duplicate key found, for constraint "$sysidx$0" in table "trashdb.dbo.t">            all uncommitted changes
2016/03/27 18:34:03 return code: -1                                                   are rolled back.

2016/03/27 18:34:03 --- executing batch example.sql:33 ---
id         |name           |
-----------+---------------+
          5|Alex           |
          9|John           |
         10|Fred           |

(3 row(s) affected)

2016/03/27 18:34:03 return code: 0

The first INSERT ran in its own autocommit transaction and committed the row ‘Alex’ in the database.

Then, an explicit transaction is started and committed, which inserts ‘John’ and ‘Fred’ in the database.

Then, another explicit transaction is started, but a statement failed. The batch is aborted and all the changes made inside this transaction are discarded (rolled back).

Guide

Reference

Misc