Database Isolation and Shared Balance Rules

Updated 8 November 2026 · Standard formula, shown below

Learn how database isolation can let two individually valid transfers violate a shared balance rule. You will trace a reproducible two-transaction example, identify the isolation behavior that permits it, and choose practical ways to prevent it.

What You’ll Need

A relational database or a transaction-capable database system that supports concurrent transactions.
A test table with two accounts and a rule such as “at least one account must remain active,” or an equivalent shared constraint.
Two database sessions or application workers, plus permission to set or inspect transaction isolation.
A disposable test database. Do not run concurrency experiments against live balances.

Step-by-Step

Write down the shared rule precisely. For example: among two accounts, at least one must have a positive balance. Start with account A at 100 and account B at 100. Each transfer of 100 from one account to the other is valid on its own, but suppose a separate operation deactivates the source account when its balance reaches zero. The rule might instead be that at least one account remains active. Choose a rule that depends on multiple rows; a check of only one row is not enough to demonstrate this anomaly.
Identify what each transaction reads and changes. Transaction T1 reads both accounts, sees two active accounts, and transfers all of A’s balance to B, leaving A inactive. Transaction T2 also reads both accounts before either change is visible, then transfers all of B’s balance to A, leaving B inactive. Each transaction’s decision is valid against the snapshot it observed. Together, their final changes can leave both accounts inactive, breaking the shared rule.
Set up two sessions and a repeatable schedule. In a scratch database, create the two rows and ensure the invariant holds at the start. Open sessions A and B. Begin a transaction in each using the isolation level you are investigating. Have each session read the rows needed to check the rule, then pause before writing. This coordinated interleaving is essential: sequential execution would let the second transaction see the first transaction’s result.
Run the first transfer in session A. Based on its read, update the relevant account rows and any status fields. Do not commit yet. Make sure the update changes rows that are different from the rows session B intends to change; this is the key setup for a write-skew outcome.
Run the second transfer in session B. Use the previously observed state to make the other valid transfer, updating the other account. If the database allows both transactions to proceed and commit, inspect the final rows and test the rule directly. The result may violate the invariant even though each transaction passed its own check.
Repeat at stronger isolation. Run the same schedule under the database’s serializable isolation mode. A serializable implementation must prevent a committed result that could not occur in some serial ordering. It may block one session or abort one transaction with a serialization conflict. Treat that abort as an expected outcome: retry the entire transaction, including its reads and validation, rather than retrying only the final update.
Compare the actual database behavior. Isolation-level names do not guarantee identical implementation details across database products. Record the database engine, isolation setting, read and write statements, commit outcomes, and final rows. Some systems’ repeatable-read modes detect this anomaly; others may allow it. Confirm behavior from the product’s documentation and a controlled test rather than relying on the label alone.
Choose a safeguard that protects the whole rule. Serializable transactions are a direct option when available and correctly handled. Another approach is to lock a shared guard row before checking the invariant, so competing transfers must serialize on that row. You can also redesign the data so a database constraint can enforce the invariant, or use an atomic conditional update where the rule can be expressed against the affected data. Verify that every code path follows the same protection.
Test retries and failure handling. Under serializable isolation, conflicts can be reported as transaction failures. Add bounded retry handling with a small delay or backoff where appropriate, and retry from the beginning. Log repeated conflicts and test with concurrent workers; a single-user test does not establish that the rule is safe.

Common Mistakes

Checking only one row: A row-level check constraint generally cannot enforce a rule about the combined state of multiple rows. Do not assume it does.
Assuming a transaction means serial execution: Transactions provide atomicity, but isolation determines what concurrent work can observe and whether conflicting schedules are rejected.
Testing without overlap: If one transaction commits before the other reads, both may appear safe. Coordinate the reads before either transaction writes.
Retesting only after a failure: A serialization retry must repeat the reads, decision, and writes. Reusing a stale decision can recreate the violation.
Assuming locks are automatic: Locking one account does not necessarily protect a condition involving other accounts. Lock the shared resource or use a mechanism that detects the predicate conflict.

Tips

Express the invariant as a query that can be run after every test, such as checking that the count of active accounts is at least one.
Keep the test schedule explicit: both reads first, then the two writes, then commits. Add barriers in test code if needed to make the ordering repeatable.
Prefer a database-enforced constraint when the rule can be represented reliably. Otherwise, document which transaction-level mechanism protects it.
Include concurrency tests in application testing, and test the production database engine rather than assuming another engine behaves the same way.

Troubleshooting

Both transactions commit and the rule breaks: Check whether they read a shared condition but wrote disjoint rows. Try serializable isolation or add a lock on a common guard row before reading. Ensure all writers use that safeguard.

The second transaction blocks: This may mean a lock is working. Check which row or predicate is locked and whether the blocked session can proceed after the first transaction commits. Look for inconsistent lock ordering that could cause deadlocks.

One transaction is aborted: Capture the database’s error details and confirm it is a serialization conflict or deadlock. Retry the complete transaction where safe, with a bounded retry policy. Do not suppress the error and report success.

Your test does not reproduce the problem: Confirm that both sessions read the same initial state before either writes, that their writes affect different rows, and that the selected isolation level permits the schedule. Check autocommit settings and database-specific behavior.

Frequently Asked Questions

What is write skew?
Write skew occurs when concurrent transactions read overlapping data, make decisions from that state, and write different rows. Both can commit while their combined result violates a rule.

Are the transfers themselves invalid?
No. Each can satisfy its checks against the state it read. The problem is that the shared rule is not preserved across the combined outcome.

Does repeatable read prevent this?
Not universally. Database products implement isolation levels differently, and repeatable read may still allow write skew. Check the engine’s documentation and test it.

Is serializable isolation always the best fix?
It is a strong general option, but conflicts may cause retries and affect throughput. A constraint, guard-row lock, or data-model change may fit a particular rule better.

Can an application check the rule before updating?
Yes, but a check followed by an update is unsafe if concurrent transactions can pass the check together. Put the check and changes under an effective database protection mechanism.

Should a failed transaction be retried automatically?
Only when the failure is understood and retrying is safe. Retry the full transaction with fresh reads, limit attempts, and surface persistent failures for investigation.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.