Why PostgreSQL Cross-Row Rules Fail: Write Skew, Serializable Transactions and Guard Rows

Two transactions can both pass a check, update different rows and jointly break a business rule. Scoped to PostgreSQL 18 on one primary, an approval-group example reproduces write skew, compares full Serializable retries with a Read Committed guard-row protocol, and provides acceptance tests for stale snapshots, bypass writes, retries and unknown commit outcomes.

When a business rule depends on several rows together, a stable snapshot and a successful single-row update are insufficient. Protect the complete read-and-write decision with serializable semantics, or coordinate every relevant write through a common guard object. This article uses approval-group membership on one PostgreSQL 18 primary, with example two-session procedures and expected results. The database guarantee must be tested together with actual connections, retries and authorization paths.

Express the invariant as a checkable condition on a set

Consider enabled approval group G in tenant T, with two active members, A and B. The business rule requires at least one active member after every committed membership change. This concerns active records in the system, not whether a person is currently online or authorized for a particular approval. Cross-database membership synchronization is outside this scope.

Define N(T,G) as the number of members with active=true in that tenant and group. An enabled group must satisfy N(T,G)≥1. Before deactivating an active member, confirm that the target exists and N≥2. If the target is already inactive, return the agreed no-change result rather than subtracting again. Disabling the entire group is a separate business operation with its own approval and workflow; it must not become a shortcut around membership rules.

The example schema is approval_group(tenant_id, group_id, enabled), with a composite primary key on tenant_id and group_id; and approval_member(tenant_id, group_id, member_id, active), keyed by all three identifiers. A foreign key on its first two columns references the group. Both enabled and active are boolean NOT NULL. Initialize group (T,G,true) and members (T,G,A,true) and (T,G,B,true), then verify that the count is two. The maintenance identity must be able to read the complete member set within its authorized tenant. If row-level policies or views hide members, first resolve their effect on the count definition.

Primary keys prevent duplicate identities, the foreign key prevents orphan groups, and NOT NULL removes ambiguous status. None expresses “at least one active member.” PostgreSQL 18's constraint documentation explains that ordinary CHECK constraints cannot maintain this guarantee using table data outside the checked row.[1] Hiding a cross-row count inside a custom function does not solve concurrency automatically. Prefer native unique or exclusion constraints where they express the rule; this example needs a coordination protocol.

SQL query snapshot consistency concerns whether a read result mixes versions. Here the problem goes further: each transaction can see a complete, stable old version while two individually sound decisions produce an invalid committed state. Acceptance must examine the business invariant after all successful transactions commit.

Reproduce the failure: both checks pass, but no approver remains

Initialize the three records in an isolated test database. Open two connections and execute BEGIN ISOLATION LEVEL REPEATABLE READ; in each. Both then execute SELECT count(*) FROM approval_member WHERE tenant_id='T' AND group_id='G' AND active;. Use a manual barrier or test driver to confirm that both reads have completed and returned two before either update begins. Starting two threads at approximately the same time does not reliably establish this interleaving.

After checking that A is active, connection one executes UPDATE approval_member SET active=false WHERE tenant_id='T' AND group_id='G' AND member_id='A' AND active RETURNING member_id;. Connection two performs the corresponding update for B. Each statement returns its target identifier, and each connection then issues COMMIT. Production code should bind parameters; the fixed literals here display a common set of test inputs.

With this controlled ordering and no additional triggers or constraints, Repeatable Read permits both commits because the transactions update different member rows. A new connection reads N=0. Each transaction's snapshot still shows the other member as active. Either transaction alone would leave one member, yet their concurrent result violates N≥1. This anomaly, involving decisions over the same set and writes to different records, is called write skew.

It is not a lost update: both row changes survive, and neither transaction reads uncommitted data. PostgreSQL 18 distinguishes Repeatable Read's stable view from Serializable guarantees; the former still permits serialization anomalies.[2] Condensing each operation into a conditional statement does not by itself remove the anomaly if its cross-row condition still uses the transaction's own snapshot.

