Transactions and Rollbacks in SQL: Beginner-Friendly SQLite & Python Tutorial

Transactions and Rollbacks with SQL: A Simple and Fun Tutorial for Everyone

Welcome back to our magical SQL journey! In our previous tutorials, we learned how to manage data in a database using commands like INSERT, SELECT, and JOIN, and we connected tables in our toy store database. Now, let’s explore a super important concept: transactions and rollbacks. These are like a safety net for your database, ensuring your changes are saved only when you’re sure everything is perfect. We’ll use SQLite and Python with our toystore.db database, keeping it fun and simple like organizing a toy store with a magical undo button!

---

What are Transactions and Rollbacks?

Imagine you’re building a toy castle with blocks. You carefully add blocks one by one, but if one step goes wrong (like a block falls), you want to undo everything and start over to keep the castle perfect. In a database, a transaction is like a group of changes (like adding or updating data) that you want to happen all at once—or not at all. A rollback is like your undo button, letting you cancel those changes if something goes wrong.

For example, in our toy store, if a customer buys two toys, you need to update the toy inventory and record the sale. A transaction ensures both steps happen together, or neither happens if there’s a mistake. This keeps your database safe and accurate!

---

Why Learn Transactions and Rollbacks?

• They’re Safe: They protect your data from mistakes, like saving only half a sale.
• They’re Simple: Just a few commands make your database super reliable.
• They’re Useful: They’re used in apps, websites, and games to ensure data stays correct.
• They’re Fun: It’s like having a magic undo button for your database!
• They Build on SQL: If you know INSERT or UPDATE from our earlier tutorials, you’re ready to learn this.

Let’s dive into our toy store and learn how to use transactions and rollbacks!

---

Getting Started

We’ll use Python with SQLite to run our SQL commands, just like before. Make sure you have Python installed (download it from python.org if needed). SQLite comes with Python, so no extra setup is required. You can also use DB Browser for SQLite to see your data visually, but we’ll focus on Python code for clarity.

We’ll work with our toystore.db database, using the Toys table and a new Sales table to track toy purchases. Here’s the setup code to create these tables and add some sample data:

import sqlite3

# Connect to the database
conn = sqlite3.connect('toystore.db')
cursor = conn.cursor()

# Create Toys table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS Toys (
        ToyID INTEGER PRIMARY KEY,
        Name TEXT,
        Type TEXT,
        Price REAL,
        Stock INTEGER
    )
''')

# Create Sales table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS Sales (
        SaleID INTEGER PRIMARY KEY,
        ToyID INTEGER,
        Quantity INTEGER,
        TotalPrice REAL,
        FOREIGN KEY (ToyID) REFERENCES Toys(ToyID)
    )
''')

# Clear existing data to avoid duplicates
cursor.execute("DELETE FROM Toys")
cursor.execute("DELETE FROM Sales")

# Add toys
cursor.execute("INSERT INTO Toys (Name, Type, Price, Stock) VALUES ('Robot', 'Action Figure', 30.00, 10)")
cursor.execute("INSERT INTO Toys (Name, Type, Price, Stock) VALUES ('Jigsaw', 'Puzzle', 10.00, 15)")
cursor.execute("INSERT INTO Toys (Name, Type, Price, Stock) VALUES ('Teddy', 'Stuffed Animal', 15.00, 8)")

conn.commit()
conn.close()
print("Toy store database ready for transactions!")

What’s Happening?

• Toys table: Stores toy details with a new Stock column.
• Sales table: Records sales with ToyID, Quantity, and TotalPrice.
• FOREIGN KEY: Ensures data integrity by linking toys to sales.
• We added 3 toys with different stock levels.

Toys:

ToyID	Name	Type	Price	Stock
1	Robot	Action Figure	30.00	10
2	Jigsaw	Puzzle	10.00	15
3	Teddy	Stuffed Animal	15.00	8

---

What is a Transaction?

A transaction is a group of SQL commands that must all succeed together or not happen at all. It follows the ACID properties:

• Atomicity: All commands happen as one unit (all or nothing).
• Consistency: The database stays valid (e.g., stock doesn’t go negative).
• Isolation: Transactions don’t mess with each other.
• Durability: Saved changes stay saved, even if the computer crashes.

