Database Transaction Safety: Locking and Isolation Rules That Hold Under Load

What Database Transaction Safety Actually Protects

Transaction discipline is a narrow slice of what code governance covers, but it is the slice that decides whether concurrency is a detail or an incident. Every database you can buy resolves conflicts according to rules you either declared or inherited by accident, and the code that inherited them looks identical in review to the code that declared them.

None of these principles are about developer carelessness. They are about the fact that a transaction which is correct at ten requests per second and wrong at a thousand produces no signal at all until the day the traffic arrives.

1. Transaction Boundaries Follow the Write Set

The order row exists and the inventory row was never decremented. Nothing threw, no alert fired, and the mismatch surfaces two weeks later in a reconciliation job. The cause is almost always the same shape: two writes that had to land together were issued as separate autocommitted statements, or they shared a transaction whose rollback lived in a catch branch that an early return skipped. Draw the boundary by asking which writes would be a bug if only some of them landed, then bind commit and rollback to a construct that runs on every exit path.

session.execute(insert(Order).values(**payload))
session.commit()
session.execute(update(Inventory).values(qty=Inventory.qty - n))
session.commit()   # a failure between them is silent data loss

with session.begin():   # commits on exit, rolls back on any exception
    session.execute(insert(Order).values(**payload))
    session.execute(update(Inventory).values(qty=Inventory.qty - n))

If rollback depends on control flow reaching a specific line, it is not guaranteed. Partial rollback inside the span is a savepoint, not a second transaction: opening a new transaction on a connection that already has one active gives you whatever your driver decided to do with the second BEGIN. This is not a rare class of defect. The ACIDRain study of self-hosted eCommerce applications found 22 exploitable vulnerabilities caused by incorrect transaction usage rather than by database defects.

2. Only Database Work Belongs Inside the Transaction

A payment provider has a slow afternoon. Checkout degrades, then every endpoint degrades, and the graph everyone is staring at says database connections at 100 percent. The provider call sits between BEGIN and COMMIT, so every in-flight checkout is holding a connection and a row lock while waiting on someone else's network. The quieter version of the same bug: the charge succeeded and the transaction rolled back, so the customer paid for an order that does not exist.

BEGIN;
  UPDATE orders SET status = 'paid' WHERE id = $1;
  -- stripe.PaymentIntent.create(...) holds a lock on someone else's latency
COMMIT;

charge = stripe.PaymentIntent.create(...)   # completes before BEGIN
BEGIN;
  UPDATE orders SET status = 'paid' WHERE id = $1;
  INSERT INTO outbox (type, payload) VALUES ('OrderPaid', $2);
COMMIT;

The rule covers work that is merely slow as well: validation, serialization, template rendering, password hashing, and file processing all belong outside the span. When a message has to be atomic with a write, the transactional outbox pattern is how you get it. Run this one on your own codebase. Search the transaction blocks in your payments and ordering services for an HTTP client symbol, because every hit is an incident scheduled for your provider's next bad day. Setting idle_in_transaction_session_timeout will surface the ones you missed, loudly, in staging.

3. Read-Modify-Write Belongs in a Single Atomic Step

Two concurrent requests read a credit balance of 100, each computes 99 in application code, and each writes 99. One decrement vanished. This passes review every time, because both writes sit inside transactions and the code reads like arithmetic. Under the read committed default the transactions do not prevent it, and low traffic hides it until a retry loop or an attacker sends the same request twice in parallel.

-- lost update: SELECT credits ... ; app computes 99 ; UPDATE credits = 99
UPDATE accounts SET credits = credits - 1
WHERE id = 42 AND credits >= 1;   -- zero rows affected means reject

Three forms are acceptable and everything else is a lost update waiting for concurrency: a single atomic statement that computes from the current value, an optimistic version predicate whose affected-row count you check, or a SELECT FOR UPDATE in the same transaction as the write. The optimistic variant is the same idea with a version column, where zero rows affected means someone else won and the caller retries the read. The process-level version of this defect is covered in the guide on concurrency and race condition prevention.

What is not acceptable is trusting the default isolation level to serialize the two paths for you, because what each level actually prevents varies by engine. Martin Kleppmann's Hermitage test suite exists precisely because the labels are not portable across Postgres, MySQL, Oracle, and SQL Server.

4. Isolation Levels Get Declared, Never Inherited

The transaction reads a status, decides based on it, and writes. It is correct on Postgres, where the default is read committed, and behaves differently on MySQL, where InnoDB defaults to repeatable read. It is also one pool configuration change away from behaving differently on the same engine, and nothing in the code records which behavior it was relying on. Any transaction whose correctness depends on reading a value and then acting on that value declares its isolation level explicitly, next to the statements it governs.

BEGIN ISOLATION LEVEL REPEATABLE READ;   -- stated here, not inherited
  SELECT seats_left FROM events WHERE id = $1;
  INSERT INTO bookings (event_id, user_id) VALUES ($1, $2);