Try ordering the two complete operations. If A is removed first, B subsequently sees only itself and must be refused. If B goes first, A must be refused. No serial ordering that obeys the defined checks lets both succeed. The issue therefore lies beyond either UPDATE's returned row: can the commits correspond to a legal sequence of complete decisions?

Option one: put the complete decision inside Serializable

Change both transactions to BEGIN ISOLATION LEVEL SERIALIZABLE;. Continue reading group status, target membership and active count within the transaction, then check, update and commit. Select this isolation level at the start, not after reading an old count. Nor should the count come from a report cache or replica while only the UPDATE is wrapped in a Serializable transaction.

Repeat the interleaving in which both read two before updating different members. The database must not allow both contradictory transactions to commit successfully. Typically one completes and another receives a serialization failure. Failure can surface during a statement or at commit, so do not assume connection two must lose or that the error must occur at COMMIT. Aborted changes roll back together, and a fresh independent read must still find at least one active member.

Serializable Snapshot Isolation, or SSI, is PostgreSQL's mechanism for implementing serializable execution. Ports and Grittner's implementation paper explains dangerous structures of read/write dependencies and why the system can conservatively abort some executions that could have been serialized.[3] A conflict error does not mean data is already corrupted, nor does every abort prove that a complete execution history would have formed a cycle.

In this example, connection one's observation that B is active conflicts with connection two's change to B; connection two's observation about A creates the reverse dependency. Tracking these relationships differs from checking whether transactions write the same row. SSI's SIReadLock tracks read dependencies rather than making every read a blocking mutex.[2] Ordinary row updates can still wait for locks, so Serializable should not be advertised as eliminating waiting.

The guarantee requires each transaction to follow the right rule when run alone and all relevant paths to follow a consistent strategy. The application-consistency documentation emphasizes coverage of related reads and writes.[4] A maintenance script that directly clears membership without checking N violates the rule even serially. Serializable cannot invent a business check the application omitted.

A retry must repeat the decision, not the old conclusion

PostgreSQL identifies serialization failures with SQLSTATE 40001. Its documentation requires retrying the complete transaction, including logic that chooses SQL statements and values.[5] Here the losing request must begin again, reread membership and N, and decide anew. Retaining N=2 and retrying only the last UPDATE is incorrect. A 40001 asks for a new decision, not completion of the old decision at any cost.

Suppose A was successfully deactivated and B's transaction failed. On a complete retry, B sees N=1 and must return a business refusal because it would remove the final active member. B remains active. Retrying until B also becomes inactive breaks the condition even if subsequent database calls succeed. Business refusal, transaction conflict and call timeout need distinct states for users and operators.

An implementable control flow preserves a stable business intent and its parameters, opens a transaction within the overall deadline, reads and checks, changes the data and records the result, then returns only after confirmed COMMIT. On 40001, roll back, discard this attempt's readings and pending response, back off within limits, and restart at the transaction boundary. At the time or attempt limit, return a state that supports later investigation rather than claiming execution. Backoff and deadlines depend on workload and business requirements; there is no universally safe retry count.

SQLSTATE 40P01 denotes a deadlock and generally calls for rollback before considering a complete retry. Unique violations, foreign-key errors, authorization failures and invalid parameters need separate analysis; they should not all receive 40001 handling.[5] Verify whether the driver starts a fresh transaction and whether the old connection remains in a failed transaction. Sending another statement after catching an exception may merely produce another “transaction aborted” error.

Do not send emails, call external approval services or execute irreversible model-selected tools inside a retryable transaction. Database rollback cannot retract those effects. If notification is required, record a pending event with the membership result and send it after commit under a stable event identity. Delivery and deduplication still need their own design. The transaction protects a database decision, not a common commit across every external system.

Option two: lock a stable guard row at Read Committed

For infrequent membership changes, stable group boundaries and controllable write entry points, use the approval_group row as a guard. Every operation affecting the group's invariant first locks that object on the same connection and transaction, then reads membership and decides. The guard represents coordination over the whole group; it is not an arbitrary currently existing member used as a temporary lock.

