⭐ Mastering SQL Transactions — A Complete 360° Guide with Examples, Modes, Errors, TRY…CATCH, Deadlocks, Isolation Levels & Real Scenarios

 Transactions are the backbone of data integrity in SQL Server. They ensure your data stays accurate even when operations fail, systems crash, or multiple users modify data at the same time.

But when should we explicitly use them? What happens if we don’t? How does SQL Server behave internally?

This 360-degree guide explains SQL transactions through:

• Auto-commit vs Explicit transactions

• Meaningful real examples

• Errors + rollback scenarios

• TRY…CATCH

• XACT_ABORT

• Deadlocks

• Isolation levels

• Flowcharts and diagrams

At the end, you’ll be able to confidently decide when, why, and how to use transactions.

---

🔵 1. Understanding SQL Transaction Modes

SQL Server supports three modes:

Mode	Behaviour
Auto-Commit	Default. Every statement is a transaction.
Implicit Transactions	SQL starts a transaction automatically until you commit.
Explicit Transactions	Developer manually controls BEGIN / COMMIT / ROLLBACK.

In this article, we will focus on:

• Auto-commit

• Explicit transactions

These are the modes used 99% of the time in real systems.

---

🔵 2. Auto-Commit Mode (Default Mode)

Every INSERT, UPDATE, or DELETE is automatically committed.

Let’s create two simple tables:

CREATE TABLE dbo.Checking
(
    ID INT NOT NULL,
    Amount DECIMAL(19, 2) NOT NULL,
    ShortDescription NVARCHAR(100) NOT NULL,
    TransactionDate DATE NOT NULL
);
GO
CREATE TABLE dbo.Savings
(
    ID INT NOT NULL,
    Amount DECIMAL(19, 2) NOT NULL,
    ShortDescription NVARCHAR(100) NOT NULL,
    TransactionDate DATE NOT NULL
);
GO

Insert example data:

INSERT INTO dbo.Checking
(
    ID,
    Amount,
    ShortDescription,
    TransactionDate
)
VALUES
(1, 25.00, 'Starting my checking account', GETDATE());
INSERT INTO dbo.Savings
(
    ID,
    Amount,
    ShortDescription,
    TransactionDate
)
VALUES
(1, 100.00, 'Starting my savings account, Thanks dad!', GETDATE());
SELECT SUM(Amount) AS [Total], -- SELECT Statement
       'Savings' AS [AccountType]
FROM dbo.Savings
UNION ALL
SELECT SUM(Amount) AS [Total],
       'Checking' AS [AccountType]
FROM dbo.Checking;
GO

✔ Output

Table	Amount
Savings	100
Checking	25

Auto-commit is simple — you issue the statement, SQL Server commits it.

---

🔵 3. The Problem With Auto-Commit (Real Example)

We now simulate a small real banking operation:

Attempt to move ₹100 from Savings → Checking

INSERT INTO dbo.Savings
VALUES (2, -100.00, 'Taking money from savings', GETDATE());

INSERT INTO dbo.Checking
VALUES (2, 100.00,  'Taking money out of my account for a copy of a new NES game, sorry dad! I will replace it after I get a job.'
, GETDATE());

❌ The second insert fails

(because the message is too long or violates a rule)

But the first insert already succeeded, and auto-commit cannot undo it.

SELECT SUM(Amount) AS [Total], -- SELECT Statement
       'Savings' AS [AccountType]
FROM dbo.Savings
UNION ALL
SELECT SUM(Amount) AS [Total],
       'Checking' AS [AccountType]
FROM dbo.Checking;
GO

✔ Result: Bad Data

ID	Total
1	0.00 ❌ (should not happen)

2

25.00

This is the main weakness of auto-commit.

---

🔵 4. Explicit Transactions: The Fix

Explicit transactions allow us to group multiple steps into one logical unit.

Syntax:

BEGIN TRANSACTION;
-- operations
COMMIT;        -- save changes
-- or
ROLLBACK;      -- undo all changes

---

🔵 5. Real Example: Fixing the Savings → Checking Transfer

We run the exact same operation using a transaction:

When we use XACT_ABORT ON keyword, we no need write rollback, it automatically rollback the transaction when it fails, below is the code

