DBS101 Unit7

Unit VII: Transactions, Concurrency & Recovery – Databases That Heal Themselves

📌 Introduction

This unit uncovers the superpowers that keep our data consistent, available, and durable — even when the system crashes, power goes out, or users mess up.

Welcome to the world of:

• ✅ Transactions (Lesson 18)
• 🔒 Concurrency Control (Lesson 19)
• 🔁 Database Recovery (Lesson 20)

---

🧾 Lesson 18: Transactions & ACID – Making Promises You Can Keep

🧩 What is a Transaction?

A transaction = a unit of work (like a bank transfer or an inventory update) that must either complete fully or not at all.

ACID Properties

Property	Purpose
Atomicity	All or nothing
Consistency	No data corruption
Isolation	No interference from other transactions
Durability	Changes survive crashes

💡 SQL Example:

BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE account_name = 'A';
UPDATE accounts SET balance = balance + 50 WHERE account_name = 'B';
COMMIT;

Storage Types That Support ACID

Type	Description
Volatile	RAM – fast but not crash-proof
Non-Volatile	Disks – durable but slower
Stable	Redundant, reliable storage (RAID)

💡 Durability relies on writing to stable storage before committing.

Transaction Lifecycle

State	What Happens
Active	Execution in progress
Partially Committed	Final operations done
Failed	Error occurred, can’t proceed
Aborted	Rolled back to safe state
Committed	Successfully completed, changes saved

Serializability — Order in the Chaos

To preserve consistency with multiple transactions, databases ensure serializability — the outcome is the same as if transactions were run one-by-one.

• ✅ Serial Schedule: T1 → T2
• ✅ Serializable: Looks like a serial execution
• ❌ Non-serial but inconsistent: Database disaster!

Conflict Serializability and Precedence Graphs

Use Precedence Graphs:

• Nodes = transactions
• Edge T1 ➝ T2 = T1 must come before T2 due to a conflict

• 🔁 No cycles = ✅ Serializable
• 🔄 Cycles = ❌ Not serializable

Recoverable vs. Cascadeless Schedules

Schedule Type	Rule
Recoverable	Don’t commit if you read uncommitted data
Cascadeless	Don’t even read uncommitted data!

Isolation Levels

Level	Isolation Strength
Serializable	Strongest – like serial
Repeatable Read	Stable reads, no phantom rows
Read Committed	Can’t read dirty data
Read Uncommitted	😱 May read dirty data

---

🔀 Lesson 19: Concurrency Control – Keeping Transactions from Colliding

🔐 Why Locks?

Locks help transactions access data safely and prevent conflicts:

• Shared (S): Read-only access
• Exclusive (X): Read + Write access

⚠️ Many S-locks allowed, only one X-lock!

Two-Phase Locking (2PL)

Phase	Rule
Growing	Acquire locks only
Shrinking	Release locks only

✅ Guarantees serializability ❌ May cause deadlocks

Deadlocks & Solutions

Strategy	Action
Detection	Use waits-for graph & pick a victim
Prevention	Use timestamp rules (Wait-Die, Wound-Wait)

Lock Granularity & Intention Locks

Level	Example
Tuple	Specific row
Page	Block of rows
Table	Entire table

🧠 Use IS/IX/SIX intention locks for hierarchy control.

Timestamp Ordering (TO)

• Assign timestamps to transactions
• Maintain order to ensure serializability
• Use Thomas Write Rule to reduce aborts

Multi-Version Concurrency Control (MVCC)

• DBMS keeps multiple versions of data items
• Readers see consistent snapshot
• Writers don’t block readers (and vice versa!)

🕰 Great for time-travel queries and snapshots

Snapshot Isolation (SI)

Every transaction sees a frozen version of the database from when it began.

⚠️ Beware of Write Skew Anomalies — two transactions update different values based on outdated shared state.

---

♻️ Lesson 20: Recovery – When Things Go Wrong, Databases Fight Back 💪

😱 Why Recovery Matters

• Power failures
• Crashes
• Transaction errors
• Natural disasters

✅ Transactions must be atomic and durable even after disaster!

Log-Based Recovery

Every change is recorded in a log BEFORE modifying the actual database.

Log Format Example	Meaning
<Ti, Xj, V1, V2>	Ti changed Xj from V1 to V2
<Ti start>	Ti started
<Ti commit>	Ti committed
<Ti abort>	Ti aborted

Undo & Redo

Operation	Action
Undo(Ti)	Rollback all changes using old values
Redo(Ti)	Reapply changes using new values

🔄 After a crash:

• Undo incomplete transactions
• Redo committed transactions

Checkpoints – Recovery Time Boosters

Type	Benefit
Standard	Snapshot of active transactions
Fuzzy	Doesn’t block transactions

✅ Reduces the log scanning needed during recovery

Log Record Buffering & WAL

• Logs are kept in memory temporarily
• Write-Ahead Logging (WAL) ensures log is written before data is updated

Steal & No-Force Policies

Policy	Description
Steal	Dirty data may be written before commit
No-Force	Committed data may not be written immediately

Recovery from Disk Failures

• Periodic full dumps to backup media
• Use dump + redo log to recover from complete disk loss

Remote Backup Systems

Technique	Use Case
Log Shipping	Send logs to remote replica
Hot-Spare Mode	Backup is nearly always ready
Distributed Systems	Multi-site redundancy

ARIES Algorithm (IBM)

A 3-phase recovery strategy:

1. 🔍 Analysis – What happened?
2. 🔁Redo – Replay history
3. ⏪ Undo – Rollback incomplete work

Uses STEAL + NO-FORCE policies and logs every step

Recovery in Main-Memory DBs

• Main memory = volatile → risk of loss
• Recovery must reload DB into memory and replay logs

✅ Optimizations:

• Skip redo for indexes
• Do recovery in parallel across cores

---

💡 What I’ve Learned

• 🔄 Transactions must be safe and consistent
• 🔐 Concurrency control is a balancing act
• 💾 Recovery = logs, checkpoints, ARIES
• 🌍 High availability = backup systems and replication

---

🌱 Reflection

From safe updates to post-crash recovery, this unit taught me that databases aren’t just about data — they’re about trust.

Transactions, locks, logs, and backups: The unsung heroes keeping our apps alive 💚

---

🔚 Conclusion

ACID, 2PL, ARIES, MVCC — they sound complex, but they all serve a simple purpose: Protect the data. Always. 🛡️

🎓 “A reliable system isn’t the one that never fails. It’s the one that knows how to recover.”