COMMIT;   -- caller retries the whole transaction on SQLSTATE 40001

Blind writes are fine at the default. The moment a decision depends on a read, declare REPEATABLE READ or SERIALIZABLE and accept the obligation that comes with it: PostgreSQL's transaction isolation documentation is explicit that applications at these levels must be prepared to retry on serialization failure, which always returns SQLSTATE 40001.

Serializable is not free. It costs throughput and produces aborts under contention, so buy it for the transactions that need it rather than flipping a global default and finding the cost in production. The Hermitage results across engines are the reference for what each label actually buys you on the database you are running.

5. Every Lock Wait Needs a Deadline

One transaction holds a row lock while doing something slow. Every request that touches that row queues behind it with no bound on the wait. The connection pool fills, requests that never touch that table start timing out, and the incident channel spends forty minutes on the load balancer. An unbounded lock wait converts one slow query into a service-wide outage. What each lock mode actually blocks is spelled out in the PostgreSQL explicit locking reference.

BEGIN;
  SELECT * FROM jobs WHERE status = 'queued' FOR UPDATE;  -- waits forever
  -- every other worker queues behind this one
COMMIT;

SET LOCAL lock_timeout = '2s';
SELECT * FROM jobs WHERE status = 'queued'
ORDER BY id LIMIT 10 FOR UPDATE SKIP LOCKED;  -- take what nobody holds

The deadline creates errors, so pair it with a bounded retry. Catch serialization failures and deadlock-detected errors around the transaction, retry the whole transaction with jitter, cap the attempts, then fail the request. Retry the transaction and not the statement, because after a deadlock the transaction is already dead. MySQL's guidance on minimizing and handling InnoDB deadlocks makes the same point. The retry budget itself belongs to the family of decisions in the retry, timeout, and circuit breaker guide.

6. Bulk Writes Commit in Bounded Batches

A backfill runs UPDATE events SET status = 'migrated' WHERE status IS NULL against forty million rows. Locks accumulate, replication lag climbs, the on-call engineer kills it at hour three, and the rollback undoes all of it. Nothing was migrated, the table is bloated, and the migration starts over from zero on a table now under more pressure than when it began.

Bulk updates, deletes, and backfills iterate over an indexed key range in bounded batches, each batch committed in its own transaction, with the last committed key recorded. A kill then costs one batch and the job resumes from where it stopped. In Postgres the shape is a CTE that selects the next 5,000 ids above the last checkpoint with FOR UPDATE SKIP LOCKED, updates the rows those ids name, and returns them so the caller can advance the checkpoint.

Batching gives up atomicity across the whole operation, so the job has to be idempotent and resumable. That is a better property to own than a single transaction large enough to threaten the database, and it is the same property that makes the job safe to rerun after any failure. Batch size interacts directly with index choice, which is covered in the database query performance and indexing guide.

7. Lock Order Is a Codebase-Wide Contract

The transfer endpoint locks two account rows in the order the identifiers arrived in the request body. It works for a year. Then volume doubles and you get a deadlock storm at peak, where two concurrent transfers between the same pair of accounts each hold the lock the other one wants. The fix inside the endpoint is a two-line sort on ascending primary key. The problem is that the fix has to hold in every path that locks more than one row, in every service.

This is where individual competence stops being enough. A reviewer looking at a pull request sees one locking path and cannot see the other one, which lives in a different repository and was written by a team that has never read your incident review. A deadlock is created by the relationship between two code paths, not by either one of them.

The same is true of the other six rules once you pass a handful of services. Isolation declarations, lock timeouts, batch sizes, and transaction spans are each locally reasonable decisions that only add up to safety if they hold across every repository, including the ones nobody has touched in eight months. Consistency at that scale is not a review problem. It is an enforcement problem.

How Pandorian Enforces This Guide

Every principle above is statically observable. An HTTP client between BEGIN and COMMIT, a locking statement on a path with no timeout, a read-modify-write that lost its version predicate during a refactor: each of these is a code shape rather than a judgment call, which is what makes them enforceable instead of aspirational.

Caught at PR level

A network call added inside a transaction span, or a SELECT FOR UPDATE with no timeout on the path, surfaces on the line that introduced it with the rule and a suggested fix, while the author still has the context in their head.

Full scans

An on-demand scan across the whole codebase establishes the day-one baseline, including everything that predates the guideline. This is where the real inventory comes from: the unordered multi-row locks, the unbounded backfills, and the transactions still relying on a connection default for correctness, ranked so the team can decide what to fix first.

Scheduled scans

A new service that ships with its own transaction conventions, a repository imported after an acquisition, generated code that never passed through review. Lock ordering in particular can only be verified globally, which is exactly the check that no single pull request can perform.

A Taste of the Guideline Package

Four of the checks that ship in this package. Each runs at the PR level, on full or scheduled scans, or several at once.

FAQ