Data Integrity & Transactions
A system is only as trustworthy as its data is consistent. The dangerous failures are not the ones that crash loudly. They are the ones that leave the database in a state nobody designed: an order marked paid with no payment, or a balance debited but never credited. Transactions and integrity rules make those half-states impossible.
Integrity means every read reflects a consistent, valid state, and every write either fully happens or fully does not. The tools are concrete: database transactions for atomicity, constraints for invariants, idempotency for safe retries, and concurrency control for correctness when two operations race. Used together, they keep the data correct under failure and load.
In an AML or payments context, a broken invariant is not a small glitch. It is a wrong balance, a missed transaction, or a regulatory error. Multi-step operations that touch money or regulated state must be all-or-nothing. Retries must never apply the same change twice.
Make writes atomic and consistent
- AlwaysWrap any multi-statement change that must succeed or fail together in a single database transaction, with a clear commit and rollback boundary.
- DoWrite the action and its audit and side-effect records in the same transaction, so neither can exist without the other.
- DoKeep transactions short and focused. Do slow work, such as network calls and external APIs, outside the transaction, not while holding locks.
- DoPush invariants into the database (constraints, unique keys) so they hold even if the application logic has a gap.
- ConsiderFor work that spans services, use a saga or outbox pattern with compensating actions instead of a distributed transaction.
- NeverMake a money or regulated-state change as a series of independent writes that can partly fail and leave an invalid state.
Survive retries and races
- DoMake operations idempotent. Use an idempotency key or a guard so a retried request gives the same result, not a duplicate effect.
- DoChoose the isolation level on purpose. Use optimistic concurrency (a row version) or suitable locking where two writers can collide.
- DoDetect and handle the lost-update problem on read-modify-write paths, rather than letting the last writer silently win.
- ConsiderReturning the earlier result on a duplicate idempotency key, so retries are safe and visible.
- Do notAssume a request runs exactly once. Networks retry, users double-click, and queues redeliver.
- NeverRetry a non-idempotent operation (a charge, a transfer, a state transition) without a guard. A retried payment is a duplicate charge.
db.Execute("UPDATE Accounts SET Balance = Balance - @amt WHERE Id=@from", ...);
// the process crashes here
db.Execute("UPDATE Accounts SET Balance = Balance + @amt WHERE Id=@to", ...);
A crash between the two statements destroys money. It is debited from one account and never credited to the other. There is no way back to a correct state.
using var tx = conn.BeginTransaction();
conn.Execute("UPDATE Accounts SET Balance = Balance - @amt WHERE Id=@from AND Balance >= @amt", p, tx);
conn.Execute("UPDATE Accounts SET Balance = Balance + @amt WHERE Id=@to", p, tx);
audit.Record("Transfer", ..., tx);
tx.Commit();
Both legs and the audit record commit together or not at all. The balance check prevents overdraw. A crash rolls the whole thing back.
Self-review checklist
- AskIf this operation fails halfway, is the data still in a valid state?
- AskDo all the writes that must be consistent commit in one transaction?
- AskWhat happens if this runs twice? Is it idempotent, or does it apply the change twice?
- AskIf two requests hit this row at once, does the correct result survive?