Lesson 18 of 20
In progress
Transactions and concurrency
Group statements so they succeed or fail together, use savepoints, and understand what isolation protects you from.
A transaction is a promise: either every statement inside it takes effect, or none does. That promise is what makes multi-step changes safe when something fails halfway.
What you will learn
- Wrap a group of statements in
BEGINandCOMMIT. - Undo part of a transaction with a savepoint.
- Know what the isolation levels actually prevent.
The basic shape
Editable. Runs in your browser.
INSERT INTO accounts (id, owner, balance_paise) VALUES
(1, 'Ana', 500000),
(2, 'Ravi', 100000);
BEGIN;
UPDATE accounts SET balance_paise = balance_paise – 25000 WHERE id = 1;
UPDATE accounts SET balance_paise = balance_paise + 25000 WHERE id = 2;
COMMIT;
SELECT id, owner, balance_paise FROM accounts;
Press Run to see the output.
Two statements, one unit. If the second fails — a constraint, a crash, a timeout — the first is undone as well, and the total balance never changes.
Without the transaction, a failure between the two statements leaves money that exists nowhere. That is the classic reason transactions exist, and it applies to any pair of writes that must agree.
ROLLBACK
Editable. Runs in your browser.
SELECT balance_paise FROM accounts WHERE id = 1;
Press Run to see the output.
The first SELECT sees zero, because the change is visible inside its own transaction. After ROLLBACK the balance is back. This is how you test a destructive statement: run it, look, then decide.
Errors and automatic aborts
In PostgreSQL, any error inside a transaction aborts it; later statements fail until you ROLLBACK. In SQLite, most errors abort only the statement, and the transaction stays open. Application code must know which behaviour it is dealing with, because "continue after the error" is safe in one and meaningless in the other.
The reliable pattern is the same either way: catch the error, ROLLBACK, report failure. Do not attempt to continue a transaction whose earlier statement failed.
Savepoints
Editable. Runs in your browser.
INSERT INTO accounts (id, owner, balance_paise) VALUES (3, 'Meera', 50000);
SAVEPOINT before_bonus;
UPDATE accounts SET balance_paise = balance_paise + 10000 WHERE id = 3;
ROLLBACK TO before_bonus;
COMMIT;
SELECT id, owner, balance_paise FROM accounts WHERE id = 3;
Press Run to see the output.
The insert survives, the bonus does not. A savepoint marks a point you can return to without discarding the whole transaction, which is useful for optional steps: try the bonus, and if a rule rejects it, keep the account.
What isolation means
Two transactions running at once can interfere. Isolation levels describe how much of that interference the database hides.
- Read uncommitted lets a transaction see another’s uncommitted rows. Rarely offered, never wanted.
- Read committed sees only committed data, but the same row can change between two reads in one transaction.
- Repeatable read keeps rows stable for the transaction’s duration.
- Serializable makes concurrent transactions behave as if they ran one after another.
Higher isolation prevents more anomalies and costs more concurrency. Most applications run at read committed and handle the remaining races with constraints and explicit locks.
SQLite takes a different approach: a write transaction locks the whole database. That sounds crude and works well because SQLite is used by one process, often with write-ahead logging enabled so readers do not block the writer.
Editable. Runs in your browser.
Press Run to see the output.
WAL lets readers continue while a writer works, which is a large improvement for a local app with a few connections.
Keeping transactions short
A transaction holds locks. A long one blocks other writers, grows the journal, and makes a crash recovery more expensive.
Keep the transaction around the writes, not around the user. Do not open a transaction, wait for a form submission, then commit: read what you need, compute in the application, and write in one short transaction. For a queue-like workflow, use a status column updated in a small transaction instead of a long-held lock.
Retries on busy
When two writers collide, one may receive a busy or deadlock error. That is expected, not a bug. Wrap the transaction in a small retry loop with a short delay, limit the attempts, and make sure the transaction is idempotent or rolled back before retrying.
Leaving a transaction open when an exception escapes. The connection keeps the locks, other writers stall, and the fix requires restarting the process. Always roll back in a finally-style block.
Write a transaction that moves 10,000 paise from account 2 to account 1, checks that neither balance goes negative, and rolls back instead of committing if it would.
Show solution
Editable. Runs in your browser.
UPDATE accounts SET balance_paise = balance_paise – 10000 WHERE id = 2;
UPDATE accounts SET balance_paise = balance_paise + 10000 WHERE id = 1;
SELECT COUNT(*) AS overdrawn FROM accounts WHERE balance_paise < 0;
ROLLBACK;
SELECT id, balance_paise FROM accounts ORDER BY id;
Press Run to see the output.
The CHECK constraint would reject a negative balance anyway, which aborts the statement. The explicit check makes the intent visible, and rolling back leaves the demonstration data unchanged. In real code, COMMIT replaces the final ROLLBACK when the check passes.
Recap
BEGIN,COMMITandROLLBACKmake a group of statements atomic.- Savepoints undo part of a transaction without discarding all of it.
- Keep transactions short, expect busy errors, and always roll back on failure.
Next, views: naming a query so a report can be reused and reviewed.
Can I nest transactions?
Are transactions slow?
Progress is saved in this browser only. No account, no email.