Technical Guide · Databases
Transaction Isolation: Lost Updates, Write Skew and Retries
Isolation determines how concurrent transactions observe intermediate/committed effects. Identical isolation names across databases do not guarantee identical application behavior.…
Technical review:
Architecture and operating model
Isolation determines how concurrent transactions observe intermediate/committed effects. Identical isolation names across databases do not guarantee identical application behavior. PostgreSQL Read Committed uses statement-level snapshots; Repeatable Read and Serializable offer different guarantees.
Lost updates can occur when applications write values computed from stale reads. Atomic UPDATE or appropriate locks may solve the relevant case. Write skew involves separate row changes jointly violating a business rule; locking one row does not protect every rule.
Serializable transactions can fail serialization and require bounded whole-transaction retries. Do not duplicate external payments/emails on retry; use idempotency or an outbox. Higher isolation does not fix unnecessarily long transactions.
- 1Business invariant
- 2Concurrent transactions
- 3Isolation/lock decision
- 4Commit/bounded retry
Design parameters
- Business invariant
- State invariants such as nonnegative stock or at least one on-call operator.
- Retry budget
- Define bounded retries, jitter and reporting for serialization/deadlock.
- Transaction duration
- Avoid holding a transaction while waiting for users or remote APIs.
Worked example
With stock five, two requests each want four. A SELECT followed by writing stock=1 can accept both. UPDATE inventory SET qty=qty-4 WHERE id=1 AND qty>=4 is an atomic condition; handle zero affected rows as insufficient stock.
Example commands: replace lab values and confirm permissions and software versions before use.
BEGIN;
UPDATE inventory SET qty = qty - 4 WHERE id = 1 AND qty >= 4;
-- Application checks affected-row count before COMMIT.
COMMIT;Troubleshooting
| Observation | Likely cause / distinction | Verification |
|---|---|---|
| Unexpected balance | Overwrite based on stale reads. | Reproduce with two sessions. |
| 40001 error | Serialization conflict. | Retry the entire transaction with bounded idempotent handling. |
Acceptance checks
- Document the invariant.
- Reproduce the race with two sessions.
- Inspect atomic-update results.
- Separate retry side effects.
- Reduce long transactions.
- Verify isolation on the engine version.
Related concepts
Transaction boundary
A transaction groups operations that an application treats as jointly successful or failed. Database atomicity does not automatically include email delivery or changes in other services. Define the commit point and retry behaviour. Evaluate idempotency to prevent processing the same request twice. Test connection loss before and after commit separately: a client-side error does not always mean the transaction failed.
Application consistency
A copy that boots does not prove application-data consistency. Operating-system caches, database logs and write ordering across disks or services affect the result. A crash-consistent copy resembles recovery after an unexpected shutdown; an application-consistent copy follows supported application preparation and write coordination. Validate transaction integrity, relationships between records and application behaviour after recovery, rather than only counting files. Confirm backup integration, application version and reported errors before assuming that consistency was achieved.
Locks, waits and deadlocks
Concurrent operations use locks or versioning to preserve integrity. Long transactions can block others; a deadlock forms a cycle of mutual waits. The database may terminate one participant, requiring safe application retries. Examine blocked and blocking queries together. Arbitrarily reducing isolation can change consistency guarantees. Test shorter transactions, consistent access order and suitable indexes under the same workload.
Durability and power loss
Survival of committed data depends on write guarantees across the database, OS, filesystem, controller and disks. Do not disable safety settings for speed without understanding caches and flush behaviour. Power-loss-protected storage helps but does not prove correctness of the entire chain. Run failure and recovery tests in a controlled lab, not production. Validate application records and supported consistency checks rather than merely opening a sample file.
Resource contention
Contention occurs when workloads sharing CPU, memory, storage or a link make each other wait. Apparent spare total capacity can hide a hot core or single-queue bottleneck. Correlate backup jobs, antivirus scans, index maintenance and user traffic on a common timeline. Confirm the bottleneck before adding resources. Run a workload alone and with its usual competitors to separate shared-resource effects, and report peak-hour latency alongside average utilization.
Isolation versus virtualization
Containers and VMs provide different isolation boundaries. Containers share the host kernel; VMs run guest operating systems. Rootless execution, namespaces and capability restrictions can reduce risk, but configuration and host security still matter. Images, running containers and persistent volumes have separate lifecycles. Updating an image does not back up data. Verify process privileges, mounts, network access and persistence after recreation separately.
Primary documentation
Prepared by the Doz Teknoloji technical team using the primary references below. Calculations and lab scenarios state their assumptions; validate the applicable product version before rollout.