SQL Server Transactions and Isolation Levels | Beginner to Expert Series - Part 13

---

Microsoft SQL Server Tutorial Series: Beginner to Expert

Part 13: Transactions and Isolation Levels in SQL Server

Welcome to Part 13 of our SQL Server tutorial series! After learning advanced topics like Views, Triggers, and Stored Procedures, it's time to understand how SQL Server handles Transactions and Isolation Levels to maintain data integrity and concurrency.

---

In this tutorial, you’ll learn:

• What transactions are and why they matter
• ACID properties of transactions
• How to write transactions in SQL Server
• Different isolation levels and their effects
• Best practices for using transactions

---

🔄 What is a Transaction?

A transaction is a sequence of one or more SQL operations executed as a single unit of work. Transactions ensure that either all operations succeed together or none do, maintaining data integrity.

Think of a transaction like a bank transfer — you want the withdrawal and deposit steps to both complete or both fail.

---

⚙️ ACID Properties Explained

Property	Description
Atomicity	All steps in a transaction complete successfully, or none do.
Consistency	Transactions move the database from one valid state to another.
Isolation	Transactions execute independently without interference.
Durability	Once committed, changes are permanent, even if the system crashes.

---

✍️ Writing Transactions in SQL Server

Here's how you can write a simple transaction:

BEGIN TRANSACTION;

UPDATE Accounts
SET Balance = Balance - 100
WHERE AccountID = 1;

UPDATE Accounts
SET Balance = Balance + 100
WHERE AccountID = 2;

-- Check if both updates were successful
IF @@ERROR = 0
  COMMIT TRANSACTION;
ELSE
  ROLLBACK TRANSACTION;

This ensures the money transfer either fully completes or doesn't happen at all.

---

🛡️ Understanding Isolation Levels

Isolation levels control how transaction changes are visible to other concurrent transactions. SQL Server supports these main isolation levels:

Isolation Level	Description	Possible Issues
READ UNCOMMITTED	Allows dirty reads; transactions can read uncommitted data.	Dirty reads, non-repeatable reads, phantom reads
READ COMMITTED (Default)	Prevents dirty reads; only committed data is read.	Non-repeatable reads, phantom reads
REPEATABLE READ	Prevents dirty and non-repeatable reads.	Phantom reads
SERIALIZABLE	Highest isolation; prevents dirty, non-repeatable, and phantom reads.	Can cause blocking and reduce concurrency
SNAPSHOT	Reads data as it was at the start of the transaction (using versioning).	Reduces blocking; requires enabling in database

---

🔧 How to Set Isolation Level

Set the isolation level using this command before your transaction:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;

-- Your SQL operations here

COMMIT TRANSACTION;

Replace SERIALIZABLE with the desired level like READ COMMITTED, SNAPSHOT, etc.

---

💡 Best Practices for Transactions

• Keep transactions as short as possible to avoid locking resources.
• Avoid user interaction during transactions.
• Use appropriate isolation levels balancing data accuracy and performance.
• Always handle errors and rollback if needed.
• Test transactions in realistic concurrent environments.

---

🧾 Quick SQL Transaction Cheat Sheet

-- Set isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Start transaction
BEGIN TRANSACTION;

-- Your SQL statements
UPDATE TableName SET Column = Value WHERE Condition;

-- Commit if no error
IF @@ERROR = 0
    COMMIT TRANSACTION;
ELSE
    ROLLBACK TRANSACTION;
  

---

🌍 Real-World Use Case: Banking System

In a banking system, transactions are crucial to prevent errors like money disappearing or duplicated transfers. Using proper isolation levels ensures data consistency even when many users perform transactions simultaneously.

🖼️ Visual Diagram of Transaction Flow

 
[Start Transaction]
       |
       v
[Execute SQL Statements]
       |
       v
[Check for Errors?] --> Yes --> [ROLLBACK]
       |
       No
       |
       v
[COMMIT Transaction]

---

✅ Summary

• Transactions group SQL commands to execute atomically.
• ACID properties ensure reliability and consistency.
• Isolation levels control data visibility and concurrency effects.
• Choosing the right isolation level balances accuracy and performance.
• Proper error handling is vital in transaction management.

