Lesson 13 • Expert
Transactions & ACID
Guarantee data integrity with transactions — the mechanism that prevents data corruption in every serious application.
🎯 What You'll Learn
- BEGIN, COMMIT, and ROLLBACK
- ACID properties: Atomicity, Consistency, Isolation, Durability
- SAVEPOINTs for partial rollback
- Isolation levels and concurrency control
What Are Transactions?
A transaction groups multiple SQL statements into one atomic operation. Either all statements succeed (COMMIT) or all are undone (ROLLBACK). No half-finished states.
🏦 Real-World Analogy
A bank transfer moves $500 from Account A to Account B. This requires two updates: subtract from A, add to B. If the system crashes after subtracting but before adding — $500 vanishes! Transactions prevent this: both updates happen, or neither does.
Basic Transactions
Wrap multiple operations in BEGIN/COMMIT for safety
-- Basic transaction: bank transfer
BEGIN TRANSACTION;
-- Debit sender
UPDATE accounts SET balance = balance - 500
WHERE account_id = 1001;
-- Credit receiver
UPDATE accounts SET balance = balance + 500
WHERE account_id = 1002;
-- Both succeed or both fail — that's atomicity!
COMMIT;
-- If something goes wrong:
-- ROLLBACK; -- undoes everything since BEGINACID Properties
🔒 Atomicity — "All or Nothing"
Every statement in the transaction succeeds, or everything is rolled back. No partial updates.
✅ Consistency — "Rules Are Enforced"
The database moves from one valid state to another. Constraints, foreign keys, and triggers are all respected.
🔀 Isolation — "Transactions Don't Interfere"
Concurrent transactions behave as if they ran one at a time. Your transaction doesn't see another's uncommitted changes.
💾 Durability — "Commits Are Permanent"
Once committed, data survives power outages, crashes, and hardware failures. It's written to disk.
Transaction with Error Handling
Handle failures gracefully within a transaction
-- Transaction with error handling (PostgreSQL)
BEGIN;
-- Step 1: Create a new order
INSERT INTO orders (customer_id, order_date, total)
VALUES (42, CURRENT_DATE, 299.99);
-- Step 2: Update inventory
UPDATE products SET stock = stock - 1
WHERE product_id = 101 AND stock > 0;
-- Step 3: Check if inventory was available
-- If no rows updated, stock was 0 → rollback
-- In application code:
-- if (rows_affected == 0) ROLLBACK;
-- else COMMIT;
COMMIT;SAVEPOINTs
Partially roll back within a transaction
-- SAVEPOINT: partial rollback within a transaction
BEGIN TRANSACTION;
INSERT INTO orders (customer_id, total)
VALUES (1, 100.00);
SAVEPOINT after_order;
INSERT INTO order_items (order_id, product_id, qty)
VALUES (LAST_INSERT_ID(), 999, 1);
-- Oops, product 999 doesn't exist!
ROLLBACK TO SAVEPOINT after_order;
-- Order is still saved, only the item is undone
INSERT INTO order_items (order_id, product_id, qty)
VALUES (LAST_INSERT_ID(), 101, 1);
-- This product exists — success!
COMMIT;Isolation Levels
Isolation levels control what one transaction can see while another is running:
| Level | Dirty Read | Non-Repeatable | Phantom |
|---|---|---|---|
| READ UNCOMMITTED | ✅ Yes | ✅ Yes | ✅ Yes |
| READ COMMITTED | ❌ No | ✅ Yes | ✅ Yes |
| REPEATABLE READ | ❌ No | ❌ No | ✅ Yes |
| SERIALIZABLE | ❌ No | ❌ No | ❌ No |
💡 Pro Tip
READ COMMITTED is the default and sufficient for most applications. Only use SERIALIZABLE when you absolutely need it — it's slower due to locking.
Isolation Levels
Control concurrency behavior between transactions
-- Isolation levels control what concurrent
-- transactions can see
-- READ UNCOMMITTED: see uncommitted changes (dirty reads)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- READ COMMITTED: only see committed data (default in most DBs)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- REPEATABLE READ: same query returns same results
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- SERIALIZABLE: strictest — transactions run as if sequential
SET TRANSACTION ISOLATION LEVEL SERIALIZAB
...📘 Quick Reference
| Command | Purpose |
|---|---|
| BEGIN / START TRANSACTION | Start a transaction |
| COMMIT | Save all changes permanently |
| ROLLBACK | Undo all changes since BEGIN |
| SAVEPOINT name | Create a checkpoint |
| ROLLBACK TO SAVEPOINT name | Undo to checkpoint |
🎉 Lesson Complete!
You now understand transactions and ACID — critical for building reliable applications. Next, you'll master advanced queries with window functions and CTEs!
Sign up for free to track which lessons you've completed and get learning reminders.