Transactions and isolation levels: how not to lose money and data

A transaction is a group of database changes that succeed together or not at all. The isolation level determines what concurrent transactions see of each other. Handle transactions and isolation carelessly and simultaneous requests produce double charges, items sold twice and negative stock — bugs that never reproduce in tests and surface under load.

Here is what ACID means, which anomalies exist, how isolation levels differ and how to protect against race conditions.

What are a transaction and ACID?

A transaction combines several operations into one: for example, debit an account, create an order and reduce stock. The ACID properties describe the guarantees the database provides.

Property	Meaning	Example
Atomicity	All or nothing	No order without a charge and no charge without an order
Consistency	Data follows the rules	Stock cannot go negative if a constraint exists
Isolation	Concurrent transactions do not interfere	Two buyers cannot both buy the last item
Durability	Committed data stays	A confirmed order survives a server restart

Which anomalies appear under concurrency?

Anomalies appear when several transactions read and change the same data at once. The most common in web applications is the lost update: two requests read stock of 1, both decide the item is available and both decrement it.

• lost update — one change overwrites another;
• non-repeatable read — the same row is read with different values within one transaction;
• phantoms — a repeated query returns new rows added by another transaction;
• write skew — two transactions check a condition on different rows and together break a rule: for example, both on-call engineers take themselves off duty.

Photo: Unsplash

How do isolation levels differ?

Isolation levels trade anomaly protection for performance. PostgreSQL defaults to Read Committed; MySQL with InnoDB to Repeatable Read. Neither protects against every race in business logic on its own.

Level	What the transaction sees	Protects against
Read Committed	Only committed changes, as of each query	Dirty reads
Repeatable Read	A snapshot as of the transaction's start	Plus non-repeatable reads
Serializable	Results as if run one after another	All anomalies, at the cost of retries

At Serializable, the database may abort a transaction with a serialization failure, and the application must be able to retry it.

How do you protect against races in practice?

In practice there are three defences: atomic operations within the query itself, explicit row locks, or optimistic locking with a version number. The choice depends on how often conflicts happen.

// 1. Conditional atomic update: stock never goes negative
$updated = DB::table('products')
    ->where('id', $productId)
    ->where('stock', '>=', $qty)
    ->decrement('stock', $qty);

if ($updated === 0) {
    throw new OutOfStock($productId);
}

// 2. Explicit row lock until the end of the transaction
DB::transaction(function () use ($accountId, $amount) {
    $account = Account::whereKey($accountId)->lockForUpdate()->first();
    $account->balance -= $amount;
    $account->save();
});

Rules:

1. Check and change in one query or under a lock, never "read, think, write".
2. Keep transactions short: no external HTTP calls inside.
3. Add database constraints — unique indexes and checks — as the last line of defence.
4. Use idempotency keys for repeated requests — see idempotent payments and webhooks.

How do you check there are no races?

Races do not show up in ordinary tests because requests there run sequentially. They are checked with concurrent tests and load scenarios that fire many identical requests at once — for example, 50 attempts to buy the last item.

• write tests that run one operation from several processes simultaneously;
• include such scenarios in load testing;
• monitor deadlocks and serialization failures;
• review code that touches money and stock with extra care.

More on financial operations in fintech product development, and on choosing a database in SQL vs NoSQL.

In short

• A transaction applies a group of changes entirely or not at all.
• Without proper isolation you get lost updates, phantoms and write skew.
• Isolation levels trade protection for performance; Serializable requires retries.
• Atomic queries, row locks and optimistic locking protect against races.
• Races are caught by concurrent tests, not ordinary ones.