SET XACT_ABORT ON;
BEGIN TRANSACTION;

INSERT INTO dbo.Savings
VALUES (2, -100.00, 'Taking money out of savings', GETDATE());

INSERT INTO dbo.Checking
VALUES (2, 100.00, 'Taking money out of my account for a copy of a new NES games, sorry dad! I will replace it after I get a job.', GETDATE());

COMMIT TRANSACTION;

✔ Behaviour

If any statement fails, SQL Server automatically:

• Rolls back

• Restores all data

• Maintains integrity

SELECT SUM(Amount) AS [Total], -- SELECT Statement
       'Savings' AS [AccountType]
FROM dbo.Savings
UNION ALL
SELECT SUM(Amount) AS [Total],
       'Checking' AS [AccountType]
FROM dbo.Checking;
GO

✔ Result (Correct)

This example is the core reason to use explicit transactions.

---

🔵 6. Using TRY…CATCH for Better Safety

Best practice in SQL Server:

BEGIN TRY
    BEGIN TRANSACTION;

    INSERT INTO dbo.Savings VALUES (3, -200, 'Transfer', GETDATE());
    INSERT INTO dbo.Checking VALUES (3, 200, 'Transfer', GETDATE());

    COMMIT;
END TRY
BEGIN CATCH
    ROLLBACK;
    PRINT 'Transaction Failed: ' + ERROR_MESSAGE();
END CATCH;

This ensures:

• Partial inserts don’t occur

• Errors are logged

• Data stays consistent

---

🔵 7. Understanding Deadlocks (Simple Visual Explanation)

A deadlock happens when:

• Transaction A waits for resources held by B

• Transaction B waits for resources held by A

Result → SQL Server kills one transaction

---

🔶 Deadlock Diagram

---

Real Example

Session A

BEGIN TRAN;
UPDATE Checking SET Amount = Amount - 50 WHERE ID = 1;
WAITFOR DELAY '00:00:10';
UPDATE Savings SET Amount = Amount + 50 WHERE ID = 1;
COMMIT;

Session B

BEGIN TRAN;
UPDATE Savings SET Amount = Amount - 20 WHERE ID = 1;
WAITFOR DELAY '00:00:10';
UPDATE Checking SET Amount = Amount + 20 WHERE ID = 1;
COMMIT;

💥 Deadlock occurs

✔ Deadlock Fix

Always update tables in the same order:

UPDATE Checking ...
UPDATE Savings ...

This prevents circular waits.

---

🔵 8. Understanding Isolation Levels (With Diagrams)

Isolation levels control how transactions interact.

---

8.1 READ UNCOMMITTED (Dirty Reads Allowed)

---

8.2 READ COMMITTED (Default Level – No Dirty Reads)

---

8.3 REPEATABLE READ (Prevents Non-Repeatable Reads)

---

8.4 SERIALIZABLE (Strictest — Fully Locked)

---

🔵 9. Flowchart: How SQL Transaction Works

---

🔵 10. Putting It All Together (360° View)

✔ When Auto-Commit Is Enough

• Simple INSERT of one row

• Logging data

• Background audit entries

✔ When You MUST Use Explicit Transactions

• Multi-step financial operations

• Inventory deductions

• Any operation affecting multiple tables

• Critical business logic

• Data migrations

✔ Why TRY…CATCH Matters

• Prevents partial changes

• Enables clean rollback

• Improves reliability

✔ Why XACT_ABORT Is Useful

• Automatically rolls back on runtime errors

• Prevents stuck transactions

✔ Deadlock Awareness

• Happens naturally under concurrency

• Ordering and proper indexing greatly reduce them

✔ Isolation Levels Matter

• They control concurrency vs. accuracy

• Choose based on system needs

---

⭐ Final Conclusion

SQL transactions are not just a feature — they are a safety system.
They ensure:

• Accuracy

• Consistency

• Reliability

• Protection from partial failures

Understanding auto-commit vs explicit, using TRY…CATCH, handling deadlocks, and choosing the right isolation level gives you complete control over data integrity.

You now have a full 360-degree understanding of SQL transactions.

Read More