Databases

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 / 8 min read

Optimistic Concurrency Rejects Stale Writes Before They Replace Newer State

Optimistic Concurrency Rejects Stale Writes Before They Replace Newer State A read-modify-write flow looks harmless when only one actor touches a record. A client reads state, changes part of it, then writes the result back. With concurrent actors, the interval between the read and the write becomes a race. Another writer can commit a newer value during that interval, and an unconditional update can erase it. Optimistic concurrency control puts a condition on the final write. The client carries a version derived from the state it read, and the storage layer accepts the mutation only if that version is still current. A mismatch becomes a conflict rather than a silent overwrite.

Software Engineering Concurrency

Software Engineering / 22 Sep 2026 / 6 min read

Optimistic Concurrency Control Rejects Stale Writes

Optimistic Concurrency Control Rejects Stale Writes Two clients can read the same record, make different edits, and save seconds apart. If each update blindly replaces the stored value, the later write can erase the earlier one even though both requests succeeded. Optimistic concurrency control prevents that silent overwrite by attaching a condition to the write. The client records a version when it reads the data. Its update succeeds only if that version is still current. A changed version turns the write into a conflict instead of an unnoticed loss.

Software Engineering Concurrency

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 / 5 min read

Version Columns Turn Lost Updates into Detectable Conflicts

Version Columns Turn Lost Updates into Detectable Conflicts A read-modify-write flow can overwrite another committed change even when every individual database statement succeeds. Two clients read the same row, compute different replacements, then write in sequence. Without a condition tying each write to the state it read, the later write can silently erase the earlier one. A version column makes that dependency explicit. The client reads both data and version, then updates only if the stored version is still the one it observed. A changed version turns the race into a failed conditional update instead of a lost update.

Software Engineering Databases

Software Engineering / 19 Sep 2026 / 5 min read

Version Columns Turn Database Updates into Conditional State Transitions

A database client can read a row, spend time computing a change, then issue an UPDATE after another transaction has already changed the same row. If the final statement identifies the row only by its primary key, the later write can replace state derived from the intervening transaction without any visible conflict. A version column changes that boundary. The client reads both the application state and a revision value, then includes that revision in the update predicate. The database accepts the write only while the stored revision still matches the state the client observed.

Software Engineering Databases

Software Engineering / 16 Sep 2026 / 7 min read

PostgreSQL SKIP LOCKED Turns Row Contention Into Visible Omission

A PostgreSQL query using FOR UPDATE SKIP LOCKED can omit a row that satisfies its predicate solely because another transaction already holds a conflicting row lock. The omitted row has not stopped matching the query. It is absent from that execution because lock acquisition would wait. That behavior changes the meaning of a locking read. Ordinary selection asks which visible rows satisfy a predicate. SKIP LOCKED adds an operational condition: among qualifying rows, return only those whose requested locks can be acquired without waiting at the point PostgreSQL attempts to lock them.

Software Engineering PostgreSQL

Software Engineering / 16 Sep 2026 / 6 min read

PostgreSQL Serializable Reads Track Conflicts Without Blocking Writers

A PostgreSQL transaction at SERIALIZABLE isolation can read a set of rows while a concurrent transaction writes data relevant to that read without the reader taking a blocking row lock. PostgreSQL preserves serializable outcomes by tracking read-write dependencies and rejecting a transaction when the observed dependency structure could admit a serialization anomaly. That mechanism differs from treating every read predicate as a barrier against matching writes. The database keeps MVCC snapshot behavior, adds SIReadLock state for dependency detection, and makes transaction retry part of the isolation contract.

Software Engineering PostgreSQL

Software Engineering / 16 Sep 2026 / 7 min read

PostgreSQL Sequence Values Survive Transaction Rollback

A PostgreSQL transaction can call nextval, roll back every row change it made, and still leave the allocated sequence value consumed. The row state returns to its earlier transactional form; the sequence allocation does not. This asymmetry is intentional and places sequence generators outside the rollback semantics developers often associate with database writes. That boundary matters whenever a generated identifier is treated as more than an opaque key. A sequence provides concurrent value allocation with atomic nextval calls. It does not provide a gapless ledger, a count of committed rows, or a transactionally reversible numbering stream.

Software Engineering PostgreSQL

Software Engineering / 16 Sep 2026 / 7 min read

PostgreSQL Savepoint Rollback Releases Later Locks

A PostgreSQL transaction can remain open while a lock acquired during part of that transaction is released. If the lock was acquired after a savepoint and execution rolls back to that savepoint, PostgreSQL releases the lock immediately rather than retaining it until the outer transaction ends. That behavior creates a lock-lifetime boundary inside a transaction. The common rule that transaction locks last until commit or rollback remains useful, but savepoints add a narrower scope for locks acquired after the marked point.

Software Engineering PostgreSQL

Software Engineering / 16 Sep 2026 / 7 min read

PostgreSQL NOT VALID Constraints Separate Installation From Table Verification

PostgreSQL can add a foreign key or CHECK constraint to a populated table without proving at that moment that every existing row satisfies it. With NOT VALID, the database records the constraint, enforces it against subsequent writes, and leaves a separate verification step for historical rows. That split creates a useful migration boundary. Constraint installation changes the rules for new data immediately, while VALIDATE CONSTRAINT later establishes that pre-existing data also conforms. The two operations have different work profiles and locking behavior, so treating them as one indivisible schema change can hide an important operational distinction.

