Partial writes: find the boundary that was not atomic

The symptoms

• •A parent record exists in the primary table, but one or more child rows (line items, audit entries, outbox events) are missing for the same business transaction identifier.
• •Aggregate counters or balances disagree with their underlying row-level state immediately after the operation returns success to the caller.
• •Replaying the operation with the same inputs produces a different row count or a different set of persisted identifiers than the original run.
• •Log lines show the operation entering the persistence layer but no corresponding COMMIT, ROLLBACK, or equivalent terminal event at the expected log boundary.
• •Idempotency keys or dedupe tables contain records for transactions whose downstream effects were not fully applied.
• •Error responses or partial success payloads from upstream services were processed as if complete, leaving the local store inconsistent with the remote store.

Likely causes

• •A multi-statement write path that issues separate writes without an enclosing transaction, so each statement auto-commits independently and a failure between statements leaves earlier writes persistent.
• •An exception, timeout, or client disconnect that aborts the flow after one statement has committed but before subsequent statements run, with the application interpreting the outcome as failure but not compensating the earlier commit.
• •Connection-pool or session-level state (autocommit toggles, SET LOCAL, advisory locks, prepared statement caches) that is reused across requests and silently changes the atomicity boundary.
• •Upstream or downstream system returning a partial success payload (HTTP 207-style, partial batch acks, streaming commits) that the client treats as a full success and persists only the acknowledged subset.
• •Background workers, retries, or outbox consumers that re-enqueue work after a partial local write, causing duplicate or conflicting rows that mask the original partial boundary.
• •Schema or migration drift (missing NOT NULL, missing UNIQUE, mismatched FK) that turns what should be a single atomic write into multiple tolerant writes governed by different constraints.
• •Branching logic (early return, short-circuit validation, exception swallowing in a try/except) that persists validated data and then silently skips the remaining writes.

First ten minutes

1. 01Capture the exact business transaction identifier, request ID, and client timestamp from the calling system before any reproduction attempt, so later evidence can be correlated to a single attempt.
2. 02Pull the current row state for the parent and every documented child table using that identifier, and record which children exist and which are absent; this is the partial-write footprint.
3. 03Locate the persistence-layer log entry for the suspect operation (transaction BEGIN, SAVEPOINT, COMMIT, ROLLBACK, statement-completed events) and read the line order, not just keyword presence.
4. 04Check whether the connection used by the suspect code path is in autocommit mode or inside an explicit transaction at the time of each statement, by inspecting session/connection attributes captured in logs or metrics.
5. 05Map every code branch in the write path to a commit or rollback point, and mark branches where the function returns without reaching any terminal statement as suspect partial-write exits.
6. 06Stop further writes against the affected transaction identifier (mark idempotency key as seen, add a deny flag, or quarantine) so triage evidence is not overwritten by retries or background processors.
7. 07Record a snapshot of related caches, search indexes, materialized views, and outbox tables so you can later verify which downstream surfaces observed the partial state.

Evidence to collect

• •The full ordered list of statements (or API calls) the operation intended to execute, paired with the timestamp and connection/session identifier each one used.
• •The COMMIT, ROLLBACK, SAVEPOINT, and RELEASE SAVEPOINT events at the database, including their ordering relative to each statement and any error raised between them.
• •The application's interpretation of the outcome: returned HTTP status, returned payload, exception type caught and rethrown, retry decision, and idempotency token stored.
• •The state of child rows, junction tables, counters, outbox events, and audit logs keyed by the same business identifier, before and after the suspect operation.
• •Configuration evidence for the connection in use: autocommit setting, isolation level, statement_timeout, search_path, role, and any SET LOCAL values that were applied for the transaction.
• •Upstream or downstream response payloads (success, partial success, error, timeout) and the client code's branch that consumed each one.

Where to look