---

SQL Transaction Commands

• BEGIN TRANSACTION: Starts a transaction.
• COMMIT: Saves all changes.
• ROLLBACK: Cancels all changes if something fails.

In Python with SQLite, you can use conn.commit() and conn.rollback().

---

Example 1: A Successful Transaction

import sqlite3

conn = sqlite3.connect('toystore.db')
cursor = conn.cursor()

try:
    cursor.execute("UPDATE Toys SET Stock = Stock - 2 WHERE ToyID = 1")
    cursor.execute("INSERT INTO Sales (ToyID, Quantity, TotalPrice) VALUES (1, 2, 60.00)")
    
    conn.commit()
    print("Sale of 2 Robots completed successfully!")
    
    cursor.execute("SELECT * FROM Toys WHERE ToyID = 1")
    print("Robot Stock:", cursor.fetchone())
    cursor.execute("SELECT * FROM Sales")
    print("Sales:", cursor.fetchall())

except:
    conn.rollback()
    print("Something went wrong, changes undone!")

conn.close()

What’s Happening?

• try: Handles errors.
• UPDATE: Reduces Robot stock.
• INSERT: Adds the sale.
• commit(): Saves if successful.
• rollback(): Undoes if an error happens.

Sale of 2 Robots completed successfully!
Robot Stock: (1, 'Robot', 'Action Figure', 30.0, 8)
Sales: [(1, 1, 2, 60.0)]

---

Example 2: Rolling Back a Failed Transaction

import sqlite3

conn = sqlite3.connect('toystore.db')
cursor = conn.cursor()

try:
    cursor.execute("SELECT Stock FROM Toys WHERE ToyID = 2")
    stock = cursor.fetchone()[0]
    
    if stock < 20:
        raise Exception("Not enough Jigsaws in stock!")
    
    cursor.execute("UPDATE Toys SET Stock = Stock - 20 WHERE ToyID = 2")
    cursor.execute("INSERT INTO Sales (ToyID, Quantity, TotalPrice) VALUES (2, 20, 200.00)")
    
    conn.commit()
    print("Sale completed!")

except Exception as e:
    conn.rollback()
    print(f"Error: {e}. Changes undone!")

cursor.execute("SELECT * FROM Toys WHERE ToyID = 2")
print("Jigsaw Stock:", cursor.fetchone())
cursor.execute("SELECT * FROM Sales WHERE ToyID = 2")
print("Jigsaw Sales:", cursor.fetchall())

conn.close()

Error: Not enough Jigsaws in stock!. Changes undone!
Jigsaw Stock: (2, 'Jigsaw', 'Puzzle', 10.0, 15)
Jigsaw Sales: []

---

Example 3: Combining Transactions with Joins

First, create a Customers table:

conn = sqlite3.connect('toystore.db')
cursor = conn.cursor()

cursor.execute('''
    CREATE TABLE IF NOT EXISTS Customers (
        CustomerID INTEGER PRIMARY KEY,
        Name TEXT
    )
''')
cursor.execute("DELETE FROM Customers")
cursor.execute("INSERT INTO Customers (Name) VALUES ('Alice')")
conn.commit()
conn.close()

Now process a sale:

import sqlite3

conn = sqlite3.connect('toystore.db')
cursor = conn.cursor()

try:
    cursor.execute("UPDATE Toys SET Stock = Stock - 3 WHERE ToyID = 3")
    cursor.execute("INSERT INTO Sales (ToyID, Quantity, TotalPrice) VALUES (3, 3, 45.00)")
    
    conn.commit()
    print("Sale of 3 Teddies completed!")
    
    cursor.execute('''
        SELECT Customers.Name, Toys.Name, Toys.Stock
        FROM Sales
        INNER JOIN Toys ON Sales.ToyID = Toys.ToyID
        INNER JOIN Customers ON Customers.CustomerID = 1
        WHERE Sales.ToyID = 3
    ''')
    print("Sale Details:", cursor.fetchall())

except:
    conn.rollback()
    print("Sale failed, changes undone!")

