The All-or-Nothing Analogy
Moving to a new apartment:
Without coordination:
- Moving truck arrives at old apartment
- You pack some boxes
- Truck breaks down halfway
- Half your stuff at old place, half in truck!
With coordination:
- Either EVERYTHING moves successfully
- Or NOTHING moves (stay at old place)
- No half-moved disaster
Database transactions work the same way. Either all operations complete, or none do.
What Is a Transaction?
Transaction = A group of operations treated as ONE unit
Example: Transfer money from Account A to Account B
1. Check A has enough money
2. Subtract the amount from A
3. Add the amount to B
All three must succeed, or none happen.
The Problem Without Transactions
Step 1: Subtract $100 from A ✓
[SYSTEM CRASH!]
Step 2: Add money to B ✗
Result: The transfer is inconsistent.
A lost money that B didn't receive.
The Solution With Transactions
BEGIN TRANSACTION
Step 1: Subtract money from A ✓
[SYSTEM CRASH!]
...system restarts...
ROLLBACK (undo Step 1)
Result: A still has their money.
Nothing was lost!
Transaction Commands
The Lifecycle
BEGIN TRANSACTION
-- Do stuff here
UPDATE...
INSERT...
DELETE...
COMMIT ← All operations made permanent
OR
ROLLBACK ← All operations undone
Automatic Rollback
Error during transaction?
Many databases mark the transaction as failed and require a rollback.
Some will automatically roll back when the connection ends.
Power failure?
On restart, the database recovers to the last committed state; uncommitted work is not applied.
ACID Properties
Transactions are designed to provide four properties (ACID). Exact behavior can vary by database and configuration:
A - Atomicity
"All or nothing"
Transaction with 5 operations:
Transaction with multiple operations:
Either ALL complete
Or NONE complete
Not left in a partial state.
C - Consistency
"Rules stay valid"
Before: Database follows all constraints
After: Database still follows all constraints
Example: Balance can't go negative.
Transaction that would cause negative = rejected.
I - Isolation
"No interference between transactions"
Transaction 1 running...
Transaction 2 running...
They don't see each other's uncommitted changes.
Each thinks it's running alone.
Note: "Isolation" has levels. Some anomalies are allowed at lower levels.
D - Durability
"Committed = durable"
Once COMMIT succeeds:
Power failure / crash? The database is designed to recover to a committed state.
Typically implemented via write-ahead logging and careful storage configuration.
Real-World Examples
E-commerce Order
BEGIN TRANSACTION
Reduce inventory by 1
Create order record
Process payment
Record an "email to send" / outbox entry
COMMIT
If payment fails → inventory restored, no order created.
Hotel + Flight Booking
BEGIN TRANSACTION
Reserve flight seat
Reserve hotel room
COMMIT
If hotel unavailable → flight not booked either.
No partial vacation bookings!
In practice, flight + hotel are often different systems. This becomes a distributed transaction problem, and many teams use sagas/compensation instead of a single DB transaction.
Banking Transfer
BEGIN TRANSACTION
Verify sender has funds
Deduct from sender
Add to receiver
Log the transaction
COMMIT
Crash at any point → rollback of uncommitted work.
Transaction Isolation Levels
Different levels of "how isolated":
| Level | See uncommitted? | Repeatable reads? | Phantom reads? |
|---|---|---|---|
| Read Uncommitted | Database-dependent (often treated like Read Committed) | Database-dependent | Database-dependent |
| Read Committed | No (in most databases) | Not ensured | Possible |
| Repeatable Read | No | Yes (typically) | Database-dependent |
| Serializable | No | Yes | Designed to prevent phantoms and other anomalies (may require retries) |
Why Different Levels?
Higher isolation = fewer anomalies but lower concurrency
Serializable: Strongest isolation, but may reduce concurrency and can fail with retryable serialization errors
Lower isolation = faster but riskier
Read Uncommitted: Fast, but might read garbage
Choose based on your needs:
Banking: often uses strong isolation or explicit locking
Analytics: Read Committed or snapshot-based reads are often fine
Common Problems Without Proper Isolation
Dirty Read
Transaction A writes data (not committed yet)
Transaction B reads that data
Transaction A rolls back
Transaction B saw data that didn't exist.
Lost Update
Both transactions read: balance = $100
Tx A adds money → balance increases
Tx B adds money → balance increases (but may overwrite Tx A)
Result: one update may be lost.
Non-Repeatable Read
Tx A reads a value
Tx B updates the value and commits
Tx A reads again and sees a different value
Same query, different result!
Best Practices
1. Keep Transactions Short
Long transaction = locks held longer
Other transactions wait
Performance suffers
Do only necessary operations inside transaction.
2. Handle Errors Properly
try:
begin_transaction()
# operations
commit()
except:
rollback()
Avoid leaving transactions open.
3. Choose Right Isolation Level
Most applications: Read Committed is enough
Financial: Serializable for safety
Analytics: Read Committed (or snapshot-based reads) is often enough
4. Avoid User Interaction Inside Transactions
Bad:
BEGIN TRANSACTION
... wait for user input ...
COMMIT
Transaction held for minutes = bad!
Common Mistakes
1. Forgetting to Commit/Rollback
Transaction started, not finished.
Locks held for a long time. Other operations blocked.
2. Too Large Transactions
Processing a very large batch in one transaction:
- Uses lots of memory
- Locks many rows
- Long recovery if it fails
Break into smaller batches.
3. Deadlocks
T1 holds lock on A, wants lock on B
T2 holds lock on B, wants lock on A
Both waiting forever!
Solution: Database detects and aborts one.
FAQ
Q: Do NoSQL databases have transactions?
Some do! MongoDB supports multi-document transactions. Redis has MULTI/EXEC. But many NoSQL prioritize speed over ACID.
Q: What happens during a transaction timeout?
Many systems roll back the uncommitted work. Exact behavior depends on the database and how the client/connection is handled.
Q: Can I have transactions across multiple databases?
Yes, but complex. Called "distributed transactions." Often replaced by Saga pattern.
Q: Transactions slow things down?
Slightly, due to locks and logging. But correctness > speed for financial data.
Summary
Transactions bundle database operations into all-or-nothing units, ensuring data integrity even during failures.
Key Takeaways:
- All operations complete or none do
- ACID: Atomicity, Consistency, Isolation, Durability
- BEGIN, COMMIT, ROLLBACK control flow
- Isolation levels trade safety for speed
- Keep transactions short
- Essential for financial, e-commerce, booking systems
Transactions are one reason your bank balance is less likely to end up in an inconsistent state.
Related Concepts
Leave a Comment
Comments (0)
Be the first to comment on this concept.
Comments are approved automatically.