The sequence is BEGIN ISOLATION LEVEL READ COMMITTED;, followed by SELECT group_id FROM approval_group WHERE tenant_id='T' AND group_id='G' AND enabled FOR UPDATE;. Confirm exactly one returned row. In a separate subsequent statement read the target state, then read the active count. Update and COMMIT only if the conditions hold. A missing lock result means the group is missing or ineligible; do not skip coordination and continue changing members.

Let A acquire the guard first and deactivate itself while B waits on the same guard. After A commits, B acquires the lock. B's subsequent membership query under Read Committed sees A as inactive and N=1, so it refuses B's removal. If A rolls back instead, B reads N=2 and may proceed. Either branch retains at least one member. Waiting for a lock is not business success.

Separating lock acquisition from the count query matters. Do not combine the guard lock and membership read into one statement or CTE merely to save a round trip, then assume waiting refreshed every read within that statement. The required evidence is that the decision-making membership query starts after the guard lock is acquired. Reads, changes and commit must remain in one transaction.

Do not transplant this protocol into a Repeatable Read transaction that already has an old snapshot. If the guard was merely locked rather than updated, a waiter may acquire it and still see two active members through its old snapshot. The documentation explicitly distinguishes lock timing from snapshot timing.[4] This design deliberately uses subsequent statements at Read Committed. Retaining Repeatable Read requires a fresh argument for a complete alternative, such as actual guard-version updates with whole-transaction retries.

Protocol coverage determines whether the set is protected

A row lock protects the locked row; it does not automatically forbid every modification to the member set.[6] Additions, deactivations, deletions, bulk imports, transfers and relevant group-configuration changes must follow the same guard protocol. A batch job or administrator script that writes directly can break the rule even if the regular API passes testing. Write privileges can be concentrated in controlled procedures, with authorization designed around actual maintenance needs.

Pre-create the guard and identify it uniquely by tenant and group. SELECT FOR UPDATE on a nonexistent group returns no row, not an acquired lock. A new group can be created together with its first active member in one transaction and exposed only after initialization conditions hold. If empty drafts are allowed, distinguish the disabled draft state from acceptance for activation; do not include initially invalid states in the guarantee assumed here.

A transfer affects both source and destination groups. Acquire both guards in a consistent tenant-and-group-key order, then check and change membership in one transaction. Consistent ordering reduces opposite waits and is a principal deadlock-avoidance technique in the locking documentation.[6] If only one active source member remains, add another through the business workflow or reject the transfer. Do not commit removal from the source and then wait to write the destination.

Materializing active membership as a counter would allow a conditional update on one row, but the counter, member records and every state transition would need atomic maintenance and continuing reconciliation. This may reduce scans while adding derived state and repair responsibility. Do not silently change the business definition to fit a single-row design: member deactivation, deletion and disabling the group are different operations.

The tradeoff is concrete. A guard queues changes within a group and is easy to explain, but requires complete coverage of write entry points. Serializable allows decisions to begin concurrently and protects legal ordering through aborts, but requires replayable transactions and retry capacity. Complex relationships and frequently changing access sets favor serious evaluation of the latter. A few clear aggregate roots, short transactions and controlled hotspots can make the former easier to implement. Neither replaces authorization.

Six failure tests: triggers, evidence, corrections and retests

First, reproduce the original anomaly. Start with two active members and force two Repeatable Read transactions to read two before each removes a different member. The baseline can allow both commits and leave zero; under Serializable, both must not succeed, and a complete retry by the loser must read one and refuse. Preserve statement order, actual isolation levels, SQLSTATE and final rows. If zero remains after the fix, inspect reads outside the transaction and bypass writers, then reverse commit order and retest.

Second, verify guard waiting and rollback. A holds the guard and performs an uncommitted deactivation; B requests the same lock. B must not perform its deciding count before acquiring it. After A commits, B must read one and refuse; after A rolls back, B must read two and may proceed. Record backend connection identities, wait stages and member state. If B decides early, fix transaction-object propagation, pooling and statement order, then rerun both branches.