conn.close()

Sale of 3 Teddies completed!
Sale Details: [('Alice', 'Teddy', 5)]

---

Tips for Success

1. Use Transactions when multiple commands must go together.
2. Check Conditions like stock before updating.
3. Test Rollbacks using intentional errors.
4. Write Clear Code for debugging and maintenance.
5. Practice with your own mini projects!

---

Common Questions

1. Are transactions hard?

No! Think of them as grouped commands with undo.

2. Do transactions work in other databases?

Yes! PostgreSQL, MySQL, and others support them.

3. What happens if I forget to commit?

Changes may be lost. Always call commit() explicitly.

4. Can I rollback after commit?

No, you must start a new transaction to reverse a change.

---

Wrapping Up

Transactions and rollbacks in SQL are like a magical safety net, ensuring your database changes are all-or-nothing. In this tutorial, we used transactions to process toy sales, rolled back a failed sale, and combined transactions with joins in our toystore.db database. Whether you’re a beginner or an experienced coder, transactions are a fun and essential skill for keeping data safe.

Try creating your own database for something cool, like a game or library, and practice transactions. Use DB Browser for SQLite to see your data or keep coding in Python. With transactions and rollbacks, you’re now a database superhero, keeping your data safe and sound!

Happy SQL adventures, and keep your data secure!

← Previous Next →

Here's the complete solution to the Practice Task on Transactions and Consistency, for both SQL and MongoDB, including full code, output, and explanations.

---

✅ Complete Solution: Fund Transfer with Transactions

---

🔹 Part A: SQL Solution

---

📁 1. Table Creation

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

---

📥 2. Insert Sample Data

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

---

🔁 3. Transaction Script

START TRANSACTION;

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

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

-- Final step: Commit the transaction
COMMIT;

📌 If an error occurs during either update, you would use:

ROLLBACK;

---

📤 Expected Output (after COMMIT):

SELECT * FROM Accounts;

AccountID	Name	Balance
1	Aisha	800.00
2	Ravi	700.00

✅ Data is consistent and correct. Both updates were applied atomically.

---

🧪 Optional Test Failure Scenario

Try this (invalid ID):

START TRANSACTION;

UPDATE Accounts SET Balance = Balance - 200 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 200 WHERE AccountID = 999; -- wrong ID

ROLLBACK;

🔁 This would rollback everything. Aisha’s balance remains unchanged.

---

🔹 Part B: MongoDB Solution

---

📥 1. Insert Documents

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

---

🔁 2. MongoDB Transaction with Session

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

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

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

  // Commit the transaction
  session.commitTransaction();
  print("✅ Transaction committed.");
} catch (error) {
  session.abortTransaction();
  print("❌ Transaction aborted:", error.message);
} finally {
  session.endSession();
}

---

📤 Expected Output After Querying:

db.accounts.find().pretty();

[
  { _id: 1, name: "Aisha", balance: 800 },
  { _id: 2, name: "Ravi", balance: 700 }
]

✅ Like in SQL, both operations succeeded together.

---

🔥 Test Failure Scenario in MongoDB

Simulate a failure by modifying the second update with an invalid field:

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

➡️ MongoDB will throw an error, and the first update will be rolled back automatically when abortTransaction() is called.

---

🧠 Final Thoughts

Operation	SQL	MongoDB
Begin Transaction	START TRANSACTION	session.startTransaction()
Commit	COMMIT	session.commitTransaction()
Rollback	ROLLBACK	session.abortTransaction()
Atomicity	Built-in	Requires session/replica set
Use Case	High integrity systems (e.g. banks)	NoSQL with transactional guarantees (MongoDB 4.0+)

---

Part 11: Database Security Basics

🚀 In the next part of this tutorial series, we’ll shift focus to Database Security Basics. You'll learn about user roles, privileges, authentication, and how to protect your data from unauthorized access—an essential step for any real-world application.

---

👍 If you found this tutorial helpful, consider following to the blog or sharing it with a friend or teammate learning databases.

💬 Got questions or a better approach? Drop a comment below—I’d love to hear your thoughts!

🔁 Ready for more? Check out the next post on Database Security →

← 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 →