luvv to help

Menu

Topic 3 of 8

Transactions And Isolation Basics

Learn Transactions And Isolation Basics for free with explanations, exercises, and a quick test (for Backend Engineer).

Published: January 20, 2026 | Updated: January 20, 2026

Table of Contents

Why this matters

Backend systems process money transfers, orders, inventory, bookings, and other state changes. Transactions and isolation ensure data stays correct when many requests happen at the same time. Get them right to avoid lost money, oversold items, or corrupt reports.

  • Banking: Move funds between accounts safely.
  • E-commerce: Prevent overselling during flash sales.
  • SaaS: Ensure consistent counters, quotas, and billing.
  • Analytics: Produce stable results even while writes continue.

Concept explained simply

ACID in one minute

  • Atomicity: All or nothing.
  • Consistency: Rules are preserved (constraints, invariants).
  • Isolation: Concurrent transactions don’t step on each other’s toes.
  • Durability: Committed data persists.

Mental model

Imagine editing a shared spreadsheet. A transaction is like making a set of edits in a private draft, then pressing Commit to publish all changes at once. Isolation is how well your draft is protected from others’ edits and vice versa.

Isolation levels and anomalies

  • Read Uncommitted: Can see uncommitted changes. Risk: dirty reads.
  • Read Committed: Only sees committed data. Risks: non-repeatable reads, phantoms.
  • Repeatable Read: Re-reads the same row and gets the same values. Risk: phantom reads (new rows appearing in a range query).
  • Serializable: Safest. Equivalent to some serial order of transactions. Avoids dirty, non-repeatable, and phantom reads. Usually the most restrictive for throughput.

Common anomalies:

  • Dirty read: Reading uncommitted changes from another transaction.
  • Non-repeatable read: Re-reading a row and getting a different value mid-transaction.
  • Phantom read: Re-running a range query and seeing new rows appear.
Tip: Locks vs MVCC

Databases use locks and/or MVCC (multi-version concurrency control). MVCC lets readers see a snapshot without blocking writers. Details vary by engine, but the isolation guarantees above remain the mental map.

Worked examples

Example 1 — Preventing dirty reads with Read Committed

  1. Tx A: UPDATE accounts SET balance = balance - 100 WHERE id = 1; (not committed yet)
  2. Tx B (Read Uncommitted): SELECT balance FROM accounts WHERE id = 1; — Might see a lower balance that is not committed (dirty).
  3. Tx B (Read Committed): SELECT balance ... — Sees the last committed balance, no dirty read.
  4. If Tx A rolls back, Read Committed protected Tx B from misinformation.

Example 2 — Non-repeatable read under Read Committed

  1. Tx A (Read Committed): SELECT balance FROM accounts WHERE id = 1; returns 1000.
  2. Tx B: UPDATE accounts SET balance = 900 WHERE id = 1; COMMIT;
  3. Tx A: SELECT balance FROM accounts WHERE id = 1; now returns 900 (changed mid-transaction) — a non-repeatable read.
  4. Under Repeatable Read, Tx A would see the same row value throughout the transaction.

Example 3 — Phantom read and Serializable

  1. Tx A (Repeatable Read): SELECT COUNT(*) FROM orders WHERE status = 'pending'; returns 5.
  2. Tx B: INSERT INTO orders(status) VALUES('pending'); COMMIT;
  3. Tx A re-runs the same query: COUNT goes to 6 — phantom row appeared.
  4. Under Serializable, Tx A would behave as if Tx B happened before or after it, not interleaved, so no phantom appears during Tx A.

Choosing an isolation level

  • Read Committed: Good default for many OLTP apps; fewer blocking issues.
  • Repeatable Read: Use when per-row consistency during a transaction matters (e.g., financial reads before write-back).
  • Serializable: Use for critical invariants across ranges (e.g., inventory caps) when simpler patterns won’t do.
Pragmatic patterns that reduce the need for strict isolation
  • Idempotent writes: Safe retries.
  • Optimistic concurrency: Check a version/timestamp before update.
  • Unique constraints: Enforce invariants at the database layer.
  • Explicit locking (SELECT ... FOR UPDATE): Serialize only the rows you need.

Engine notes (very high-level)

  • Defaults vary by engine. For example, some engines default to Read Committed while others default to Repeatable Read.
  • MVCC engines often let readers run without blocking writers, but write-write conflicts or serialization failures can still happen.
  • Always confirm the default isolation level and available options in your environment.

