Someone Changed the Data After Approval

In one line

Put the approved version into the condition and change in one go, and if even one item in the batch mismatches, roll back the whole thing. When you lose the completion response, check the fact with the receipt for the same change ID.

Why this was needed

In a job that cancels two orders, the first row succeeded and the second row had been changed first by another owner. If you judge that nothing changed just because the function raised an error, you are wrong. If each UPDATE was auto-committed, the first cancellation is already in place. The customer approved canceling both, but in reality the state is only half changed. Code that catches an exception and the atomicity of a business change are different devices.

Another problem is the response. If the execution program ends after the change and the audit are committed in the DB, the caller does not receive the success. If you rerun the same job and look only at the current state, you get a conflict saying it is no longer pending. To tell whether this was the success of the original run or another worker's change, you must durably record the approval contents that were applied and the change ID. Instead of a completion response, you need a receipt that you can check.

How it works

A single-row cancellation compares id, tenant, revision, qty, and pending all in the WHERE of the UPDATE. If they match, it changes state to cancelled, raises revision by 1, and receives the changed row with RETURNING. If no row is returned, the approval premise is gone, so it is a Conflict. If you do a SELECT comparison and then an unconditional UPDATE, another write can get in between the two statements, so the comparison itself goes inside the change statement.

Under PostgreSQL's default Read Committed, if another transaction is modifying the same row, you may have to wait. After the earlier transaction commits, the condition is checked again against the updated row. The checker actually creates this situation. While another connection increments the revision and holds the lock, it starts a cancel request, observes the Lock wait in pg_stat_activity, and then commits. The later request must not be able to cancel the current row with the old revision. It is evidence that is distinguished from a test that simply calls two functions one after another.

Multiple targets need one outer transaction. You design together the protection when cancel_one is used on its own and the protection of cancel_many as a whole. When psycopg's transaction context enters an already open transaction, it uses a savepoint, so you can compose things so that the single-row function does not commit the outer batch early. If an exception occurs at a later row, it is propagated to the outer context so that the earlier rows are rolled back too. An implementation that swallows the exception and continues to the next row does not fit this all-or-nothing apply contract.

The changes table for the receipt holds the change_id, tenant, and normalized targets. The per-row audit keeps the revision before and after the change and the quantity at that time. The receipt insert, the whole change, and the audit rows are all in the same transaction. On an error midway, none of the three should exist, and on a termination after commit, all three should exist. Writing the audit file separately and reconciling it later is not this lesson's atomic change record.

If the same approval content already exists for the same change_id, it returns False without changing anything new. If you submit a different customer or different targets with the same ID, it is a Conflict. If you treat a duplicate key as an unconditional success, a wrongly reused change number hides. Separating the retry of an identical request from the collision of a different request is the role of the receipt. Even if the same change comes in twice at the same time, only one should actually be applied and the other side should check the existing receipt.

What it looks like in the field

Having a receipt means that the change was confirmed at that time. It does not mean the state is still that way now. Another owner may have changed the quantity or deleted the row afterward. That is why inspect_change reads the approval contents of that time, and reconcile separately matches the current rows. It splits the results by ID into matching, changed afterward, and missing, and does not fill a missing one with another row of the same count. It also does not add a feature that undoes someone else's change on the basis of the reconciliation alone.

For example, if a row that had revision=4 at cancellation time has revision=5 now, it is classified as changed afterward even if the quantity and state are the same. Even if a cancelled row of a different ID newly appears and the total count stays the same, if the original target has disappeared, it is missing. If you report with a single number, these two cases can look normal. To the customer, you have to explain the confirmed record of that time and the current reconciliation result side by side, and you must not invent a cause arbitrarily just because there is a difference.

The lab DB's settings differ from production durability standards. In the existing image, fsync and full_page_writes are turned off, so this test only verifies the termination of a client attached to a live PostgreSQL server. You do not extend the observations that a connection drop before commit is rolled back and that the receipt is found after reconnecting following a commit into a guarantee of recovery from a server power failure or disk corruption. When you take this to a real service, production storage, backup, and restore verification are needed separately.

What you will do in the next lab

You implement it in 8 steps, starting from approval plan validation and going through single-row and batch changes, the audit receipt, reconciliation with the current state, and the connection lifetime. At the end, you kill a real process before and after the commit and try again with the same change ID. Instead of looking only at requests that succeed, you also check the wrong answers: another customer, an old version, a duplicate ID, a partial commit, and confusing the current state with the receipt.

References: PostgreSQL isolation levels, psycopg transaction management. The quantity and version ranges, the receipt schema, and the exception classification are the contract of this lab.