Transaction plus external side effect split-brain: locate the commit boundary

The symptoms

• •Database row exists for an order/payment/user but the downstream side effect (email queued, message published, webhook delivered, payment captured) did not fire or fired twice.
• •Reconciliation report shows a positive delta on the database side and a negative or duplicate delta on the external system side for the same business identifier.
• •Application logs show COMMIT followed by an exception in the next statement, with a partial HTTP response body or partially parsed message envelope already accepted by the peer.
• •PostgreSQL pg_stat_database xact_commit increases but the application error stream reports a thrown exception after the commit log line.
• •Idempotency token is missing or scoped only to the database write, not the external call.
• •Retry or job-queue replay produces duplicate external effects even though the database row is uniquely constrained.

Likely causes

• •Code commits the database transaction and only then invokes the external side effect, so any failure between COMMIT and the call leaves permanent divergence.
• •External system lacks a transactional outbox or two-phase commit handshake, so the boundary between durable state and observable action is a one-way door.
• •Auto-commit mode is implicit and the application treats multiple statements as one logical unit, so partial rollback semantics are not what the operator assumes.
• •Retry loop on the external call is not de-duplicated against an idempotency key, so a 5xx replay duplicates the side effect while the database row remains unique.
• •Connection pool returns the session to the pool after COMMIT but before the external call resolves, creating interleavings where a sibling transaction reads a not-yet-visible-but-soon-to-be state.
• •Clock or ordering assumptions on the external system mean a logically earlier call is processed after a logically later one, inverting the order around the commit boundary.

First ten minutes

1. 01Freeze retries and disable any background worker that fans out side effects, so further divergence does not occur while you inspect.
2. 02Capture the exact commit log line: identifier, transaction start timestamp from pg_stat_activity if available, and the byte offset or row count at COMMIT.
3. 03Read the application trace from the top, not the bottom: identify the first line that says COMMIT or issues a commit-implicit DDL, then read the next non-trivial statement.
4. 04Pull the external system receipt for the business identifier: HTTP status, request ID, delivery timestamp, and any retry or replay counter.
5. 05Compare COMMIT-to-side-effect wall-clock gap against the external system's stated idempotency window; a gap longer than that window is a strong tell.
6. 06Decide which side is the source of truth for this business action before changing anything; record the decision in the incident note.

Evidence to collect

• •PostgreSQL commit count and rollback count around the incident window via pg_stat_database, scoped to the database that owns the affected table.
• •Application log lines for the suspect transaction: BEGIN, COMMIT/ROLLBACK markers, the first statement after COMMIT, and any thrown exception class with stack frame.
• •External system access log or message broker delivery log keyed by the business identifier and any idempotency key the client supplied.
• •Connection pool checkout and return timestamps for the connection that ran the transaction, to test whether the session crossed the side-effect boundary.
• •Configuration snapshot: autocommit setting, isolation level, transaction manager, retry policy, and idempotency key generation rule.
• •Order of operations document or sequence diagram, if any, with the COMMIT line clearly marked.

Where to look

• •The commit boundary in code: the line where the transaction manager commits and any statement that immediately follows it.
• •The application process or worker boundary: any thread, queue consumer, or scheduled job that performs both the database write and the external call in the same logical unit.
• •The PostgreSQL statistics boundary: pg_stat_database for commit/rollback counters and pg_stat_activity for in-flight transactions on the affected database.
• •The external system boundary: HTTP access logs, message broker delivery logs, payment gateway transaction lookups, email service provider event streams.
• •The retry and queue boundary: the de-duplication store, dead-letter queue, and idempotency cache used by the caller.
• •The reconciliation boundary: nightly jobs that diff database state against the external system, which is where the divergence first becomes visible.

Diagnostic steps

1. 01Read the code path linearly from request entry to final return, marking each persistence boundary (BEGIN, COMMIT, ROLLBACK) and each outbound call (HTTP, message publish, email send).
2. 02Compare commit count to side-effect call count for the suspect window; a delta larger than the documented retry policy suggests replay without de-duplication.
3. 03Inspect the external system for the business identifier: if exactly one record exists and it predates the database commit, the external call ran before commit but was acknowledged after.
4. 04Inspect the external system for duplicates: if more than one record exists with the same idempotency key, the retry path bypassed the idempotency cache.
5. 05Verify autocommit by querying the application configuration or the driver settings on the suspect connection; implicit autocommit changes rollback semantics.
6. 06Check pg_stat_activity for a backend stuck in idle in transaction during the incident window, which indicates the COMMIT was deferred past the external call.
7. 07Confirm whether the external system supports an idempotency contract and whether the caller actually sends a stable key; absence of a key is a structural cause, not a transient fault.
8. 08Decide boundary ownership: who owns the source of truth, and what compensating action (database correction vs external reversal) is permitted by policy.

