Transactions

Software Engineering / 22 Sep 2026 / 6 min read

Write Skew Can Break Invariants Under Snapshot Isolation

Write Skew Can Break Invariants Under Snapshot Isolation Snapshot isolation gives each transaction a stable view of committed data and usually rejects concurrent updates to the same row. That combination removes many anomalies that appear under weaker isolation levels. It does not, however, make every application invariant serializable. Write skew is the important edge case. Two transactions read overlapping state, make decisions from the same valid snapshot, then update different rows. Because their write sets do not collide, both can commit. The combined result can violate a rule that neither transaction violated in its own snapshot.

Software Engineering Databases

Software Engineering / 22 Sep 2026 / 7 min read

Saga Compensation Is Not Transaction Rollback

Saga Compensation Is Not Transaction Rollback A multi-service operation can cross inventory, payments, shipping, and other independently committed systems. Once one service commits its step, a later failure cannot make that earlier commit disappear through an ordinary database rollback. A saga handles this boundary by pairing forward actions with explicit recovery actions. If a later step fails, the coordinator invokes compensations for earlier completed steps where the business process permits them.

Software Engineering Distributed Systems

Software Engineering / 21 Sep 2026 / 8 min read

Write Skew Breaks Invariants Under Snapshot Isolation

Write Skew Breaks Invariants Under Snapshot Isolation Snapshot isolation gives each transaction a stable view of committed data. That property removes many anomalies caused by values changing midway through a transaction. It does not, by itself, make every concurrent execution equivalent to some serial order. Write skew is a compact example of the gap. Two transactions read the same valid state, make decisions from that state, then write different rows. Because their write sets do not overlap, both commits can succeed even though the combined result violates a rule that each transaction preserved in isolation.

Software Engineering Databases

Software Engineering / 21 Sep 2026 / 5 min read

Write Skew Breaks Cross-Row Invariants Under Snapshot Isolation

Write Skew Breaks Cross-Row Invariants Under Snapshot Isolation Snapshot isolation gives each transaction a stable database view and commonly prevents concurrent transactions from committing conflicting writes to the same row. That is a strong concurrency property, but it does not make every application invariant serializable. Write skew appears when two transactions read overlapping state, make decisions from the same valid snapshot, then write different records. Since their write sets do not collide, both commits may succeed. The combined state can violate a rule that each transaction checked before writing.

Software Engineering Databases

Software Engineering / 21 Sep 2026 / 8 min read

Transactional Outbox Closes the Dual-Write Gap

Transactional Outbox Closes the Dual-Write Gap A service often needs one request to change database state and publish an event. The two operations may look adjacent in application code, but they cross different durability boundaries. A database commit can succeed while a broker publish fails, or the publish can succeed before the database transaction rolls back. That split creates a dual-write problem. No ordering of two independent writes can make them atomic by itself.

Software Engineering Distributed Systems

Software Engineering / 19 Sep 2026 / 6 min read

Write Skew Breaks Cross-Row Invariants Under Snapshot Isolation

Snapshot isolation can let two transactions commit even when their combined result violates a rule that each transaction checked before writing. The anomaly appears when both transactions read the same logical condition, then write different rows. Because their write sets do not overlap, ordinary write-write conflict detection has nothing to reject. This is write skew. It matters at the boundary between application invariants and database isolation: a transaction may see a consistent snapshot and still participate in a final state that would have failed its own predicate.

Software Engineering Database

Software Engineering / 19 Sep 2026 / 8 min read

Transactional Outbox Couples State Change to Message Intent

A service that updates its database and publishes an event to a message broker crosses two independent commit boundaries. If the database commit succeeds and the broker publish fails, durable state exists without its corresponding message. Reversing the order only reverses the failure: a consumer can observe a message for a state change that never commits. A transactional outbox narrows this gap by placing the application write and a durable message record in the same local database transaction. Publication moves to a separate relay. The pattern does not make the database and broker one atomic system; it changes the boundary so message intent becomes part of the database commit.

Software Engineering Distributed Systems

Software Engineering / 15 Sep 2026 / 7 min read

Write Skew Escapes Row-Level Conflict Detection

Two concurrent transactions can each read a valid database state, update different rows, and both commit without colliding on a written row. The final state can still violate a constraint that spans those rows. No lost update is required; each transaction can preserve every value written by the other and still produce an invalid result. This anomaly is commonly called write skew. Its defining feature is that the conflict lives in the relationship among values rather than in two writes aimed at the same row. Isolation mechanisms that detect direct write-write conflicts therefore do not automatically protect every application invariant.

