Transactions — BEGIN TRANSACTION, COMMIT, ROLLBACK and TRY/CATCH

SQL Server transactions group multiple statements into an atomic unit — either all succeed (COMMIT) or all are rolled back (ROLLBACK). For the BlogApp, publishing a post involves updating the post status, setting the published timestamp, and potentially updating denormalised counters — all of which must succeed together or not at all. The TRY/CATCH pattern in T-SQL is the correct way to handle errors inside stored procedures and multi-statement scripts, ensuring transactions are never left open.

Transaction Patterns #

-- ── Basic transaction ──────────────────────────────────────────────────────
BEGIN TRANSACTION;

    UPDATE dbo.Posts
    SET    IsPublished = 1,
           PublishedAt = SYSUTCDATETIME(),
           Status      = 'published',
           UpdatedAt   = SYSUTCDATETIME()
    WHERE  Id = 42;

    -- Also update the author's published post count
    UPDATE dbo.Users
    SET    PublishedPostCount = PublishedPostCount + 1
    WHERE  Id = (SELECT AuthorId FROM dbo.Posts WHERE Id = 42);

COMMIT TRANSACTION;

-- ── TRY/CATCH pattern — required for safe multi-statement operations ───────
BEGIN TRY
    BEGIN TRANSACTION;

        INSERT INTO dbo.Posts (AuthorId, Title, Slug, Body, Status)
        VALUES (N'user-123', N'New Post', 'new-post', N'Content', 'draft');

        DECLARE @PostId INT = SCOPE_IDENTITY();

        INSERT INTO dbo.PostTags (PostId, TagId)
        SELECT @PostId, Id
        FROM   dbo.Tags
        WHERE  Slug IN ('dotnet', 'csharp');

    COMMIT TRANSACTION;
    PRINT 'Transaction committed successfully. Post ID: ' + CAST(@PostId AS VARCHAR);

END TRY
BEGIN CATCH
    -- Check if a transaction is open before rolling back
    IF XACT_STATE() <> 0
        ROLLBACK TRANSACTION;

    -- Re-raise the error with details
    DECLARE @ErrorMsg  NVARCHAR(4000) = ERROR_MESSAGE();
    DECLARE @ErrorLine INT            = ERROR_LINE();
    RAISERROR('Error at line %d: %s', 16, 1, @ErrorLine, @ErrorMsg);
END CATCH;

-- ── XACT_ABORT — automatic rollback on any error ─────────────────────────
-- SET XACT_ABORT ON at the top of a script or stored procedure:
-- If ANY statement fails, the entire transaction is automatically rolled back
-- Avoids the need to manually check and rollback in CATCH
SET XACT_ABORT ON;
BEGIN TRANSACTION;
    -- Any error here automatically rolls back the entire transaction
    UPDATE dbo.Posts SET IsPublished = 1 WHERE Id = 42;
    UPDATE dbo.Users SET PublishedPostCount = PublishedPostCount + 1
    WHERE  Id = (SELECT AuthorId FROM dbo.Posts WHERE Id = 42);
COMMIT TRANSACTION;
SET XACT_ABORT OFF;  -- reset after the block

-- ── XACT_STATE() values ───────────────────────────────────────────────────
--  1: active, committable transaction
--  0: no active transaction
-- -1: active, uncommittable (doomed) transaction — MUST ROLLBACK
-- In CATCH: IF XACT_STATE() <> 0 ROLLBACK TRANSACTION handles both 1 and -1

Transaction Isolation Levels #

-- ── Isolation levels (most common) ────────────────────────────────────────
-- READ COMMITTED (default): reads only committed data
--   → No dirty reads; may see different values on repeated reads (non-repeatable reads)
-- READ UNCOMMITTED:          reads uncommitted data (dirty reads possible)
--   → Fastest but potentially sees incomplete transactions; use for approximate counts
-- SNAPSHOT:                  each statement sees a consistent snapshot
--   → No blocking reads; requires enabling READ_COMMITTED_SNAPSHOT on the database
-- SERIALIZABLE:              strictest; prevents all anomalies but most blocking

-- Enable RCSI (recommended for high-concurrency apps):
-- ALTER DATABASE BlogApp SET READ_COMMITTED_SNAPSHOT ON;

Common Mistakes #

Mistake 1 — Not checking XACT_STATE() in CATCH block (uncommitted transaction leaked) #

❌ Wrong — CATCH BEGIN ROLLBACK TRANSACTION END without checking state; throws error if no active transaction.

✅ Correct — IF XACT_STATE() <> 0 ROLLBACK TRANSACTION safely handles all cases.

Mistake 2 — Long-running transactions holding locks (blocks other users) #

❌ Wrong — transaction includes an HTTP call or file I/O; locks held for seconds while waiting for external system.

✅ Correct — complete all database work within the transaction, commit, then perform external I/O.