Deadlocks, timeouts, and retries

  • Deadlock: Two transactions wait on each other. Databases detect and abort one. Your code should catch this and retry the transaction.
  • Serialization failure: At high isolation (often Serializable), concurrent changes may conflict. Retry with backoff.
  • Keep transactions short: Fewer locks held, lower chance of conflicts.
Simple retry strategy
  1. Begin transaction.
  2. Run statements.
  3. On deadlock/serialization failure: rollback, wait briefly (jitter), retry up to N times.

Practical projects

  • Bank transfer service: Debit one account, credit another, ensure invariants (no negative balances).
  • Inventory reservation: Prevent overselling on concurrent checkouts.
  • Counter with optimistic concurrency: Maintain a usage limit with version checks.

Common mistakes and self-check

  • Leaving long transactions open while doing network calls — self-check: ensure external calls happen outside the transaction.
  • Relying on app-level checks without DB constraints — self-check: add unique keys, CHECK constraints.
  • Assuming reads are always stable — self-check: pick isolation level based on required guarantees.
  • Forgetting retries on deadlocks/serialization failures — self-check: test under load and observe errors.

Exercises

Do these in a local database (any relational engine you are comfortable with). Keep transactions short. The Quick Test is available to everyone; if you are logged in, your progress and answers will be saved.

Exercise 1 — Safe money transfer with proper isolation

Goal: Implement a transfer that keeps total funds consistent even under concurrency. See the Exercises section for full instructions and solution.

Exercise 2 — Phantom reads and how to prevent them

Goal: Reproduce a phantom read on a range query, then eliminate it with Serializable or explicit locking. See the Exercises section for full instructions and solution.

  • Checklist before running: test with two concurrent sessions; add assertions; verify final invariants.

Mini challenge

You run a ticketing system with 100 seats per show. Multiple users buy at the same time. Design the flow so you never oversell. Which isolation level do you choose? What DB constraints and locks would you add? Outline steps as a numbered list.

Hint
  • Consider a unique constraint on seat_id per show.
  • Consider selecting seats FOR UPDATE during allocation.
  • Keep transactions small; handle retries.

Who this is for

  • Backend engineers building APIs and services with relational databases.
  • Developers moving from single-user apps to multi-user, concurrent systems.

Prerequisites

  • Basic SQL (SELECT/INSERT/UPDATE/DELETE).
  • Understanding of primary/foreign keys and indexes.
  • Ability to run a local relational database.

Learning path

  1. Review ACID and isolation levels.
  2. Practice with the worked examples in two sessions.
  3. Complete Exercises 1–2 and add retries for conflicts.
  4. Take the Quick Test to check understanding.
  5. Apply to a small project (inventory or transfers).

Next steps

  • Add monitoring: log deadlocks and retry counts.
  • Document chosen isolation per operation (read-only vs critical writes).
  • Load-test critical flows to validate correctness under concurrency.

Practice Exercises

2 exercises to complete

Instructions

Create a table and simulate concurrent transfers between two accounts. Ensure total balance stays constant and no negative balances appear.

  1. Setup:
    CREATE TABLE accounts(id INT PRIMARY KEY, balance INT NOT NULL CHECK (balance >= 0));
INSERT INTO accounts VALUES (1, 1000), (2, 1000);
  2. Session A (Transaction 1):
    BEGIN;
-- choose isolation appropriate for your DB, e.g. SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
-- optionally: SELECT * FROM accounts WHERE id IN (1,2) FOR UPDATE;
-- do not commit yet
  3. Session B (Transaction 2):
    BEGIN;
-- same or different isolation level (experiment)
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;
COMMIT;
  4. Return to Session A and finish transfer:
    UPDATE accounts SET balance = balance + 200 WHERE id = 2;
COMMIT;
  5. Check results:
    SELECT * FROM accounts ORDER BY id;
SELECT SUM(balance) AS total FROM accounts;
  6. Repeat with different isolation levels and/or SELECT ... FOR UPDATE to see how conflicts, waits, or retries happen.
Expected Output
Final balances reflect exactly one transfer per committed transaction; the total sum remains 2000. No negative balances. Some attempts may block or fail with deadlock/serialization errors that require a retry.

Transactions And Isolation Basics — Quick Test

Test your knowledge with 10 questions. Pass with 70% or higher.

10 questions70% to pass
Relational Modeling BasicsConnection Pooling

Have questions about Transactions And Isolation Basics?

AI Assistant

Ask questions about this tool