SQL Server Transactions Management

Estimated study time: 9 minutes. Group statements so they all succeed — or none do.

A transaction groups one or more statements so they execute as a single unit: either everything commits, or everything rolls back if something goes wrong.

Basic Transaction

BEGIN TRANSACTION;

UPDATE Accounts SET Balance = Balance - 500 WHERE AccID = 1;
UPDATE Accounts SET Balance = Balance + 500 WHERE AccID = 2;

COMMIT TRANSACTION;

Rolling Back on Error

BEGIN TRANSACTION;

BEGIN TRY
    UPDATE Accounts SET Balance = Balance - 500 WHERE AccID = 1;
    UPDATE Accounts SET Balance = Balance + 500 WHERE AccID = 2;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
END CATCH;

The ACID Properties

Transactions are built around four guarantees: Atomicity (all or nothing), Consistency (valid state to valid state), Isolation (transactions don't interfere with each other), and Durability (once committed, it survives a crash).

Savepoints

A savepoint lets you roll back part of a transaction without discarding all of it:

BEGIN TRANSACTION;
SAVE TRANSACTION BeforeUpdate;

UPDATE Accounts SET Balance = Balance - 500 WHERE AccID = 1;

ROLLBACK TRANSACTION BeforeUpdate;
COMMIT TRANSACTION;