advanced~2h

Production Engineering

Every pattern covered so far, viewed from 3 AM on-call: how transaction problems actually announce themselves in production, and how to find and fix them fast.

SELECT blocked_locks.pid AS blocked_pid,
       blocking_locks.pid AS blocking_pid,
       blocked_activity.query AS blocked_query,
       blocking_activity.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
   AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
   AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
   AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
   AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
   AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
   AND blocking_locks.pid != blocked_locks.pid
   AND blocking_locks.granted
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

This exact query is the difference between guessing and knowing during an incident — it directly identifies which query is blocking which, which is precisely the "wait-for graph" concept from Chapter 07 §5, made visible and queryable.

▲ Common mistake

A version of this query that only joins on locktype and pid (skipping database/relation/page/tuple/transactionid) looks right but isn't — it pairs a waiting lock with any other session holding or waiting on a lock of the same type anywhere in the database, including two completely unrelated tables. Match on the actual lock target, and require blocking_locks.granted (an ungranted "blocker" is itself just waiting, not actually blocking anyone) — otherwise you'll get false-positive blocking pairs during a real incident.

💻 Code example

SELECT blocked_locks.pid AS blocked_pid,
       blocking_locks.pid AS blocking_pid,
       blocked_activity.query AS blocked_query,
       blocking_activity.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
   AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
   AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
   AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
   AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
   AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
   AND blocking_locks.pid != blocked_locks.pid
   AND blocking_locks.granted
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

◆ The problem

A single forgotten, still-open transaction (a debugging session left open, a batch job stuck retrying) holding a lock, or an old MVCC snapshot (Chapter 07 §4), can silently degrade an entire table's performance for every other transaction — and it's invisible unless you're actually looking for it.

SELECT pid, now() - xact_start AS duration, query, state
FROM pg_stat_activity
WHERE (now() - xact_start) > interval '5 minutes'
AND state != 'idle'
ORDER BY duration DESC;

▲ Common mistake — "idle in transaction"

An application that opened a transaction, made one query, and then went off to do slow, unrelated work (an HTTP call, waiting on user input) before committing leaves the transaction "idle in transaction" — holding locks and an old snapshot the entire time, for no active database work at all. This is one of the most common real production causes of mysterious lock contention, and directly connects to Chapter 08 §4's timeout setting as a preventive guardrail.

💻 Code example

SELECT pid, now() - xact_start AS duration, query, state
FROM pg_stat_activity
WHERE (now() - xact_start) > interval '5 minutes'
AND state != 'idle'
ORDER BY duration DESC;

Metric	What it tells you
Transaction commit rate vs. rollback rate	A rising rollback rate often signals a growing rate of business-rule violations or race conditions (Chapter 07's optimistic lock failures, for instance).
Deadlocks per minute	Should be near zero in a healthy system — a sustained rate signals a real lock-ordering bug (Chapter 07 §5) worth fixing at the code level, not just retrying around.
Longest-running open transaction	Directly surfaces the "idle in transaction" problem above before it becomes a full incident.
Outbox/inbox table backlog size (Chapter 18)	A growing backlog means the relay process is falling behind or stuck — a leading indicator of downstream event-driven failures before consumers even notice missing events.
Saga compensation rate (Chapter 17)	How often sagas actually fail and need to compensate — a sudden spike usually points to a specific failing downstream service.

◆ Under the hood — correlation IDs

Debugging "why did this order get stuck half-completed" across four independent services (Chapter 15's scenario) is nearly impossible without a shared correlation ID — a single identifier generated at the start of the saga and propagated through every event, log line, and service call for that specific saga instance. Distributed tracing tools (Jaeger, Zipkin, or a cloud provider's equivalent) use exactly this mechanism to reconstruct the full timeline of a single saga's execution across every participating service, into one queryable trace.

ProducerRecord<String,String> record = new ProducerRecord<>("order-placed", orderId, payload);
record.headers().add("sagaId", sagaId.getBytes());
kafkaTemplate.send(record);
// every downstream service reads and re-propagates this same header on every event it emits,
// making the entire saga instance greppable/traceable across every service's logs by one ID

💻 Code example

ProducerRecord<String,String> record = new ProducerRecord<>("order-placed", orderId, payload);
record.headers().add("sagaId", sagaId.getBytes());
kafkaTemplate.send(record);
// every downstream service reads and re-propagates this same header on every event it emits,
// making the entire saga instance greppable/traceable across every service's logs by one ID

Concern	Guidance
Compensating transactions as an attack surface	A compensation endpoint (e.g. "refund this payment") needs the same authorization rigor as the original action — an attacker triggering unauthorized compensations is a real risk in a poorly-secured saga.
Idempotency keys as sensitive data	An idempotency/message key that's guessable or sequential can let an attacker probe or replay another user's saga step — generate them as genuinely unguessable (UUID, not incrementing integers).
Outbox table access	The outbox table (Chapter 18) often contains full event payloads, sometimes including sensitive data — apply the same access controls to it as to the source tables, not looser ones just because it's "just a queue."

Want a visual for this concept?

Generate a diagram tailored to “Production Engineering” — the AI picks whichever visual (flowchart, comparison, sequence, etc.) best fits.

Practice quiz

Next Step

Continue to Capstone — A Fault-Tolerant Order-Payment System →← Back to all Transaction Mastery chapters