🔷 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
No comments:
Post a Comment