ACID Transactions and Isolation Levels: Preventing Corruption Under Concurrency

Why Transactions Exist: Preventing Partial Business Operations

A bank transfer can fail in the most expensive place: after the debit and before the credit. The service subtracts money from account A, commits that change, and then crashes before it credits account B. If money is successfully taken from the source account but never credited to the destination account, a serious accounting problem has been created. The sender is poorer, the recipient is not paid, and the database now records a transfer that never completed.

Without a transaction boundary, every multi-step write is a loaded gun. A partial write can leave one side of a business operation persisted and the other side missing. A concurrent request can read that half-finished state and make another decision from it. A process crash can stop the sequence partway through, leaving partial state that a transaction would otherwise prevent. The resulting corruption is not limited to transfers: an order can exist without its payment, inventory can be deducted without a confirmed purchase, or a payment can be recorded without the order that explains it.

The database therefore needs a boundary around the whole business operation, not merely around each individual statement. Inside that boundary, a failure must not expose a partial result, and a crash must not leave the system permanently between steps. The transaction's job is to make a sequence of related reads and writes take effect as one outcome: all of it becomes visible, or none of it does.

The Transaction Boundary and the Four ACID Guarantees

A transaction is a sequence of reads and writes treated as one logical unit. You open the boundary, perform the related operations, and then choose one of two outcomes: commit, which makes the unit visible as a whole, or rollback, which removes its effects. For an e-commerce checkout, the database transaction can contain the inventory deduction and order-row creation; payment charging requires an idempotent external workflow unless the payment record is local to the same database. If the process crashes after the inventory update but before the order is created, the database must not leave only the first write behind.

The four guarantees

ACID names the four guarantees that make this boundary reliable: Atomicity, Consistency, Isolation, and Durability. They describe different failure classes, so “the database supports transactions” is not enough by itself; you need to know which guarantee protects which invariant.

• Atomicity — all or nothing. The database records changes in a write-ahead log (WAL) before treating the transaction as complete. After a crash, recovery uses that bookkeeping to replay committed work or undo incomplete work. A checkout therefore becomes either all three writes or none of them.
• Consistency — declared rules remain true. The application defines the contract: foreign keys, check constraints, and business rules such as “a seat cannot be booked twice.” The database enforces the constraints you declare; it cannot infer every business rule from your intent.
• Isolation — concurrent work is controlled. One transaction should not observe another transaction’s in-progress changes. The database must control visibility while transactions overlap, which is why isolation is the most complex ACID property to provide cheaply.
• Durability — a successful commit survives failure. Once the database returns a commit acknowledgement, the committed data must survive a power loss. Systems typically combine flushing data with fsync and maintaining replicas, so a process or machine failure does not erase an acknowledged result.

These properties cooperate. Suppose two transactions both try to book the last seat. A foreign key can ensure that each booking references a valid flight, but it does not by itself prevent both transactions from passing the same availability check. Isolation must control how those concurrent reads and writes interact; otherwise both transactions can appear valid individually while the combined result violates the business rule.

Isolation Levels: The Anomalies You Are Buying or Preventing

Isolation is a spectrum of visibility guarantees: it controls how much one transaction can be affected by concurrent transactions. Stronger isolation makes concurrent work look more like a sequence of isolated transactions, but can reduce concurrency or require retries. The four standard levels, from weakest to strongest, are READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE.

The four anomalies

A dirty read occurs when transaction A reads a value that transaction B has changed but not committed. If B rolls back, A acted on a value that never became part of the database. A non-repeatable read occurs when A reads a row, B updates or deletes it and commits, and A reads it again within the same transaction; the second read returns a different value or no row.

A phantom read applies the same problem to a result set rather than one row. A queries all orders above a threshold, B inserts and commits a matching order, and A repeats the query. The second result contains a row that was absent from the first result.

A lost update is a read-then-write race. Two transactions read the same counter, increment their local copies, and write the results back. The later write overwrites the earlier increment, so one update disappears. The same sequence oversells inventory: both requests read seats_remaining = 1, both decide that booking is allowed, and both insert a booking.

What each level permits

READ UNCOMMITTED provides no protection against dirty, non-repeatable, or phantom reads, and a read-then-write update can also lose an earlier write when concurrent transactions overwrite one another. It is rarely appropriate for mutable business data, though approximate analytics can tolerate data that changes during the scan.