Software Engineering Databases

Software Engineering / 15 Sep 2026 / 6 min read

Savepoints Create Partial Rollback Boundaries

A database transaction does not have to choose only between keeping every statement and discarding the entire unit of work. In systems that support transaction savepoints, a transaction can mark an intermediate boundary, perform additional operations, then roll back changes made after that boundary while keeping the transaction itself active. That behavior makes a savepoint more than a convenience for error recovery. It creates a local rollback boundary inside a larger atomic unit, with semantics that remain tied to the surrounding transaction. Nothing before the final commit becomes durable merely because a partial rollback succeeded.

Software Engineering Databases

Database / 15 Sep 2026 / 6 min read

PostgreSQL Frozen Pages Bound Transaction ID Maintenance

A PostgreSQL heap page can reach a state where anti-wraparound vacuum no longer needs to inspect its tuple transaction IDs. The visibility map records this state with the all-frozen bit, allowing later aggressive vacuum work to skip the page until a data change invalidates that fact. This is separate from reclaiming dead tuples. A table with little update or delete activity can still require vacuum work because transaction IDs have a finite comparison range. Freezing converts sufficiently old tuple transaction metadata into a form that remains valid across transaction ID wraparound.

PostgreSQL Database

Database / 15 Sep 2026 / 5 min read

PostgreSQL Deferrable Unique Constraints Postpone Conflict Checks

A uniqueness rule can be valid for a transaction even when an intermediate statement temporarily creates duplicate keys. PostgreSQL supports that distinction through deferrable unique constraints: enforcement can move from each modifying statement to a later constraint-check point. This behavior changes transaction semantics rather than removing the rule. Duplicate values may exist transiently in transaction-local work, but a deferred constraint still has to be satisfied before the transaction can commit successfully.

PostgreSQL Database

Software Engineering / 14 Sep 2026 / 7 min read

Transactional Outboxes Move Atomicity Into the Database

A service updates an order row and emits an event about that update. If the database commit succeeds but the broker publish fails, durable state says one thing while downstream consumers receive no corresponding message. Reversing the order only moves the gap: a successful publish followed by a failed database transaction exposes an event for state that never committed. The difficulty is not message syntax or retry configuration. It is atomicity across two systems that do not share a transaction. A transactional outbox changes the boundary. The application writes its domain state and a message record into the same database transaction, then a separate relay publishes committed outbox records to the broker.

Software Engineering Distributed Systems

Database / 14 Sep 2026 / 5 min read

PostgreSQL Serializable Tracks Read-Write Conflicts

PostgreSQL Serializable isolation does not turn every read into a blocking lock. Transactions still execute against MVCC snapshots, while the database tracks read-write dependencies that can make concurrent execution inconsistent with every possible serial order. That distinction matters when an invariant spans multiple rows. Snapshot visibility can give each transaction a stable view and still permit a pair of writes whose combined result could not arise if the transactions had run one after another. Serializable Snapshot Isolation, or SSI, adds conflict detection around that snapshot model.

PostgreSQL Database

Database / 14 Sep 2026 / 4 min read

PostgreSQL Deferrable Constraints Shift Check Timing

Most PostgreSQL constraints reject invalid state as soon as the relevant statement is checked. That timing is usually desirable, but some valid multi-statement changes pass through a temporary state that violates a uniqueness, foreign-key, primary-key, or exclusion rule. A deferrable constraint changes the timing rather than the rule itself. PostgreSQL can postpone its check until transaction commit, allowing intermediate row states that would fail under immediate checking. The final transaction state must still satisfy the constraint.

PostgreSQL Database

Software Engineering / 13 Sep 2026 / 9 min read

Write Skew Across Disjoint Rows

Write Skew Across Disjoint Rows Two transactions read the same set of rows, reach compatible decisions, and then update different rows. Neither transaction overwrites the other’s write. Both commits can still leave the database in a state that violates a rule spanning those rows. That shape is write skew. It is easy to miss because many concurrency discussions center on two writers contending for one row. Write skew has no such collision. The conflict exists at the level of an invariant inferred from several records, while the physical writes remain disjoint.

Software Engineering Databases

Software Engineering / 12 Sep 2026 / 8 min read

Write-Ahead Logging and the Meaning of Commit

A database can report a transaction as committed while the data pages touched by that transaction are still absent from their final locations on disk. That behavior is not a contradiction. In systems built around write-ahead logging, durability is established by the log before the modified pages need to reach durable storage. The distinction matters because a transaction changes several kinds of state at once. It changes the logical database, it changes in-memory page images, and it creates recovery information. Treating those as a single physical write obscures the mechanism that gives commit its meaning after a crash.