Software Engineering PostgreSQL

Software Engineering / 16 Sep 2026 / 5 min read

PostgreSQL Foreign Keys Turn Reference Checks Into Row Locks

A PostgreSQL insert into a child table can block a concurrent transaction that tries to delete the referenced parent row, even though the two statements modify different tables. Foreign key enforcement is not only a value lookup. The database must also prevent the referenced key from disappearing before the referencing transaction reaches its boundary. That requirement creates a concurrency relationship between child writes and parent-row changes. The relationship is narrower than a general parent-row write lock: PostgreSQL has a row-lock mode specifically compatible with updates that leave key columns intact.

Software Engineering PostgreSQL

Software Engineering / 16 Sep 2026 / 7 min read

PostgreSQL Exclusion Constraints Express Pairwise Conflicts Beyond Equality

A PostgreSQL exclusion constraint can reject two rows even when none of their stored values are equal. Its rule is pairwise: for any two candidate rows, the configured operator comparisons must not all evaluate to true. That makes the constraint suitable for invariants such as non-overlapping time intervals, where scalar uniqueness does not describe the forbidden state. The mechanism is more general than a scheduling convenience. It turns an operator-defined notion of conflict into a database constraint, with an index access method participating in conflict detection. The exact operators, their null behavior, range boundaries, and any constraint predicate determine which row pairs are legal.

Software Engineering PostgreSQL

Software Engineering / 16 Sep 2026 / 7 min read

PostgreSQL Deferrable Uniqueness Moves Conflict Detection to a Transaction Boundary

A PostgreSQL transaction can temporarily contain rows that violate a unique constraint and still remain executable. That state is possible only when the constraint is declared deferrable and its current mode is deferred. The duplicate is not accepted as valid data; enforcement has moved from the statement boundary to a later constraint-check boundary. This timing distinction changes which multi-statement transformations are representable. It also changes where an error can surface, which statements can act as conflict arbiters, and what application code can safely infer from the success of an individual write.

Software Engineering PostgreSQL

Software Engineering / 16 Sep 2026 / 6 min read

PostgreSQL Advisory Lock Lifetime Follows Acquisition Scope

A PostgreSQL advisory lock can survive a transaction rollback when it was acquired at session scope. The SQL transaction may have ended with no committed data changes, yet the same database session can continue holding the application-defined lock until an explicit release or session termination. That behavior places lock lifetime at an interface boundary that is easy to blur in pooled applications. Advisory locks have application-defined meaning, but PostgreSQL still gives each acquisition precise server-side scope.

Software Engineering PostgreSQL

Software Engineering / 16 Sep 2026 / 7 min read

Long PostgreSQL Snapshots Delay Dead Tuple Reclamation

A PostgreSQL transaction can remain idle while still preserving a visibility horizon that constrains cleanup elsewhere. Rows updated or deleted after that transaction acquired its snapshot may become obsolete for newer transactions, yet some older versions can remain potentially visible to the retained snapshot. VACUUM cannot reclaim a row version merely because the newest application state no longer references it. This is a direct consequence of multiversion concurrency control. Visibility and physical reclamation are separate decisions: one transaction changes which row version is current, while the database must retain versions that can still be observed by relevant snapshots.

Software Engineering PostgreSQL

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 / 7 min read

Version Columns Turn Lost Updates Into Explicit Conflicts

Two clients can read the same database row, derive different changes, and then write in sequence. If each update replaces values without checking the state that produced its decision, the later write can silently erase part or all of the earlier one. The database has serialized the statements, yet the application-level read-modify-write operation has still lost a concurrent change. A version column changes the admission rule for the write. The update is accepted only if the row still carries the version observed by the client. A competing update advances that version, so a stale writer affects zero rows instead of overwriting newer state.

Software Engineering Databases

Software Engineering / 15 Sep 2026 / 5 min read

Schema Renames Create a Compatibility Interval

Renaming a database column is a single catalog operation in many relational systems, but an application deployment can make that apparently atomic change span several software versions. If an old process still sends statements containing old_name after the database exposes only new_name, the schema is valid and the process is valid in isolation, yet their interface no longer matches. The central issue is not the rename operation itself. It is the interval in which multiple application versions can reach one database. During that interval, schema evolution behaves like an API compatibility problem.

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

Software Engineering / 15 Sep 2026 / 7 min read

Connection Pools Turn Session State Into Shared State

A database connection pool reuses physical sessions across many logical borrowers. That reuse changes the lifetime of session-scoped state. A setting applied by one request can outlive the request itself because returning a connection to the pool usually ends only the borrower’s access to that connection, not the database session behind it. This distinction matters whenever application code changes properties that belong to the session rather than to a single statement or transaction. Transaction isolation, read-only mode, schema selection, session variables, advisory locks, temporary objects, prepared statements, and database-specific configuration can all have lifetimes that differ from the lexical scope of application code.

Software Engineering Databases

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 / 13 Sep 2026 / 10 min read

Version Columns Turn Lost Updates Into Conflicts

Two transactions can read the same row, compute different changes, and then write in sequence. If each update replaces values derived from its earlier read, the later write can erase part of the earlier one without either transaction observing a database error. A version column changes that interaction. The row carries a generation value alongside its domain fields, and an update is accepted only when the generation still matches the value observed by the writer. A stale writer no longer looks identical to a current writer at the storage boundary.

Software Engineering Databases