READ COMMITTED prevents dirty reads, but permits non-repeatable reads and phantoms. Each statement sees committed data as of that statement's read. Consequently, two statements in one transaction can observe different committed states. A read-modify-write sequence can also lose an update unless the write operation or an explicit protection strategy closes the race. This is the default in PostgreSQL and Oracle and is a reasonable general-purpose OLTP starting point, but it does not make a read-then-write invariant safe by itself.

REPEATABLE READ gives a transaction a consistent snapshot of committed data from its start, so repeated reads do not see concurrent commits. It prevents dirty and non-repeatable reads. Under the SQL standard, phantoms can still occur; MySQL InnoDB prevents them at this level with gap locks. PostgreSQL provides a stronger behavior than that minimum: its repeatable-read transaction never sees changes committed by concurrent transactions after the transaction's snapshot began, but applications must be prepared to retry after serialization failures. Lost-update behavior depends on the implementation and write pattern, so the level name alone is not a substitute for checking how conflicting writes are handled.

SERIALIZABLE provides the strongest guarantee: concurrent transactions behave as if they ran one at a time, preventing dirty, non-repeatable, and phantom reads as well as the lost-update race. Databases can implement that behavior with two-phase locking (2PL) or serializable snapshot isolation (SSI). The cost is contention or transaction aborts and retries, so the level is justified when a weaker visibility model could violate a business invariant—not merely because the query is important.

A practical reading rule

Choose the lowest isolation level that prevents the anomaly capable of corrupting the invariant. Use READ COMMITTED when each statement only needs committed data and small differences between statements are acceptable. Use REPEATABLE READ when several reads must share one committed snapshot, especially for read-only calculations. Use SERIALIZABLE when the transaction's correctness depends on a complex set of reads and writes appearing to execute alone. For a balance check followed by a debit, ask whether two transactions can both pass the check and then overwrite or duplicate the result; if they can, READ COMMITTED alone is not sufficient.

How Databases Implement Isolation: Locks, MVCC, and Explicit Row Protection

Isolation is implemented by controlling what a transaction may read and when it may change a row. The two broad strategies are pessimistic locking, which assumes a conflict may happen and makes transactions wait, and MVCC, which lets readers use a committed version while a writer prepares a newer one. Both protect the database, but they move the cost to different places: locking spends time waiting, while MVCC spends work maintaining versions and resolving write conflicts.

Pessimistic locking: wait before touching the row

With two-phase locking (2PL), a transaction acquires the lock it needs before reading or writing, holds that lock through the transaction, and releases it at commit or rollback. A reader and writer that need incompatible locks cannot proceed together: the later operation waits until the first transaction finishes. This prevents concurrent operations from observing or changing protected state in an unsafe order, but the waiting consumes concurrency. Under high contention, lock queues reduce throughput; if transactions acquire overlapping locks in different orders, they can deadlock and require one transaction to be aborted.

MVCC: read a committed version

Optimistic concurrency control through multi-version concurrency control (MVCC) keeps multiple versions of a row. A reader chooses the version visible to its transaction snapshot, while a writer creates a newer version instead of forcing that reader to wait. PostgreSQL and MySQL InnoDB use MVCC. This makes ordinary reads cheap and avoids reader-writer blocking, but it does not make writes conflict-free: two transactions that try to change the same logical row still need a conflict decision. The database can wait for the earlier writer, reject one transaction, or require an application retry, depending on the operation and isolation implementation.

The important distinction is between reading a value and claiming the right to act on it. A plain availability read can return one seat remaining to two transactions. Each transaction can then decide that booking is safe before either has changed the row. SELECT FOR UPDATE closes that gap: it declares that the transaction intends to write the selected row, obtains a write lock, and holds it until the transaction ends. The second transaction waits; after the first commits or rolls back, it must use the resulting row state rather than the stale value it initially expected.

For a seat reservation, the safe sequence is to begin the transaction, lock the inventory row, check seats_remaining, insert the booking only when the value is positive, and commit. If no seat remains, roll back instead of inserting. The lock protects the read-then-write invariant without requiring every transaction in the database to run at SERIALIZABLE.

BEGIN TRANSACTION
row = SELECT seats_remaining FROM inventory WHERE <seat predicate> FOR UPDATE
IF row.seats_remaining > 0:
    INSERT booking for the selected inventory row
    COMMIT