• •At the boundary between the application function and the database session: the point where a transaction is supposed to begin, span multiple writes, and terminate, but where control flow can exit early.
• •At the boundary between local persistence and the upstream service (HTTP RPC, message queue, object storage): the point where a remote partial-success response is consumed.
• •At the boundary between the synchronous request handler and asynchronous workers (outbox dispatcher, retry queue, change-data-capture stream): the point where work re-enters after a partial local commit.
• •At the boundary between logical validation (schema constraints, business rules, idempotency checks) and physical persistence: the point where a validated row is written but dependent rows are not yet written.
• •At the boundary between a parent table and its child tables governed by separate transactions, separate connections, or separate services in a microservice decomposition.

Diagnostic steps

1. 01Compare the intended step list for the operation against the observed write log; any step present in the intent but absent from the log is a candidate for the partial boundary.
2. 02For each present step, confirm whether its enclosing transaction committed by reading the COMMIT/ROLLBACK pair in the same connection's log stream, not by keyword search across all connections.
3. 03Reproduce the failure in a non-production environment with verbose statement logging enabled, using the same isolation level and connection-pool settings recorded in evidence, to map branches to commits.
4. 04Inspect the code path that handles exceptions thrown between statements: determine whether caught exceptions trigger a compensating write, a no-op, or an unconditional return that leaves earlier commits intact.
5. 05For upstream integrations, replay the exact request with the recorded payload and compare the response code, response body, and client-decoded success flag against what the production code observed.
6. 06Check whether the suspect connection had autocommit toggled or a SET LOCAL applied mid-transaction by a previous request that was reused, since reused sessions can carry over state.
7. 07For async or outbox-driven flows, drain and snapshot the retry queue and outbox table for entries older than the suspect timestamp, and match them to operations that lack full child rows.
8. 08Decide between two hypotheses before changing code: the partial boundary is inside one transaction (logical partial commit, usually a missing rollback on a caught error), or the partial boundary is across transactions (architectural partial commit, usually missing cross-service compensation or idempotent replay).

Common mistakes

• •Assuming a single database error message identifies the cause; partial writes are defined by what was already committed, not by what failed, so the error trail often points to an unrelated later statement.
• •Trusting a returned HTTP 200 or a wrapped success boolean as proof of full persistence when the underlying call returned a partial-success payload or when an internal exception was swallowed before the response was built.
• •Searching only for keywords like COMMIT or ROLLBACK in logs without correlating them to the same connection/session identifier, which causes commits from unrelated requests to be misattributed.
• •Adding a retry without first deduplicating by an idempotency key, which can re-execute writes that already partially committed and turn a partial-write diagnosis into a duplicate-row diagnosis.
• •Refactoring to a single transaction without first confirming that the failing path actually used a transaction at all, which can mask an existing partial-commit boundary instead of fixing it.
• •Relying on schema-level UNIQUE or FK constraints to make a multi-step write atomic; constraints prevent invalid end states but do not roll back already-committed prior statements.

Safe fixes

• •If evidence shows the write path issued multiple top-level statements with autocommit on, wrap the full sequence in a single explicit transaction (BEGIN ... COMMIT/ROLLBACK) and ensure the same connection is used for the entire sequence, so a failure between statements cannot leave half the writes durable.
• •If evidence shows a caught exception aborts the flow after some statements have already committed, add a SAVEPOINT before each independent write and ROLLBACK TO SAVEPOINT on error, or restructure so the commit point is reached only once at the end of the operation.
• •If evidence shows a reused connection carried over autocommit or SET LOCAL state, enforce explicit per-request transaction demarcation and reset connection state before returning the connection to the pool.
• •If evidence shows the client treated a partial-success upstream payload as a full success, parse the response against an explicit contract (per-item status) and persist only items the upstream acknowledged as durable, while routing unacknowledged items to a retry path.
• •If evidence shows duplicate retries masked the partial boundary, introduce an idempotency key tied to the business transaction identifier and check-and-set it inside the same transaction that performs the writes.
• •If evidence spans multiple services, model each cross-service write as an explicit two-phase or outbox step with a reconciliation check, rather than assuming one synchronous call equals one atomic effect.

