Transactions
Group multiple operations into atomic units with BEGIN, COMMIT, and ROLLBACK.
What you'll learn
- What a transaction is and why it matters
- BEGIN, COMMIT, and ROLLBACK syntax
- How transactions ensure atomicity and consistency
- Savepoints for partial rollbacks
- Common transaction isolation issues
Concept
What Is a Transaction?
A transaction is a sequence of database operations that are executed as a single unit — either all of them succeed, or none of them do. The classic example is a bank transfer: deducting $100 from account A and adding $100 to account B must both succeed. If the system crashes after the debit but before the credit, the money is lost. A transaction prevents this: the debit and credit are committed together or rolled back together.
BEGIN, COMMIT, ROLLBACK
BEGIN (or START TRANSACTION) starts a transaction. All subsequent statements are part of it until you COMMIT (make the changes permanent) or ROLLBACK (undo all changes since BEGIN). If the connection drops or the database crashes before COMMIT, the transaction is automatically rolled back.
Savepoints
A savepoint is a named marker within a transaction. You can roll back to a savepoint without rolling back the entire transaction. This is useful when you want to try an operation that might fail, and continue with other work if it does. SAVEPOINT sp1 ... ROLLBACK TO sp1.
Why Transactions Matter
Without transactions, a partial failure leaves the database in an inconsistent state. With transactions, the database guarantees that either all operations in the group are applied or none are — this is the atomicity property, the A in ACID. Transactions also provide isolation (concurrent transactions do not interfere) and durability (committed data survives crashes).
Autocommit
By default, most database drivers run in autocommit mode — every statement is its own transaction, committed immediately. To use explicit transactions, you either turn off autocommit or wrap statements in BEGIN ... COMMIT. In application code, use the driver's transaction API rather than sending raw BEGIN/COMMIT strings.
Example
-- A bank transfer as a transaction
BEGIN;
-- Deduct from source account
UPDATE accounts
SET balance = balance - 100.00
WHERE id = 1;
-- Credit to destination account
UPDATE accounts
SET balance = balance + 100.00
WHERE id = 2;
-- Verify both accounts exist and source has sufficient funds
-- (application code checks row count and balance here)
COMMIT;
-- If anything went wrong, run ROLLBACK instead
-- Using savepoints for partial recovery
BEGIN;
INSERT INTO orders (user_id, total) VALUES (1, 50.00);
SAVEPOINT after_order;
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (currval('orders_id_seq'), 100, 2);
-- If this fails, roll back to savepoint but keep the order
-- ROLLBACK TO after_order; -- uncomment if needed
COMMIT;
Two patterns: a bank transfer wrapped in BEGIN/COMMIT, and a transaction with a savepoint for partial rollback.
Try it
- SQL Formatter
Format transaction blocks with proper indentation.
- HTTP Request Tester
API endpoints often wrap database transactions — test them.
Common mistakes
The mistake
Forgetting to COMMIT or ROLLBACK, leaving a transaction open
The fix
Always close transactions with COMMIT or ROLLBACK. An open transaction holds locks and prevents other transactions from proceeding. Use try/finally in application code.
The mistake
Running a long transaction that locks many rows
The fix
Keep transactions short. Long-running transactions block other operations and can cause deadlocks. Do heavy computation outside the transaction.
Practice Transactions
8 exercises
Look up unfamiliar terms
A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.
Build real projects
Apply what you learned by building guided projects with starter code and solutions.
Decode error messages
Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.