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 →

Learn Python

Menu