Transactions ACID and Isolation Levels

01 The idea

The problem this lesson solves

Wrapping code in BEGIN and COMMIT does not make every concurrent business rule safe. A transaction groups changes, while its isolation level controls which concurrent effects it can observe. To design correctly, you need to distinguish atomicity, visibility and invariants—and then walk an actual interleaving rather than relying on the word “ACID.”

You should recognize API resources and failure responses. SQL examples introduce their own tables and constraints.

02 Worked example

Read a value while another transaction commits

03 The method

Understand the mechanism

Separate the four ACID promises

Atomicity means a transaction’s changes commit together or are rolled back together. If a transfer debits one account and credits another in the same transaction, an ordinary transaction abort should not leave only one of those database changes committed.

Consistency means the database transitions preserve the rules enforced by constraints and correctly written transaction logic. It does not mean every replica immediately shows the latest value. A database cannot infer that a doctor must remain on call or that a particular payment workflow is valid unless the design expresses those rules.

Isolation concerns concurrent execution and observation. Durability concerns what happens to an acknowledged commit under the configured failure and persistence guarantees. These are separate axes. A transaction can be atomic while a client still sees an older replica, and a durable transaction can still implement the wrong business rule.

Also define the transaction boundary. A database transaction does not automatically include an HTTP request to a payment provider or an email already sent. Rolling back database rows cannot unsend an external effect. Later workflow designs need a protocol for those cross-system boundaries.

Understand snapshots and visible versions

Databases often retain multiple row versions so readers can observe a consistent view without blocking every writer. A snapshot determines which committed versions a query may see. The exact rules are implementation-specific, so this lesson uses PostgreSQL explicitly.

Under PostgreSQL Read Committed, each ordinary statement begins with its own snapshot of committed data. Transaction A can SELECT value=10, transaction B can commit value=20, and A’s next SELECT can see 20. A did not read uncommitted data; it read a newer committed version in a later statement.

That behavior is useful for many operations, but it means several reads in one transaction need not describe the same moment. If application logic assumes the world stayed unchanged between statements, it can make an invalid decision.

Row updates also interact with locks and rechecking rules. A concurrent conditional UPDATE at Read Committed can wait for another updater and then reevaluate its condition against the updated row. This is why an atomic predicate can protect a single-row claim where a separate earlier read cannot.

Name the anomaly with an actual schedule

A dirty read observes another transaction’s uncommitted change. A nonrepeatable read obtains a different value when rereading a row after another transaction commits. A phantom concerns a changed set of rows matching a predicate. A serialization anomaly means the combined committed outcome cannot be explained by running the transactions one at a time in some order.

Names help only if you can produce a schedule. For a nonrepeatable read: A reads 10, B writes and commits 20, A reads 20. For a lost-update-style application bug: A and B both read counter 10, both calculate 11 in their own memory, and both write 11 unconditionally. Two increments have produced a final value of 11.

An atomic UPDATE counter = counter + 1 changes that particular operation because the database performs the increment as part of the row update. It does not solve every multi-row invariant. Choosing the mechanism requires understanding the shape of the rule.

Write skew is a useful multi-row example. Two on-call doctors each check that another doctor remains, then each turns off their own row. If both decisions use snapshots that show the other still on call, both can commit in an isolation model that permits the anomaly, leaving nobody available.

Choose isolation for the required guarantee

PostgreSQL Repeatable Read gives a stable transaction snapshot, so the later SELECT in our value example continues to see 10. It prevents more phenomena than the minimum standard definition requires, including phantom reads in PostgreSQL’s implementation. It still does not guarantee that every concurrent result is equivalent to serial execution.

Serializable provides a stronger target: committed results must be consistent with some serial ordering. PostgreSQL may abort a transaction when it detects a dangerous concurrent pattern. That abort is part of the mechanism, not proof that the database failed to provide the promised guarantee.

The application must retry the whole transaction when required, using a bounded policy. Retrying only the final statement can reuse decisions made from an invalid snapshot. The transaction body also needs to avoid unrepeatable external effects such as sending an email before a potentially retried commit.

Higher isolation is not a substitute for good modeling. Unique constraints, conditional writes and clear ownership remain valuable. Choose the smallest combination that enforces the actual invariant, then test the concurrent schedules that threaten it.

Compare locking and optimistic version checks

SELECT FOR UPDATE obtains a row lock useful when a decision needs several steps involving that row. Keep the transaction short because other operations may wait for it. A lock held while a person fills out a payment form creates an operational problem even if the basic correctness argument is sound.

An optimistic update includes a previously observed version in its condition: update only where id matches and version still equals 4, then advance the version. If zero rows are changed, another writer or state transition invalidated the assumption. The application must reread or report a conflict.

Optimistic control is attractive when conflicts are uncommon and repeating work is affordable. Pessimistic locking can be useful when serializing a short critical decision is simpler. Neither is automatically superior; the workload and user experience determine the cost of waiting versus retrying.

For multi-row operations, consider lock order and deadlocks. Two transactions can each hold one row and wait for the other. A consistent acquisition order reduces a common source of cycles, but applications still need to handle database-selected aborts.

Keep transactions short and observable

A transaction occupies resources: connections, locks, snapshots and sometimes retained old versions. Long-running transactions can create latency and maintenance pressure even when they do not consume much CPU. Avoid opening one before waiting for user input or an unrelated slow network call.

Record transaction duration, lock waits, deadlocks and retry counts separately from ordinary query time. A query that is fast in isolation can be slow because it waits behind another transaction. An index may reduce the work inside a transaction but cannot fix every bad transaction boundary.

Design a retry loop with a maximum attempt count and total deadline. If repeated conflicts occur, surface a meaningful failure rather than retrying forever. Investigate whether contention reflects a hot row, an overly broad transaction or an invariant that should have a different ownership model.

The practical lesson is to reason about a schedule of reads, writes and commits. Once you can predict what two concurrent sessions observe and which one must wait or retry, isolation becomes a design tool rather than a list of definitions.

-- Setup once in a disposable database, before opening both sessions:
CREATE TABLE sd_lab_values (id integer PRIMARY KEY, value integer NOT NULL);
INSERT INTO sd_lab_values VALUES (1,10);
-- Session A:
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT value FROM sd_lab_values WHERE id = 1;
-- Session B, outside A's transaction:
UPDATE sd_lab_values SET value = 20 WHERE id = 1;
-- Session A again: this reads the newer committed value 20.
SELECT value FROM sd_lab_values WHERE id = 1;
COMMIT;

04 Cheat sheet

Review the decisions

Use this table to explain the mechanism and its limit without memorizing a product name.

05 Where & why

Check the primary references

Use official references for protocol and implementation details. The questions here are teaching prompts.

06 Interview questions

Explain the reasoning out loud

Answer before opening the explanation. The questions progress from vocabulary to decisions and failure behavior.

07 Practice problems

Practice a new case

Solve the new cases before revealing hints. Complete worked solutions follow the set.

Worked solutions