Business Software Is Hard

Essay 5

Why does it only fail in production?

Why transaction bugs look intermittent, and why hiding the transaction makes them harder to understand.

Database transactions are supposed to make things safe. Put the work inside a transaction and either all of it commits or none of it does. That is how I understood them for a long time, and I suspect it is still the working model for many application developers.

It is not enough. A transaction can run successfully, commit successfully and do exactly what its SQL asked for, and the business outcome can still be wrong because another legitimate transaction was running at the same time.

The title question has a dull answer. Development and many test environments are effectively single-user. The second transaction is not missing by luck; it is missing by construction. Production is often the first place where the concurrent program we wrote actually runs concurrently, and therefore the first place where anyone discovers what it does. Two users happen to act at nearly the same moment and the system reaches a state everybody agrees should have been impossible.

That can make the database look unreliable. How can the same code work correctly for months and occasionally produce the wrong answer? But perhaps the database did not fail. Perhaps we misunderstood what we asked it to guarantee.

Application code encourages a sequential mental model: load some data, check a condition, calculate something, write a result. In production, other users, batch jobs and other applications are executing their own programs against the same persistent state. Database programming is therefore much more like multi-threaded programming than many application developers like to admit.

There is one difference that can make it harder still. In a multi-threaded program you can at least read the other threads. The transaction that invalidates your assumption may live in another team’s service, a nightly batch or a database administrator’s session. It may not even be in your repository.

Two ways to be wrong

It helps to separate two failures that are often discussed as one.

The first is a level problem. The physical transaction boundary is in the right place, but the isolation level permits an interleaving that breaks the business rule. This is the kind of problem the isolation-level literature is mostly about.

The second is a scope problem. The boundary itself is wrong. The observation and the change it justifies are not inside the same physical transaction, so no isolation level can relate them.

Warszawski and Bailis’s study of concurrency attacks on database-backed web applications maps onto this distinction. They found twenty-two critical attacks across twelve widely used self-hosted eCommerce platforms, allowing attackers to corrupt inventory and spend gift cards more than once. What strikes me is how many of those vulnerabilities were scope problems rather than level problems. The transaction was not too weakly isolated. It was missing part of the business operation.

That imbalance matters. Isolation levels get attention because they have names and settings. Scope gets less attention because the boundary is often nobody’s explicit design decision.

The database is shared mutable state

We already know that shared mutable state makes concurrent programming difficult. Two individually correct pieces of code can become incorrect when they run together. A thread-safe collection does not make a sequence of operations on that collection atomic, and a value that was true when one thread read it may no longer be true when that thread acts on it.

We often forget that a database has the same underlying problem. Repository and ORM abstractions make it easy to think in terms of loading an object, changing it and saving it, but the database remains shared mutable state.

What it gives us is machinery for controlling that state. Transactions provide atomic commit and rollback. Constraints can make some invalid states impossible. Locks, row versions and isolation rules determine what concurrent operations may observe and when they wait, fail or proceed. What those facilities do not provide is a blanket guarantee that every transaction behaves as though no other transaction exists.

The phrase transaction isolation therefore sounds stronger than the guarantee we may actually have chosen. The same level name can also imply different behaviour across database products. I am not going to turn this essay into an isolation-level tutorial; the references at the end point to the detail. The important point here is that accepting a database or framework default is still a design choice, whether or not anyone made it consciously.

The database is not breaking its promise when a permitted anomaly occurs. The promise in our heads may simply be stronger than the one the database made.

A correct statement is not a correct transaction

There is a familiar concurrency mistake in ordinary code:

if queue.size() > 0:
    queue.remove()

Both operations may be individually safe, yet the compound operation is unsafe if another thread can empty the queue between them.

Business database code contains the same pattern: check that an order is awaiting approval, then approve it; check that capacity remains, then allocate some of it; read a balance, decide that a withdrawal is permitted, then update the balance. Each SQL statement can be correct while the business operation still races with another business operation.

Here is the race made concrete. Two operations begin from the same valid state:

Step approve_order cancel_order
1 reads order 4711, status awaiting_approval
2 reads order 4711, status awaiting_approval
3 updates status to cancelled, commits
4 checks the approver’s limit against the order value
5 inserts approval history, updates status to approved, commits

The order is now approved, and an approval history row records a decision about an order that had already been cancelled. Neither transaction did anything its own code forbids.

If the read and final update are inside one physical transaction, this is a level problem: the outcome depends on the database’s concurrency semantics and the isolation chosen. A sufficiently strong implementation must prevent the non-serializable outcome, perhaps by blocking or aborting one transaction.

If the read and update are in different physical transactions, it is a scope problem. No isolation level on the later update can make the earlier observation atomic with it.

That second shape is common in web applications: read the order, render a screen, wait for a human, then post the decision back. The answer is not to keep one database transaction open while a user thinks. We need an explicit way to detect that the state on which the decision depended has changed, often by carrying a version or timestamp out to the client and requiring it to match when the change is applied.

Frameworks can make the physical boundary harder to see. A transaction annotation may begin above several service calls; nested calls may join or create transactions according to propagation rules; an ORM may delay writes until flush or commit. None of that machinery is necessarily wrong, but the physical transaction can become an emergent property of the call graph.

Two terms are worth pinning down. The physical transaction is what the database sees between begin and commit. The logical transaction program — what an earlier essay called simply the transaction program — is the business operation we intended: the facts it observes, the decisions it takes and the changes it makes. Many problems arise when those two things are not the same shape.

Some invariants do not need an argument

All of this can make concurrency sound as though every invariant requires elaborate reasoning. It does not.

