Lesson 9	Creating transactions
Objective	Create and manage transactions in SQL-Server

Create and Manage Transactions in SQL Server

In SQL Server, an explicit transaction groups one or more statements into a single unit of work. Either every change is made permanent together, or none of them are. You manage explicit transactions with three core statements:

BEGIN TRANSACTION — start a transaction
COMMIT TRANSACTION — make the work permanent
ROLLBACK TRANSACTION — undo the work

For partial rollback inside a larger transaction, you also use:

SAVE TRANSACTION — create a savepoint

Transaction statement syntax

The following figures show the most common syntax patterns you will use in modern SQL Server (including SQL Server 2025). The progression from #bcrtrans1 through #bcrtrans8 is intentionally incremental: first naming a transaction, then using variables, then rolling back the entire transaction, and finally rolling back to savepoints.

Azure SQL-Server

BEGIN TRAN[SACTION] [transaction_name];

Begin Transaction (named)
transaction_name is optional. A name is most useful for readability (especially in stored procedures) and for communicating intent during nested transaction patterns. SQL Server tracks open transactions using @@TRANCOUNT.

DECLARE @tran_name sysname = N'TransferFunds';
BEGIN TRAN[SACTION] @tran_name;

Begin Transaction (name stored in a variable)
A variable-driven name can be convenient when you log or print transaction context, or when you standardize naming across reusable scripts.

COMMIT TRAN[SACTION] [transaction_name];

Commit Transaction
The transaction name is optional. In SQL Server, COMMIT makes your work permanent only when the outermost transaction is committed. (With nested BEGIN TRAN calls, each COMMIT decrements @@TRANCOUNT until it reaches 0.)

DECLARE @tran_name sysname = N'TransferFunds';
COMMIT TRAN[SACTION] @tran_name;

Commit Transaction (name stored in a variable)
As with named commits, the key behavior is that the outermost commit finalizes the work.

ROLLBACK [TRAN[SACTION]];

Rollback Transaction (entire transaction)
This is the most common rollback pattern: undo all work in the current transaction scope. If you are inside nested BEGIN TRAN calls and you issue a plain ROLLBACK, SQL Server rolls back the entire transaction and resets @@TRANCOUNT to 0.

ROLLBACK [TRAN[SACTION]]
  [ transaction_name | @transaction_variable | savepoint_name | @savepoint_variable ];

Rollback options
You can roll back the whole transaction, or (when a savepoint exists) roll back to a savepoint to undo only part of the work while keeping the transaction open.

-- Create a savepoint inside an active transaction
SAVE TRAN[SACTION] SavePointName;

-- Undo work back to that savepoint (transaction remains open)
ROLLBACK TRAN[SACTION] SavePointName;

Rollback to savepoint name
savepoint_name identifies a point inside the transaction. Rolling back to a savepoint reverses changes made after that savepoint, but the transaction is still active and must be explicitly committed or rolled back.

DECLARE @sp sysname = N'SavePointName';
ROLLBACK TRAN[SACTION] @sp;

Rollback to savepoint variable
Use a variable-driven savepoint name when you centralize control flow, log decisions, or generate scripts programmatically.

TRAN vs TRANSACTION

You can write TRAN or TRANSACTION; they are synonymous. Most teams standardize on BEGIN TRAN, COMMIT TRAN, and ROLLBACK TRAN for readability.

SAVE TRANSACTION syntax

SAVE TRANSACTION creates a savepoint inside an active transaction. Savepoints are especially useful when your transaction performs several steps and you want the option to undo only the last step(s) while keeping earlier changes intact.

SAVE { TRAN | TRANSACTION } { savepoint_name | @savepoint_variable };

savepoint_name is the name of your savepoint. This is used to identify the point in the transaction where you might want to rollback to. — 1) `savepoint_name` identifies where you might want to roll back to.

@savepoint_variable is the name of your savepoint as stored in a variable. — 2) `@savepoint_variable` stores the savepoint name in a variable.

Creating a transaction

Creating a transaction is straightforward: start the transaction, execute the statements that must succeed together, and commit the work. A practical (and classic) example is transferring funds—two updates must succeed together or the database becomes inconsistent.

BEGIN TRAN;

UPDATE dbo.Checking
SET Balance = Balance - 1000.00
WHERE AccountName = N'Glenn';

UPDATE dbo.Savings

SET Balance = Balance + 1000.00
WHERE AccountName = N'Glenn';

COMMIT TRAN;

Modern error handling for transactions

In modern SQL Server, the recommended pattern for transaction safety is TRY...CATCH with XACT_STATE(), plus (often) SET XACT_ABORT ON to ensure certain runtime errors automatically abort the transaction. This approach replaces legacy patterns that relied on @@ERROR checks after each statement.

SET XACT_ABORT ON;

BEGIN TRY
  BEGIN TRAN;

  -- 1) DDL and DML can both participate in transactions in SQL Server.
  --    (Not every operation is allowed inside a transaction, but typical table/index DDL is transactional.)
  CREATE TABLE dbo.Timesheets
  (
    TimesheetID     int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Timesheets PRIMARY KEY,
    EmployeeID      int               NOT NULL,
    ClientID        int               NOT NULL,
    TaskID          int               NOT NULL,
    WeekEndingDate  date              NOT NULL,
    Hours           decimal(6,2)      NOT NULL CONSTRAINT CK_Timesheets_Hours CHECK (Hours >= 0)
  );

  INSERT INTO dbo.Timesheets (EmployeeID, ClientID, TaskID, WeekEndingDate, Hours)
  VALUES (1002, 102, 11, CONVERT(date, '2025-02-02', 23), 40.00);

  COMMIT TRAN;
END TRY
BEGIN CATCH
  -- If the transaction is still valid, roll it back.
  IF XACT_STATE() <> 0
    ROLLBACK TRAN;

  -- Re-throw the original error with full context.
  THROW;
END CATCH;

Using savepoints inside a transaction

Savepoints let you undo part of a transaction without discarding everything. A common pattern is “attempt step 2, but if it fails, keep step 1.”

SET XACT_ABORT ON;

BEGIN TRY
  BEGIN TRAN;

  INSERT INTO dbo.Orders (OrderID, CustomerID, OrderAmount)
  VALUES (1, 101, 100.00);

  SAVE TRAN SavePoint1;

  INSERT INTO dbo.Orders (OrderID, CustomerID, OrderAmount)
  VALUES (2, 102, 200.00);

  -- Suppose we decide the second insert is not valid:
  ROLLBACK TRAN SavePoint1;

  -- Transaction is still open here; we can continue with different work...
  COMMIT TRAN;
END TRY
BEGIN CATCH
  IF XACT_STATE() <> 0
    ROLLBACK TRAN;
  THROW;
END CATCH;

Operational guidance

Keep transactions short. Do validation and user interaction outside the transaction whenever possible.
Be deliberate about isolation level. Higher isolation can increase blocking; row-versioning options can reduce reader/writer contention.
Never leave transactions open. An uncommitted transaction can cause widespread blocking and log growth.
Prefer structured error handling. Use TRY...CATCH, XACT_STATE(), and THROW rather than legacy @@ERROR chains.

The next lesson takes transactions a step further by showing how distributed transactions work across multiple servers.

Creating Transaction - Exercise

Click the Exercise link below to practice creating a transaction.
Creating Transaction - Exercise