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 →
