Interactive Transaction Simulator
Scenario: Bank Transfer
Transfer $100 from Alice to Bob.
Database State (Disk Storage)
db_main.mdf*The Write-Ahead Log records intent before the disk is modified, ensuring Durability. Rollback logic ensures Atomicity.
Technical Deep Dive
A Atomicity
Treats all operations within a transaction as a single, indivisible unit. The transaction either completely succeeds (Commit) or completely fails (Rollback). There is no "halfway" state visible to the database.
Technical Implementation
Implemented using Undo Logs. If a crash occurs or an error is raised, the database manager reads the Undo Log to reverse any changes made by the active transaction, restoring the original state.
Transaction Lifecycle State Machine
Real World Analogy
A Vending Machine. If the chips get stuck, you get your money back. You never lose the money AND the chips.
C Consistency
Ensures the database transitions from one valid state to another valid state. It must follow all defined rules, constraints, and invariants (e.g., account balance cannot be negative, primary keys must be unique).
Technical Implementation
Enforced via schema constraints (Primary Key, Foreign Key, Check Constraints) and Triggers. If a transaction attempts data that violates a rule, the DB rolls it back.
Invariant Maintenance
Total money in system remains constant (Invariant)
Real World Analogy
A Jigsaw Puzzle. Every piece must fit perfectly. If you force a square peg in a round hole, the puzzle is invalid.
I Isolation
Ensures that concurrent transactions execute independently. One transaction shouldn't see the intermediate (dirty) data of another. The result should be the same as if they were executed serially (one after another).
Technical Implementation
Uses Locks (Shared/Exclusive) and MVCC (Multi-Version Concurrency Control). Levels range from Read Uncommitted (Risky) to Serializable (Safest, Slowest).
Interference Prevention (Locking)
Tx 2 is forced to wait until Tx 1 releases the lock.
Real World Analogy
A Fitting Room. Only one person can be inside at a time. Others must wait outside until the door unlocks.
D Durability
Guarantees that once a transaction is committed, it remains committed even in the event of a power loss, crash, or error. The changes are permanently written to non-volatile storage.
Technical Implementation
Relies on Write-Ahead Logging (WAL). The DB writes a log entry to disk before modifying the actual data pages. On restart, the DB "replays" the logs to restore committed data.
Write-Ahead Logging (WAL)
Data is secure as soon as it hits the Log (Step 1).
Real World Analogy
Stone Carving. Once you carve the letters into stone, they stay there. Rain (power loss) won't wash them away like ink on paper.
The "Ace the Interview" Cheat Sheet
Concise, impact-driven answers for when you're on the spot.
"Explain ACID properties like I'm 5 (or a Manager)."
"ACID is the safety net for data. Atomicity means no partial updates—it's all or nothing. Consistency means the data always follows the rules. Isolation keeps users from tripping over each other. Durability means once I say 'saved', it's saved forever, even if the power cuts."
"What happens if the server crashes right after I click 'Pay'?"
"This touches on Atomicity and Durability. If the crash happened before the commit was logged, the database restarts and sees an incomplete transaction in the logs, so it performs a Rollback (money returns to your account). If the crash happened after the commit was written to the log, the database uses the log to Replay the transaction, ensuring your payment goes through."
"Why don't we just use Serializable isolation all the time?"
"Performance. Serializable is the safest level, but it requires extensive locking (or validation), effectively making transactions wait in a single-file line. For high-traffic systems (like Facebook likes), we accept lower isolation (like Read Committed) to gain speed, while critical financial systems stick to higher isolation."