TCL in SQL Server: BEGIN TRAN, COMMIT, ROLLBACK

(also called DTL - Data Transaction Language)

A transaction = a group of statements that are saved
together, or undone together.
  BEGIN TRANSACTION -> start
  COMMIT            -> save the changes
  ROLLBACK          -> undo the changes
  SAVE TRANSACTION  -> a checkpoint to roll back to

This lesson creates its own small tables, so you can run it on any SQL Server database. The Output under each query is the real result.

IF DB_ID('SqlBasicsDb') IS NULL
    CREATE DATABASE SqlBasicsDb;
GO

USE SqlBasicsDb;
GO

DROP TABLE IF EXISTS Accounts;
GO

CREATE TABLE Accounts
(
    AccountID INT PRIMARY KEY,
    Name      VARCHAR(50),
    Balance   INT
);

INSERT INTO Accounts VALUES (1, 'Ravi', 1000), (2, 'Sita', 500);
GO

SELECT * FROM Accounts;

(2 rows affected)

COMMIT - changes are saved

BEGIN TRANSACTION;
    UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 1;
COMMIT;

SELECT * FROM Accounts;     -- Ravi = 1100

(1 row affected)

ROLLBACK - changes are undone

BEGIN TRANSACTION;
    UPDATE Accounts SET Balance = 0;          -- oops, all balances 0
    SELECT * FROM Accounts;                   -- shows 0 inside the transaction
ROLLBACK;

SELECT * FROM Accounts;     -- balances are back

(2 rows affected)

Money transfer: both updates must happen together

Ravi sends 200 to Sita

BEGIN TRANSACTION;
    UPDATE Accounts SET Balance = Balance - 200 WHERE AccountID = 1;
    UPDATE Accounts SET Balance = Balance + 200 WHERE AccountID = 2;
COMMIT;

SELECT * FROM Accounts;     -- Ravi = 900, Sita = 700

(1 row affected)
(1 row affected)

SAVE TRANSACTION - undo only part of the work

BEGIN TRANSACTION;
    INSERT INTO Accounts VALUES (3, 'John', 300);
    SAVE TRANSACTION AfterJohn;               -- checkpoint

    INSERT INTO Accounts VALUES (4, 'Meena', 400);
    ROLLBACK TRANSACTION AfterJohn;           -- undo Meena only
COMMIT;

SELECT * FROM Accounts;     -- John is saved, Meena is not

(1 row affected)
(1 row affected)

@@TRANCOUNT - how many transactions are open right now

SELECT @@TRANCOUNT AS OpenTransactions;      -- 0

BEGIN TRANSACTION;
    SELECT @@TRANCOUNT AS OpenTransactions;  -- 1
COMMIT;

-- =============================================
-- Remember
--   Without BEGIN TRANSACTION, every statement is saved immediately.
--   Always finish a transaction with COMMIT or ROLLBACK,
--   otherwise the table stays locked for other users.
-- =============================================