---

🔗 What’s Next?

next in Part 14: Indexes and Query Optimization — learn how to speed up your queries and improve database performance.

Meanwhile, catch up on earlier tutorials:

• Part 11: Stored Procedures and User-Defined Functions
• Part 12: Views and Triggers in SQL Server

---

Questions or feedback? Drop a comment below and share your learning journey! 🚀

← Previous Next →

Hands-On SQL & MongoDB Transaction Practice Task

🧪 Practice Task: Managing a Simple Fund Transfer System

---

Practical hands-on practice task for transactions and consistency, designed for both SQL and MongoDB. It reinforces the concepts from Part 10 and helps learners experience the role of transactions in maintaining data integrity.

---

This hands-on task builds on the concepts introduced in Part 10: SQL ACID Transactions vs NoSQL Transactions. We recommend reading it first if you haven’t already.

---

🎯 Objective:

Implement a basic fund transfer between two users and ensure data consistency using transactions in both SQL and MongoDB.

---

⚠️ Prerequisites & Environment Notes:

• Ensure your SQL database (e.g., MySQL with InnoDB or PostgreSQL) supports transactions.
• MongoDB version must be 4.0 or higher. Transactions require running in a replica set, even if it’s a single-node development setup.
• Ensure your accounts collection exists before starting the MongoDB transaction.

---

🔹 Part A: SQL Transaction Practice

🧱 Step 1: Create Tables

CREATE TABLE Accounts (
  AccountID INT PRIMARY KEY,
  Name VARCHAR(100),
  Balance DECIMAL(10, 2)
);

---

🧾 Step 2: Insert Initial Data

INSERT INTO Accounts (AccountID, Name, Balance) VALUES
(1, 'Aisha', 1000.00),
(2, 'Ravi', 500.00);

---

⚙️ Step 3: Write a Transaction

Simulate transferring ₹200 from Aisha to Ravi.

Your task:

• Deduct 200 from Aisha’s account

• Add 200 to Ravi’s account

• Use START TRANSACTION, COMMIT, and ROLLBACK

🔧 Expected Query Structure:

START TRANSACTION;

-- Step 1: Deduct from Aisha
UPDATE Accounts
SET Balance = Balance - 200
WHERE AccountID = 1;

-- Step 2: Add to Ravi
UPDATE Accounts
SET Balance = Balance + 200
WHERE AccountID = 2;

-- Commit if successful
COMMIT;

-- If something fails:
-- ROLLBACK;

---

📤 Expected Output:

Aisha: ₹800.00  
Ravi: ₹700.00

(₹ = Indian Rupees. You can substitute your local currency as needed like $ =Dollar or any other.)

If any part fails (e.g., insufficient balance), rollback should leave both balances unchanged.

---

🔹 Part B: MongoDB Transaction Practice

🧱 Step 1: Insert Accounts

db.accounts.insertMany([
  { _id: 1, name: "Aisha", balance: 1000 },
  { _id: 2, name: "Ravi", balance: 500 }
]);

---

⚙️ Step 2: Simulate a Transaction (Multi-Document)

Use a MongoDB session to transfer ₹200 from Aisha to Ravi.

🔧 Your task:

• Start a session

• Perform two updates inside a transaction

• Handle commit or abort

✅ Code Hint:

const session = db.getMongo().startSession();
session.startTransaction();

try {
  db.accounts.updateOne(
    { _id: 1 },
    { $inc: { balance: -200 } },
    { session }
  );

  db.accounts.updateOne(
    { _id: 2 },
    { $inc: { balance: 200 } },
    { session }
  );

  session.commitTransaction();
  print("Transaction committed.");
} catch (e) {
  session.abortTransaction();
  print("Transaction aborted:", e);
} finally {
  session.endSession();
}

---

📤 Expected Output:

Aisha: 800
Ravi: 700

If anything fails, neither balance should change.

---

💡 Bonus Challenge (Optional)

Enhance your transaction logic by adding a check to ensure the sender (Aisha) has sufficient balance before transferring funds.

• SQL: Add a condition or query to verify Balance >= 200 before proceeding with the deduction.
• MongoDB: Read Aisha’s balance within the session and only proceed if it’s sufficient.