Prove the fix

1. 01Replay the original failing request against a staging environment and confirm the parent row plus every documented child row exist under the same business identifier after a single attempt, with exactly one COMMIT event at the expected log boundary.
2. 02Inject a deterministic failure (simulated statement error, simulated upstream HTTP 500, simulated network drop) at each candidate boundary and verify that no committed child rows remain for the affected business identifier after the operation terminates.
3. 03Run a concurrent load that issues the same idempotency key across two clients and confirm the final state contains one full set of writes and one documented rejection, not two partial sets.
4. 04Run a reconciliation query that joins parent and child tables on the business identifier and asserts non-empty child sets and matching aggregate counters for every record created during a fixed observation window.
5. 05Verify by reading the post-fix logs that every COMMIT in the suspect code path is preceded by all intended writes and followed by no further writes for the same connection and request, for at least one full observation window.

Prevention and next steps

• •Define the atomicity boundary for every multi-step write operation in code review and in the persistence-layer logs: name the BEGIN/COMMIT span, the connection or session it must own, and the response that signals its success.
• •Require idempotency keys for any operation whose success may be retried, and store the key inside the same transaction that performs the writes so the dedupe check cannot be bypassed by a partial prior commit.
• •Reject partial-success upstream payloads at the integration layer by parsing per-item status and persisting only acknowledged items, rather than collapsing them into a single boolean.
• •Add a periodic reconciliation job that compares parent-to-child row counts and aggregate counters for each transaction completed in the last window, and alerts on any identifier whose children are missing or whose counts disagree.
• •Keep connection-pool and session settings out of application business logic; centralize autocommit, isolation level, statement_timeout, and per-request transaction demarcation in a single persistence helper so reused connections cannot silently change atomicity.

Safe commands and checks

List the last statements executed by a single connection session, replacing <pid> with the process identifier from the persistence-layer log: SELECT pid, state, query_start, LEFT(query, 200) FROM pg_stat_activity WHERE pid = <pid>;
Inspect transaction state for all active sessions to identify write paths that are unexpectedly outside a transaction: SELECT pid, xact_start, state, LEFT(query, 200) FROM pg_stat_activity WHERE state IS NOT NULL ORDER BY xact_start NULLS LAST;
Read autocommit and isolation settings for a connection session by replacing <pid> with the process identifier from the suspect log line: SELECT name, setting FROM pg_settings WHERE name IN ('default_transaction_isolation','default_transaction_deferrable','statement_timeout','lock_timeout');
List commits and rollbacks for a given database by querying transaction statistics, replacing <dbname> with the suspect database name: SELECT datname, xact_commit, xact_rollback FROM pg_stat_database WHERE datname = '<dbname>';
Find child rows missing for a parent business identifier, replacing <business_id> with the suspect identifier and <parent_table> with the parent table name: SELECT * FROM <parent_table> WHERE business_id = '<business_id>' AND NOT EXISTS (SELECT 1 FROM <child_table> c WHERE c.business_id = <parent_table>.business_id);
Search the persistence log for transaction terminal events tied to a request, replacing <log_path> with the log file path and <request_id> with the suspect request identifier: grep -nE 'BEGIN|COMMIT|ROLLBACK|SAVEPOINT|RELEASE' <log_path> | grep '<request_id>'
Recreate the suspect write transaction's intent in a non-production environment with verbose logging enabled, replacing <isolation> with the recorded isolation level, to confirm the statement order: SET TRANSACTION ISOLATION LEVEL <isolation>; SET log_statement = 'all'; -- then run the operation once and inspect the resulting log
Confirm the connection state after the operation returns, replacing <pid> with the process identifier from the suspect log line, to detect carried-over session settings: SELECT application_name, state, backend_start, xact_start FROM pg_stat_activity WHERE pid = <pid>;