Let’s break down Transactions and Concurrency into its three major parts: ACID properties, Isolation levels, and Locking & Deadlocks—in simple terms with relatable examples.
✅ 1. ACID Properties
ACID is a set of four key properties that ensure reliable processing of database transactions.
Property | Meaning | Example |
|---|---|---|
A - Atomicity | All steps in a transaction happen, or none at all. | If you transfer ₹100 from Account A to B, and debit from A succeeds but credit to B fails, the debit is rolled back. |
C - Consistency | A transaction moves the database from one valid state to another. | If a transaction violates a constraint (like negative balance), it’s not allowed. |
I - Isolation | Transactions run independently, even if at the same time. | Two users booking the last seat should not both succeed—only one should. |
D - Durability | Once a transaction is committed, it won’t be lost—even if power fails. | After your bank transfer is confirmed, the amount won't reverse due to a crash. |
🔁 2. Isolation Levels
Isolation defines how visible one transaction's changes are to others before it's completed. Lower isolation = more performance, but higher risk of data issues.
Isolation Level | Issues Prevented | Allowed Issues | Use Case |
|---|---|---|---|
Read Uncommitted | None | Dirty Reads | Fast reporting, risky |
Read Committed | Dirty Reads | Non-repeatable Reads | Most DBMS default |
Repeatable Read | Dirty + Non-repeatable Reads | Phantom Reads | Bank account balance check |
Serializable | All issues | None | Highest safety, lowest speed |
Problem Types:
-
Dirty Read: Reading data from an uncommitted transaction.
-
Non-repeatable Read: Data read once, then changed by another transaction.
-
Phantom Read: Rows added/removed by another transaction during yours.
🔐 3. Locking and Deadlocks
🔒 Locking
Locks prevent multiple transactions from modifying the same data simultaneously.
-
Shared Lock (Read Lock): Others can read but not write.
-
Exclusive Lock (Write Lock): No one else can read or write.
Example:
Transaction A reads a record (shared lock), while Transaction B tries to update it—B must wait.
⚠️ Deadlocks
A deadlock happens when two transactions are waiting on each other forever.
Example:
-
T1 locks Row 1, wants Row 2.
-
T2 locks Row 2, wants Row 1.
-
They both wait—forever.
How DBMS handles it:
-
Automatically detects deadlocks.
-
Kills one transaction (called a victim) and rolls it back.
💡 Summary
Concept | Purpose |
|---|---|
ACID | Guarantees safe, reliable transactions |
Isolation Levels | Controls how transactions interact |
Locks | Prevent data clashes |
Deadlocks | Special problem from poor locking logic |
Let me know if you'd like visuals, SQL examples, or implementation with a database like MySQL or PostgreSQL!