This simulates a real-world scenario where overdrafts are not allowed.

---

🧠 Reflection Questions

1. What happens if you skip the commit in a SQL or MongoDB transaction?

2. Why is it important to use transactions for operations like fund transfers?

3. In MongoDB, what advantages and trade-offs do you notice with session-based transactions?

---

complete solutions to this task will be shared in the next post. Stay tuned! 

← Previous Next →

SQL ACID Transactions vs NoSQL Consistency Explained for Beginners

🔷 Part 10: Transactions and Consistency in SQL vs NoSQL

---

📍 Introduction

Ever wondered what happens if a system crashes in the middle of updating data?

That’s where transactions and consistency come in.

• In SQL, transactions follow ACID rules to keep data safe.

• In NoSQL, the focus shifts to eventual or tunable consistency, especially in distributed environments.

Let’s explore how both systems keep your data reliable — even during failures.

---

🔸 1. What is a Transaction?

A transaction is a group of operations performed as a single unit of work. Either all succeed, or none happen.

💡 Example:

Transferring money between accounts:

• Debit from one account

• Credit to another
  Both must succeed or be rolled back.

---

🔹 2. SQL Transactions & ACID Properties

SQL databases (MySQL, PostgreSQL, etc.) support ACID transactions:

Property	Description
A – Atomicity	All operations complete, or none do
C – Consistency	Data moves from one valid state to another
I – Isolation	Concurrent transactions don’t interfere
D – Durability	Once committed, changes are permanent

---

✅ SQL Example: Bank Transfer

START TRANSACTION;

UPDATE Accounts SET balance = balance - 500 WHERE account_id = 1;
UPDATE Accounts SET balance = balance + 500 WHERE account_id = 2;

COMMIT;

If something fails, use:

ROLLBACK;

📌 Ensures either both updates succeed, or none.

---

🔹 3. NoSQL Transactions and Consistency

Many NoSQL databases like MongoDB, Cassandra, or DynamoDB prioritize speed and availability, often relaxing strict consistency.

✅ MongoDB Transactions

MongoDB (since v4.0) supports multi-document ACID transactions — similar to SQL.

⚠️ Note: MongoDB transactions require a replica set or sharded cluster, even for local development. If you're using a standalone MongoDB instance, transactions won't work.

const session = db.getMongo().startSession();
session.startTransaction();

try {
  db.accounts.updateOne({ _id: 1 }, { $inc: { balance: -500 } }, { session });
  db.accounts.updateOne({ _id: 2 }, { $inc: { balance: 500 } }, { session });

  session.commitTransaction();
} catch (e) {
  session.abortTransaction();
}

---

📦 Eventual vs Strong Consistency

Model	Description
Strong Consistency	Always reads latest write (like SQL)
Eventual Consistency	Reads may return stale data temporarily
Tunable Consistency	Adjustable based on latency, consistency, etc.

Example: In Cassandra, you can choose consistency level per query (QUORUM, ONE, ALL).

This is called tunable consistency. It lets you adjust the trade-off between consistency, latency, and availability.

• ONE: Fastest, but may return stale data.
• QUORUM: Balanced – waits for responses from a majority of replicas.
• ALL: Strongest – waits for all replicas to respond, but slowest.

Choose based on your app's tolerance for stale data vs need for speed.

---

🧠 Comparison Table: SQL vs NoSQL Transactions

Feature	SQL (Relational DBs)	NoSQL (MongoDB, Cassandra, etc.)
Transactions	Built-in ACID	Varies by DB (Mongo supports it)
Consistency Model	Strong	Eventual or Tunable
Rollbacks	Yes	Some support (Mongo, DynamoDB)
Performance	Slower but safer	Faster, more scalable

---

📝 Summary

• SQL ensures strong consistency using ACID rules.

• NoSQL offers flexibility, often sacrificing consistency for speed and scale.

• Some NoSQL databases (like MongoDB, DynamoDB) now support full or partial transactions.

---

✅ What’s Next?

In Part 11, we’ll explore Database Security Basics — covering user roles, authentication, encryption, and common best practices in both SQL and NoSQL systems.

---

• click next forpractice task for transactions

← Previous Next →