ELSE:
    ROLLBACK

The choice is a workload decision, not a contest between universally safe and unsafe databases. Higher isolation prevents more anomalies, but generally increases lock contention, conflict detection, aborts, or retries and therefore can reduce throughput. Use the lowest isolation level that protects the invariant being changed. For a Stripe-style idempotency key, a unique constraint plus a READ COMMITTED transaction can prevent duplicate database records; the payment operation must also be made idempotent or coordinated separately so a duplicate external charge is not produced.

Worked Example: Making Checkout All-or-Nothing

Consider a checkout for one item. The operation has three required effects: deduct inventory, create the order row, and complete payment processing. The first two can share a database transaction; payment needs a separate idempotent or coordinated workflow if it is external. Without a transaction boundary, a server crash can occur after any database write—or between writes—leaving a partial checkout behind. The most damaging case is easy to construct: inventory is deducted and an order exists, but payment is never completed. The customer may receive an order the system has not collected money for, while the inventory is no longer available to anyone else.

For three database-local effects, each effect has two possible outcomes after a failure: it survives or it does not. That gives 2^3 = 2 × 2 × 2 = 8 possible subsets of writes. Only two outcomes fit an all-or-nothing database transaction: none of the required database changes, or all of them. The transaction therefore removes 8 − 2 = 6 invalid terminal states and leaves 2 rather than 8 states—4 times fewer outcomes. An external payment effect is different: it may require a separate completion or compensation path.

Put the database-local writes inside one transaction. The database tracks them as one logical unit, then makes one commit decision. If inventory deduction or order creation fails, the database transaction rolls those database changes back. An external payment charge cannot be assumed to roll back with it; use an idempotent payment workflow and reconciliation or compensation. If the database-local writes succeed, they commit together, while the payment workflow must bring the external effect to a consistent outcome.

The arithmetic is the point: atomicity does not make individual operations impossible to fail. It constrains what can remain after failure. A crash during the checkout can still prevent the order from completing, but it cannot leave the database in one of the six mixed states. The application can report that the checkout failed and safely retry the transaction, instead of trying to infer which of the three writes survived and repairing an unknown combination of inventory, order, and payment state.

Choosing the Lowest Isolation That Protects the Invariant

Isolation is not a binary switch between safe and unsafe. It is a budget: you choose how much concurrent work a transaction may observe, then pay for stronger guarantees with more contention, more transaction retries, or both. Start with the invariant that must never be violated—such as “a seat cannot be sold twice”—and choose the lowest level that prevents the anomaly capable of breaking it.

The comparison below separates visibility from cost. The important distinction is between Read Committed, which gives each statement a fresh committed view, and Repeatable Read, which holds a transaction-level view. Read Committed is the default in PostgreSQL and Oracle and is a sensible general-purpose OLTP starting point, but it does not make a read-then-write sequence one indivisible decision.

Read Uncommitted is defensible only when an approximate result is more useful than a moment-of-query-accurate one—for example, a high-level summary over data that may be changing. It permits dirty, non-repeatable, and phantom reads, so it is not an appropriate foundation for balances, reservations, or other business invariants. Read Committed is usually the practical default: it prevents dirty reads while allowing a later statement to observe a different committed state. That is often acceptable for an inventory display, where a small inconsistency between reads is less harmful than the coordination cost of a stronger level.

Repeatable Read is the better fit when several reads must describe one stable committed view, such as calculating a user's total account balance. It prevents dirty and non-repeatable reads; phantom protection depends on the implementation and workload, so do not infer full serial execution from the name. Serializable is the choice when the transaction's successive commands must behave as though transactions ran one at a time. It prevents the standard read anomalies, but the database must detect more interference, which means more contention and transaction retry errors. Retrying a complex transaction can be expensive because all of its work may need to be performed again.

Failure Mode: Read-Then-Write Races Under Real Concurrency

A seat reservation can look correct in code and still oversell under concurrency. Suppose seats_remaining = 1. Request A reads the value, sees one seat, and decides booking is allowed. Before A writes, request B reads the same value and reaches the same conclusion. Both requests insert a booking; both may decrement the inventory afterward. The system has sold two seats while its inventory check started with one: a lost update and an oversell by 1.