Third, expose a stale snapshot deliberately. Misconfigure the guard design as Repeatable Read and have B read membership before A commits. After A removes its member, B acquires the guard that was only locked and rereads the count. This flawed implementation can still see two and violate the rule. With the prescribed Read Committed sequence, it must see one. Record when the snapshot was established and whether the guard was actually updated; a FOR UPDATE entry in a log is insufficient proof of repair.

Fourth, test uncovered entry points. Run a bulk-deactivation script concurrently with a regular removal in the same group. A compliant system must make the script obey the protocol or reject uncontrolled writes through permissions. It must not leave an enabled group with N=0. Restrict a discovered bypass, add the protocol and least necessary privileges, then repeat the same script and interleaving. Inspect other maintenance jobs rather than fixing only the user-facing API.

Fifth, test retries and external effects. Trigger 40001 with an initial count of two and a retry count of one. Expect business refusal, no further reduction and no external notice representing rolled-back success. Record attempt numbers, stable business intent, each reading and commit outcome. If only UPDATE is retried or actions are duplicated, repair the retry boundary and post-commit delivery, then inject failures after the update and before commit in the test environment.

Sixth, test missing rows, tenant boundaries and transfer deadlocks. Missing groups, inactive targets and incorrect tenants must not become unconditional writes. Construct reverse-order transfers, then retest with consistent locking order. Expect an explainable no-change result, business refusal or rollback, with N≥1 in every enabled group afterward. Check tenant-inclusive guard keys, affected-row counts and whole-transaction rollback; an HTTP 200 response is not database evidence.

Include unknown commits and operating capacity in the delivery design

A connection failure after COMMIT differs from an explicit 40001. The operation may already have committed, so do not announce rollback and execute under a new business identity. For stable responses, store an operation result and parameter digest keyed uniquely by tenant and request_key in the same transaction. On recovery, query the primary using the original key and verify parameters. Keep an unresolved outcome unknown; an empty result on a lagging replica does not prove failure.

Repeatedly setting a status to false can appear harmless, yet it may cross an intervening reactivation. Distinguish retrieving the prior result from making a new removal request now. The request ledger and current membership state serve different purposes. An old key should return the original operation result instead of re-evaluating a new business intent. Ledger writes belong to the transaction and roll back with membership changes. Retention must cover the allowed retry window.

Capacity evaluation should record total attempts per business intent, 40001 and deadlock counts, guard waiting, transaction duration and final success or refusal. For an illustrative 1,000 original intents with 150 additional attempts, the database executes 1,150 attempts. Extra attempts divided by original intents equals 15%. That does not mean 15% of intents were retried: a smaller subset may retry repeatedly. Record both denominators separately.

A hot group's guard necessarily constrains concurrent changes to that group; more connections cannot make conflicting updates safely proceed together. Serializable may incur more conflict work with hotspots, wider scans or long transactions. Test actual group sizes and hotspot distributions, measuring queues and repeated work separately. The historical paper's benchmark is not a throughput commitment for this system. Tune indexes and connection counts from actual plans and measurements.

Keep only operations needed for the invariant inside the transaction. Do not wait there for model prose, human confirmation or remote-service responses. Set explainable duration limits and confirm transaction and connection cleanup after timeouts. Before rollout, inventory all change entry points and inspect existing enabled groups, then switch gradually under one coherent protocol. Rollback must preserve a complete controlled path rather than mixing half a guard design with old checks. Deliver the invariant, two-session timelines, writer inventory, failure-retest evidence and named recovery owner.

References

  1. [1] PostgreSQL 18 — Constraints (accessed 2026-11-22)
  2. [2] PostgreSQL 18 — Transaction Isolation (accessed 2026-11-22)
  3. [3] Ports and Grittner — Serializable Snapshot Isolation in PostgreSQL (PVLDB 2012)
  4. [4] PostgreSQL 18 — Data Consistency Checks at the Application Level (accessed 2026-11-22)
  5. [5] PostgreSQL 18 — Serialization Failure Handling (accessed 2026-11-22)
  6. [6] PostgreSQL 18 — Explicit Locking (accessed 2026-11-22)
Back to insights
鲁ICP备2024109755号-2
Drag to move. Right-click, touch and hold, or press Shift+F10 to choose a corner.