Common mistakes

• •Treating COMMIT and the external call as one atomic unit when they are not, and writing rollback logic that assumes the database can reverse the external effect.
• •Logging only the success path, so when COMMIT succeeds and the external call fails, there is no record of which side actually committed first.
• •Adding retries without an idempotency key, which converts a single partial failure into multiple duplicate side effects while the database row stays unique.
• •Reading the stack trace bottom-up and concluding the database is at fault, when the root cause is a thrown exception in the statement after COMMIT.
• •Running a compensating transaction on the database without first checking whether the external system already executed the action, compounding the split-brain state.
• •Assuming the reconciliation job is wrong when it reports divergence; the job is usually the first honest signal that the boundary is leaking.

Safe fixes

• •Move the external call behind a transactional outbox: write the side-effect intent into a database row inside the same transaction, then have a separate worker drain the outbox and invoke the external system with an idempotency key.
• •Add a stable idempotency key derived from the business identifier plus a per-attempt nonce, and pass it on every retry of the external call.
• •If an outbox is not feasible, scope a compensating action narrowly: for each affected business identifier, query the external system first, then apply the smallest database correction that restores consistency.
• •Bound the retry window on the external call to the documented idempotency window of the peer system; beyond that window, treat the call as needing human review.
• •Capture the commit timestamp from the database and persist it alongside the outbox row, so the worker can prove ordering if the peer reorders by clock.
• •Document the source-of-truth decision per business action in the runbook, so operators do not improvise conflicting compensations during an incident.

Prove the fix

1. 01Replay a controlled test transaction: induce a forced failure between COMMIT and the external call, and verify that no external side effect is observed; the outbox row should be the only artifact.
2. 02Replay a controlled retry: force the external call to return 5xx twice and succeed on the third attempt, and verify the peer records exactly one side effect for the supplied idempotency key.
3. 03Diff pg_stat_database xact_commit against the outbox-drained counter over a full reconciliation window; the two counters should agree within the documented retry policy.
4. 04Run the reconciliation job against a fresh dataset and confirm zero delta on the affected business identifier within the idempotency window.
5. 05Inspect an alert rule that fires when COMMIT-to-side-effect wall-clock gap exceeds the peer's idempotency window, and confirm it triggers on the synthetic fault and not on healthy traffic.

Prevention and next steps

• •Adopt the outbox pattern as the default for any code path that combines a database write with an external side effect; review exceptions in design review.
• •Make idempotency keys mandatory at the client SDK level for all external calls, not optional at the call site.
• •Emit a structured log line on COMMIT and a matching line on the external call completion, with the same correlation identifier, so divergence is grep-able.
• •Add a regression test that forces a fault between COMMIT and the external call and asserts no observable external side effect, kept in the suite that runs on every change.
• •Track commit-to-side-effect gap as a service-level indicator, with an alert when it exceeds the minimum idempotency window across integrated peers.

Safe commands and checks

psql -d <dbname> -c "SELECT datname, xact_commit, xact_rollback, conflicts FROM pg_stat_database WHERE datname = current_database();"
psql -d <dbname> -c "SELECT pid, state, query_start, xact_start FROM pg_stat_activity WHERE datname = current_database() AND state IN ('idle in transaction','active');"
psql -d <dbname> -c "SELECT COUNT(*) FROM <outbox_table> WHERE dispatched_at IS NULL;"
psql -d <dbname> -c "SELECT id, business_key, idempotency_key, created_at, dispatched_at, last_error FROM <outbox_table> ORDER BY created_at DESC LIMIT 50;"
grep -nE 'BEGIN|COMMIT|ROLLBACK' <application_log_path> | grep -A1 '<correlation_id>'
grep -nE 'idempotency-key|Idempotency-Key|<idempotency_header>' <application_log_path> | tail -n 200