C · WriteAdvanced40 min read
9. Transactions & isolation
SQL · 48 pages source format
BEGIN / COMMIT / ROLLBACK. ACID guarantees. Transfer money mental model: debit and credit must both succeed or both fail.
What you'll learn
- BEGIN/COMMIT/ROLLBACK
- ACID intuition
- Isolation levels lite
- Deadlocks awareness
Transaction basics
BEGIN; multiple statements; COMMIT if all good, ROLLBACK on error. SQLite: BEGIN IMMEDIATE for writes.
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- inspect; then COMMIT or ROLLBACK
COMMIT;Do this now
Transfer script: decrement account A, increment B — wrap in transaction.
Clear?
ACID
Atomicity (all or nothing), Consistency (valid state), Isolation (concurrent sessions), Durability (committed survives crash).
Do this now
Write one sentence each for A-C-I-D in your notes.
Clear?
When transactions matter for QA
Test data setup/teardown, verifying rollback on validation failure, reproducing race conditions.
Do this now
List 2 test scenarios where transaction rollback should be verified.
Clear?