Some invariants can be stated directly to the database. A unique constraint can make a duplicate approval impossible. An exclusion constraint can prevent overlapping bookings. A check constraint can prevent some invalid values. A foreign key can prevent references to state that does not exist.

Where an invariant can be expressed as a database constraint, that is usually cheaper than reasoning about every possible interleaving in application code. The race becomes a deterministic error that one transaction receives and can handle.

The difficult reasoning should be reserved for the invariants that cannot be stated so directly.

Transactions are hard. Why do we want to hide them?

Transactions are difficult because atomicity, concurrency, failure and shared state all meet there. Hiding that difficulty behind abstractions does not remove it.

I want to be able to see the transaction in one place. That does not mean one enormous method or a stored procedure. It means the logical transaction program should be visible as a coherent unit: what it reads to make decisions, what it changes, where the physical boundary begins and ends, and what must remain true when it commits.

An approve_order transaction might establish that the order is awaiting approval, establish that the user is permitted to approve it, update the order and insert approval history. Once that program is visible, useful questions become easier to ask. Which read establishes a fact that must remain valid? Which constraint backs that assumption? Could two instances both pass the same check? Does the transaction need blocking, conflict detection or retry?

Those questions are hard enough without first reconstructing the transaction from controllers, services, repositories, ORM behaviour and annotations.

One transaction is still not enough

Making one transaction visible only gets us part of the way because a transaction does not race against “the database”; it races against other transactions.

Suppose approve_order is internally coherent. We still need to know what else can change the facts it relies on. cancel_order may change the order to cancelled. amend_order may change the amount being approved. A batch process may expire the order.

Now the useful questions are about interactions. What happens if approve_order and cancel_order begin from the same state? If amend_order changes the amount while approval is in progress, which amount has actually been approved? Which operation wins, and how does the loser find out?

Once transactions are named, we can identify the important ones that share an invariant or compete over mutable state and place their transaction diagrams beside one another on the same entity model. Only then does choosing an isolation level become a design decision about known interactions rather than a setting inherited from the framework.

There is an important warning here. The fix for a scope problem is not simply a larger physical transaction. A transaction held open across a payment provider, a message broker or a user’s attention span creates a different class of problem. Some business processes cannot be atomic and need explicit durable states, retries, compensation or an outbox instead. Deciding which invariants must be atomic and which must be recoverable is a separate design decision.

Testing the interactions

Yeah, but does it work? argued for exercising physical transactions against the real database. Concurrency is one reason. If approve_order and cancel_order can conflict, we can force a useful interleaving and verify that one blocks, aborts or observes the other’s change as the design requires.

Those tests are useful regression evidence, but they cannot prove the absence of every possible anomaly. Correctness also requires reasoning about scope, isolation, constraints and the other transactions that can invalidate an assumption. Testing can reproduce important races; it cannot replace understanding the concurrent program.

Other properties of the transaction

Concurrency is not the only concern that becomes easier to place once the transaction has a business name.

Authorisation is a good example. Applications often treat it as something that happens at the HTTP or service boundary: this role may call this endpoint. But real authority decisions often depend on the same persistent facts the transaction is about to change. A user may be allowed to approve orders only below a certain value, only for a particular entity, only when they were not the originator and only while the order is still approvable. That is not simply “role X may invoke method Y”; it is a precondition of approve_order.

If those facts can change, the definitive authority check belongs inside the same boundary as the change. Otherwise we have recreated the scope error: the facts that justified the decision can change before the decision takes effect. Authentication can establish who is asking at the system boundary; the transaction decides whether that identity may cause this state change.

Retry policy has the same property. A deadlock or serialization failure may justify another attempt, but every observation on which the decision depends must be made again inside that attempt. Retrying a transaction with values captured before the retry loop can simply repeat a stale decision.

Expected failures, audit and observability also become more meaningful at this level. The mechanisms may be cross-cutting, but their policy can still belong to one named business transaction.

The transaction contract

This leads me towards a small transaction contract. For an important state-changing transaction, I would like to know:

Some of that may eventually be executable metadata. Some will live in SQL, code, tests or a concise design description. I am less interested in inventing another framework than in making sure we can answer the questions.

The transaction name gives those views a durable point of reference. approve_order remains the same business operation if the HTTP route changes, the package is refactored, the repository disappears or the SQL is rewritten. Tests can use the same name that production metrics report, and security can grant authority over the same operation whose database activity we trace.

If we cannot name the important transactions, we will struggle to reason about how they interact. If we cannot see their logical programs, we will struggle to reason about races. If we cannot state their assumptions, we will accept isolation defaults without knowing whether they protect the business rule we care about.

A practical test

Take an intermittent production problem involving persistent data, or choose an important state-changing operation in a system you know well. Write down the physical transaction as one logical program. What facts does it observe? What decisions depend on them? What does it change? Where does the atomic boundary begin and end? If answering that requires following a large call graph and reconstructing propagation rules, note that as part of the problem.

Then identify the other named transactions that can change facts this transaction relies upon. Pick the most interesting pair and ask what happens if they begin from the same valid state and overlap. Which observations can become stale? Which constraint or isolation guarantee prevents an invalid result? Could the invariant be expressed as a constraint instead? If one transaction must abort, does the retry re-read everything the decision depends on?

Finally, look at the wider properties. Who is authorised to cause the transaction, and is that decision made inside the boundary? Which failures are normal business outcomes? What must be audited with the change? Can you identify the same transaction by name in a production trace?

A bug that appears one time in a thousand is not evidence that the database is unreliable. More often it is evidence that our sequential mental model collided with the concurrent system we had actually built.

Transactions are hard. That is not an argument for making them disappear from the design. It is an argument for making them easier to see.

References

Background

Where the detail lives