Database transactions and ACID

What it means

Think of a transaction as a boundary around database work that must belong together. A proposed change is not the same as a committed outcome, and a successful commit is not proof that the application chose the right changes. The example isolates the first distinction: whether to retain a proposed value or discard the change. It does not connect to a database.

Try it

Simulation: begin with Original account value = 100, Proposed change = -20 and Transaction decision = rollback. Then change only Transaction decision to commit and inspect the result and explanation. Use Compare with baseline to display the baseline and current outcomes together; it compares local calculations, not database observations.

Stored value: 100. Rollback discards this toy uncommitted change and preserves the initial value.

Keep Original account value at 100 and Proposed change at -20. Only Transaction decision changes between the baseline and contrast.

Reset restores Original account value to 100, Proposed change to -20 and Transaction decision to rollback. It clears input errors and the baseline comparison. It preserves your independently chosen open or closed states for each FAQ answer and About this model.

How it works

A transaction boundary answers which database changes belong together. ACID separates that boundary from three other questions: whether the data obeys its rules, how concurrent work interacts, and what survives a failure.

Consider a synthetic transfer of 20 from balance A=100 to B=50. The intended result is A=80 and B=70, or both original balances if the work is rolled back. Putting both updates in one transaction prevents an ordinary transactional failure from leaving only one committed update. But if the application mistakenly submits only the debit and commits it, the transaction cannot invent the missing credit. Atomicity preserves the boundary you specified, not the business logic you intended.

The same distinction appears in a supported edge case in this local model: Original account value = 10, Proposed change = -20, Transaction decision = commit produces Stored value: -10. The arithmetic succeeds because the model has no rule requiring the resulting balance to be nonnegative. If that rule matters to a real application, it must be enforced; atomicity alone does not supply it.

Rollback scope also needs an engine-specific qualification. In PostgreSQL, sequence increments are not rolled back like ordinary row updates. That is another reason not to interpret “rollback” as “everything that happened nearby is undone.” The interactive scene models neither sequences nor a two-account transfer.

Worked example

This is a synthetic baseline, not a production observation.

The proposed arithmetic is 100 + (-20) = 80. Because the decision is rollback, the model discards that proposed change and selects the original value instead.

Stored value: 100. Rollback discards this toy uncommitted change and preserves the initial value.

Here, 80 is an explanatory candidate value, not a separate pending-state display or an observed database write.

What changes when the condition changes

Change Transaction decision to commit while keeping Original account value at 100 and Proposed change at -20.

Stored value: 80. The -20 change becomes part of the committed toy state.

The proposed arithmetic has not changed: 100 + (-20) is still 80. What changes is the decision about whether to retain that proposed value or discard the change. The model recalculates from the supplied original value; selecting commit again does not subtract another 20.

In practice

Before relying on a transaction in a real workflow, make a read-only review of the application design and database documentation using access you are authorized to use. Identify which database changes must commit together, which rules are actually enforced, and which effects—such as sending a message—happen outside that boundary. Check the engine's isolation and persistence settings rather than inferring them from the word “transaction.”

For PostgreSQL 18, the linked documentation separates these questions: section 3.4 covers transaction boundaries, section 5.5 covers constraints, section 13.2 covers isolation, and section 28.4 covers asynchronous commit. A useful review should identify both the intended behavior and the mechanism that provides it. If the mechanism or configuration is undocumented, the corresponding guarantee is not established.

STOP before blindly repeating a non-idempotent operation—one whose repetition could create another effect—after a connection loss around commit. First reconcile the application's stable operation identity with its authoritative outcome records. If you cannot establish the outcome with your authorized access, leave it unknown and involve the application or database owner. This page provides no real-database mutation or recovery procedure.

Limits and unknowns

The simulation evaluates one proposed change to one synthetic value. It has no concurrent transactions, database constraints, disk writes, crash recovery, email delivery or real connection. Its “Stored value” label describes the selected in-memory result, not evidence of a durable database commit.

Original account value accepts values from 0 to 1000; Proposed change accepts values from -1000 to 1000. Empty, nonnumeric or out-of-range numeric inputs are invalid. Invalid input removes the calculated outcome until you correct the value or use Reset; the scene does not silently retain an earlier valid result.

The model cannot determine whether a real workflow prevents lost updates, survives a crash or completed a commit whose response was lost. Those outcomes remain unknown from this example. Without JavaScript, the initial example and explanation remain readable, but the controls do not operate.

What to carry forward

Use a transaction to group the database changes that must belong together, but do not treat commit success as proof that the entire workflow is correct. Business rules, concurrent access, persistence and external effects each need their own evidence. When the client cannot confirm the outcome, keep it unknown rather than assuming rollback.