The Core Idea
Grouping Operations Into One Committable Unit
A transaction is a sequence of database operations bounded by BEGIN and either COMMIT (make all the changes permanent) or ROLLBACK (undo everything back to how it was before BEGIN, as if none of it happened). This is the concrete mechanism that makes ACID's atomicity guarantee real — without transactions, there'd be no way to bundle multiple operations into a single all-or-nothing unit at all.
Transactions matter most exactly when multiple related operations must succeed or fail together — like debiting one account and crediting another during a transfer — and when multiple transactions might run concurrently and need some defined behavior for how much of each other's in-progress work they're allowed to see.
💡 Memory Trick
Think of a transaction as writing in pencil first, then deciding whether to trace over it in permanent ink. BEGIN picks up the pencil. Every operation inside the transaction is drafted in pencil — visible to you, but not yet permanent. COMMIT traces the entire pencil draft over in ink, making it permanent all at once. ROLLBACK simply erases the pencil draft entirely, leaving the page exactly as it was before you started.
Isolation Levels
How Much Concurrent Transactions Can See of Each Other
1
Read Uncommitted — Weakest
A transaction can see another transaction's uncommitted (still in-progress, possibly about to be rolled back) changes — this allows 'dirty reads,' where you might read data that later turns out to have never actually happened, since the other transaction rolled back.
2
Read Committed
A transaction only ever sees changes from OTHER transactions that have already fully committed — eliminating dirty reads. However, if you read the same row twice within your own transaction, another transaction's commit in between could cause you to see different values each time (a 'non-repeatable read').
3
Repeatable Read
Guarantees that if you read the same row multiple times within one transaction, you'll see the same value every time, even if another transaction commits a change to that row in between — eliminating non-repeatable reads. However, a completely NEW row matching your query's criteria could still appear on a later read within the same transaction (a 'phantom read').
4
Serializable — Strongest
The strictest level: transactions behave completely as if they ran one at a time in some sequential order, with no overlap at all — eliminating dirty reads, non-repeatable reads, and phantom reads entirely. This strongest guarantee typically comes at the cost of the lowest concurrency and potential performance, since it may require blocking other transactions more aggressively.
The Trade-off
Stricter Isolation, Less Concurrency
Each isolation level up the list eliminates one more category of concurrency-related anomaly, but generally at the cost of more locking or blocking between concurrent transactions — meaning lower overall throughput under heavy concurrent load. This is a genuine trade-off, not a simple 'always pick the strictest option' decision: many applications deliberately choose 'Read Committed' (a common default) as an acceptable balance between correctness and performance for their specific workload.
Choosing the right isolation level requires understanding which specific anomalies your application can tolerate — a simple analytics dashboard reading approximate, slightly-stale counts might be perfectly fine with a weaker level, while a financial ledger recording exact transfers absolutely cannot tolerate dirty reads or phantom reads.
🖥️ Applied Scenario
You're implementing a seat-booking system for a concert venue, where two customers might try to book the exact same seat at nearly the same moment.
1
You wrap the 'check seat availability, then reserve it' logic in a single transaction, so the check-and-reserve happens as one atomic unit rather than two separate, interruptible steps.
2
You recognize that at Read Committed isolation, a race condition is still possible: both transactions could check availability (finding the seat open) before either one commits its reservation, resulting in a double-booking.
3
You choose Serializable isolation specifically for this booking transaction, ensuring the two concurrent booking attempts are forced to behave as if they ran one after another — the second one will correctly see the seat as already taken.
4
Conclusion: because double-booking a seat is a completely unacceptable outcome, the stricter (and slower) Serializable isolation level is the correct trade-off here, even though a less critical feature might reasonably accept a weaker, faster isolation level.
📌 Exam Application
Exam questions frequently ask you to define dirty reads, non-repeatable reads, and phantom reads precisely, and to identify which isolation levels prevent which of these specific anomalies. You may also be asked to recommend an appropriate isolation level for a described scenario, expecting you to weigh the specific correctness requirement against the concurrency/performance cost.
⚠️ Most Common Database Transactions Mistakes
The most common mistake is confusing non-repeatable reads (the SAME row's value changes between two reads within a transaction) with phantom reads (a NEW row appears matching your query's criteria between two reads) — both are eliminated at different isolation levels, and mixing them up is a frequently tested distinction. Another frequent error is assuming Serializable isolation should always be used 'to be safe' — its stronger blocking behavior can significantly reduce throughput under heavy concurrent load, so it should be a deliberate choice for cases where the specific anomaly it prevents is genuinely unacceptable, not a default for every transaction.
✓ Quick Self-Test
Can you define dirty reads, non-repeatable reads, and phantom reads, and state which isolation level first eliminates each one? Given a described application scenario, can you recommend an appropriate isolation level and justify the trade-off between correctness and concurrency?
Next Lesson
Stored Procedures and Triggers
→
← All Databases Lessons