The race exists because the check and the write are separate actions. READ COMMITTED, PostgreSQL’s default isolation level, prevents either request from reading uncommitted data, but it does not make A’s earlier read remain true when B commits an intervening change. The database can therefore execute two individually valid transactions whose combined result violates the invariant “bookings must not exceed available seats.” For general-purpose OLTP, Read Committed remains a sensible default; it offers good concurrency when minor inconsistencies between reads are acceptable. It is not a safe default for a read-then-write invariant.

At small scale, the direct fix is to serialize access to the inventory row. Run the reservation transaction at SERIALIZABLE, accepting that conflicting transactions can fail and must be retried, or read the row with SELECT FOR UPDATE before checking and updating it. The lock makes the second request wait until the first transaction commits or rolls back; it then evaluates the current inventory instead of acting on the stale read. Use this when the invariant is non-negotiable, such as inventory reservation or a financial transfer, and the contention is manageable.

At higher scale, avoid making every request wait behind a single hot row when the workload cannot afford Serializable’s retry and contention cost. Optimistic locking stores a version with the inventory record: the update succeeds only if the version is still the one the request read, and a failed comparison triggers a retry against fresh state. You can also serialize reservation writes through a dedicated queue or reservation service. Advisory locks are another application-level option when you can define a stable lock key. The decision is not “strict or unsafe”; it is which mechanism detects or prevents the conflicting write at the point where the invariant could break.

Failure Mode: Transaction Boundaries Stop at the Database

A database transaction is atomic only within the database system that owns it. If a checkout service writes an order to one database, decrements inventory in a second shard, and asks a payment service to charge a card, each component can commit locally while the overall operation still fails. A process crash after the payment succeeds but before the order is recorded leaves a charge without an order; a shard outage after the order commit leaves an order without reserved inventory. No single local rollback can undo work that has already crossed a service boundary.

The failure is a missing global commit decision. The service must either add coordination across participants or accept that the operation can be observed in intermediate states. Two-phase commit is a coordination mechanism for extending atomicity across participants, with an associated coordination cost. A saga is an application-level alternative to a single distributed transaction; it coordinates local operations and must define how failures are handled. Compensation is a business operation, not a database rollback; a payment refund, for example, may not erase every side effect of the original charge.

Reporting has a quieter version of the same boundary problem. PostgreSQL's Read Committed isolation is statement-scoped: each command starts with a new snapshot containing transactions committed up to that instant. A long-running report that issues multiple statements can therefore combine results from different committed states. That may be acceptable for a dashboard showing approximate current activity, but it is unsafe for a financial ledger read whose totals must describe one coherent state. Use a transaction-level snapshot when the report requires a stable view; otherwise make the mixed-state behavior an explicit product choice rather than treating it as an ACID guarantee.

Distributed databases add another choice rather than removing the problem. Eventual consistency can make independently replicated data converge without giving a cross-shard operation one immediate commit boundary. When the invariant requires cross-shard ACID, coordination mechanisms such as two-phase commit or systems such as Spanner's TrueTime impose a coordination cost. At larger boundaries, sagas and queue-based serialization can preserve a business invariant through explicit state transitions, but they require retry-safe operations, observable intermediate states, and carefully designed compensation.

Defending an Isolation Choice in a Design Conversation

A strong isolation answer starts with the invariant, not the database setting. State what must never happen: “one ticket must produce at most one booking,” “a transfer must debit and credit exactly once,” or “the ledger read must represent one coherent state.” Then name the anomaly that would violate it. For a read-then-write reservation, that is a lost update or oversell; for a long-running report, it is a changing view between statements.

Next, choose the narrowest protection that closes that failure mode. A transaction is required when several writes form one logical operation. READ COMMITTED is often enough when each statement can use committed data independently and a constraint or atomic update protects the invariant. Add SELECT FOR UPDATE when the operation reads a row and then makes a decision that depends on its current value. Use REPEATABLE READ for a read-only calculation that must observe one snapshot. Use SERIALIZABLE when several reads and writes interact in a way that cannot be protected by a narrower lock, constraint, or retryable optimistic check.

A design answer should name the cost alongside the guarantee: stronger isolation means fewer anomalies but more contention, retries, or lower throughput. Ask which reads must agree, which writes compete, how the application handles abort-and-retry, and whether stale results are acceptable for this operation. The question that separates operational experience from memorized definitions is: “What exact interleaving breaks the invariant under the proposed level, and what happens when the competing transaction wins?” If you cannot show that interleaving, you have not justified the level.