Software Engineering Databases

Software Engineering / 12 Sep 2026 / 7 min read

Transactional Outbox: Moving Publication Across the Commit Boundary

Transactional Outbox: Moving Publication Across the Commit Boundary A service that changes database state and publishes a message has two distinct side effects. A local transaction can make the database change atomic, and a broker can accept the message durably, but those facts do not make the pair atomic. The awkward interval sits between them. If the database commit succeeds and publication does not, durable state exists without the corresponding message. Reversing the order only reverses the exposure: a message can become visible before the database commit succeeds.

Software Engineering Distributed Systems

Software Engineering / 12 Sep 2026 / 9 min read

Transactional Outbox at the Database-Broker Boundary

Transactional Outbox at the Database-Broker Boundary A database commit and a broker publish are two separate state transitions. An application can complete either one first, but unless both systems participate in a common transaction protocol, there is an interval in which one side has changed and the other has not. That interval is the central problem behind the transactional outbox pattern. The pattern does not make a database and broker commit atomically. Instead, it moves the durable publication decision into the same database transaction as the application state change. A separate publisher later converts that recorded intent into a broker message.

Software Engineering Distributed Systems

Software Engineering / 12 Sep 2026 / 10 min read

Prevent Write Skew with Serializable Transactions

Database transactions make many state changes easier to reason about, but transaction boundaries alone do not guarantee that every business invariant survives concurrency. A particularly subtle failure is write skew: two transactions read overlapping state, update different rows, and both commit even though their combined result violates a rule. This anomaly matters because each transaction can look correct in isolation. The defect appears only when valid decisions are made from snapshots that become incompatible once both writes are accepted.

Software Engineering Databases

Software Engineering / 12 Sep 2026 / 10 min read

Phantom Rows and the Limits of Row-Level Locking

A transaction can lock every row it reads and still leave a business rule exposed. The gap appears when the rule is about a set described by a predicate, not only the rows that currently satisfy it. Suppose an application limits a small allocation group to four active reservations. A transaction queries the active rows, sees three, and decides that one more reservation is valid. If another transaction inserts a new matching row before the first transaction commits, both decisions may have been based on a set that no longer represents the committed state.

Software Engineering Databases

Software Engineering / 12 Sep 2026 / 9 min read

Optimistic Concurrency with Version Columns

A row can be read correctly, modified correctly, and still be written incorrectly. The problem appears when another transaction changes the same logical record between the read and the write. A plain UPDATE often has no memory of the state on which the new values were based, so the later writer can replace an earlier change without detecting the race. A version column turns that hidden assumption into a predicate. The update says, in effect, that the write is valid only while the row remains at the version that the caller observed. The database then evaluates the state check and the mutation as one atomic statement.

Software Engineering Databases

Software Engineering / 12 Sep 2026 / 7 min read

Compensation Is Not Rollback Across Service Boundaries

Compensation Is Not Rollback Across Service Boundaries A local database rollback can erase uncommitted writes before other transactions are allowed to depend on them. A compensating operation has a different shape. It runs after an earlier operation has committed, often after that result has become visible to other components. That distinction changes the consistency model. Compensation does not restore a distributed system to a state in which the original action never occurred. It adds another state transition whose domain meaning offsets some consequence of the first one.

Software Engineering Distributed Systems

Software Engineering / 07 Sep 2026 / 11 min read

Understanding Two-Phase Commit

Suppose one business operation must update two independent transactional resources. Writing to the first and then the second creates an uncomfortable failure case: the first write may commit while the second fails. Reversing the order only moves the problem. Two-phase commit, usually shortened to 2PC, is a coordination protocol for making one commit-or-abort decision across multiple participants that can each prepare and commit a local transaction. Its purpose is atomicity across those participants: under the protocol’s assumptions, they do not intentionally finish with some participants committed and others aborted for the same transaction.

Software Engineering Distributed Systems

Database / 04 Sep 2026 / 11 min read

Prevent Lost Updates with Optimistic Locking in SQL

Two users can read the same database row, make different changes, and both believe their update succeeded. If the second write silently replaces the first, the application has a lost update. This is easy to miss because each individual SQL statement can be valid. The bug appears only when multiple requests overlap in time. One practical way to prevent this is optimistic locking: let readers proceed without holding a database lock, but make every write prove that the row is still the version the writer originally read.

Database SQL