# Lock blocking chain deeper than the lock budget

- **Finding:** `H11`
- **Severity:** P1
- **Owner:** Developer (Application SQL / ORM / Migrations) + DBA / Platform Engineer (Cluster GUCs / Vacuum / Pooling / Storage)
- **Environment:** cloudrun-showcase (synthetic-demo)
- **Generated:** 2026-10-27 17:24:50 UTC

## 1. Incident Overview & Executive Summary

CircuitSQL detected finding H11 (Lock blocking chain deeper than the lock budget) in environment cloudrun-showcase (synthetic-demo) at 2026-10-27 17:24:50 UTC. The queue included checkout updates. Lock depth caused customer-visible latency and 504 responses. The correlated report classifies the incident as P1 with triggers H2, H6, H11, H12, pg_stat_statements. CircuitSQL routes remediation to Developer (Application SQL / ORM / Migrations) + DBA / Platform Engineer (Cluster GUCs / Vacuum / Pooling / Storage). Environment cloudrun-showcase (synthetic-demo) reported 4 breached health check(s) across 2 correlated root-cause incident(s) and 2 query offender(s). CircuitSQL routes remediation to Developer (Application SQL / ORM / Migrations) + DBA / Platform Engineer (Cluster GUCs / Vacuum / Pooling / Storage).

### Document metadata

| Field | Value |
| --- | --- |
| Incident ID | CSQL-INC-H11-202610271724 |
| Date / Time of Incident | 2026-10-27 17:24:50 UTC |
| Duration | 63 min (trigger -25 min to recovery +38 min; see section 4) |
| Severity Level | P1 - Critical (customer-visible outage or data-risk) |
| Affected Systems | circuitsql_monitor@db-primary.internal:5432/circuitsql (ssl_mode=require); orders and customers OLTP paths; environment cloudrun-showcase (synthetic-demo) |
| Database Engine & Version | PostgreSQL 17.2 |
| Lead Investigator(s) | Developer / Marlon Francis Briguera (incident commander); Backend Engineer (query path); DBA / Platform Engineer (cluster controls) |
| Current Status | Mitigated - monitoring for recurrence; permanent remediation tracked in section 7 |

## 2. Technical Environment Context

### System topography

```text
Client / API (FastAPI, app_rw)
        |
        v
[PgBouncer :6432] -- pool_mode=transaction, default_pool_size=4x vCPU
        |
        v
[PostgreSQL primary circuitsql]  << finding H11
        |-- pg_stat_activity / pg_locks / pg_blocking_pids
        |-- pg_stat_statements (offender ranking)
        |-- pg_stat_checkpointer / pg_stat_io (PG 17/18 subsystem counters)
        '-- [Read replica] analytics_ro (reporting, EXPLAIN ANALYZE rehearsals)

[CircuitSQL Monitor] -- H1-H18 probes, CSQL-S01..S07 security scan, RCA export
```

### Privilege and tool prerequisites

- Role membership: pg_monitor and pg_read_all_stats (read-only catalog access).
- pg_stat_statements present in shared_preload_libraries and installed in the database.
- PgBouncer admin console reachable on port 6432 for SHOW POOLS and RELOAD.
- psql 17 client plus a shadow database for EXPLAIN (ANALYZE, BUFFERS) rehearsals; never run write DDL against production to inspect a plan.
- ALTER SYSTEM requires a superuser or a role with the ALTER SYSTEM privilege; credentials come from the secret manager, never URL query strings.
- Incident channel and GitLab issue write access for the fix MR linked in section 8.

## 3. Symptoms & Observed Evidence

### Observed vs. expected behavior

| Aspect | Observed | Expected |
| --- | --- | --- |
| Measured value vs. threshold | H11 measured 4 against 1 (breached). | Lock wait depth stays at 1 or below, and every DDL path fast-fails with lock_timeout instead of queuing writers. |
| Session and lock state | Blocking sessions and long transactions remain visible in pg_stat_activity; lock waits queue behind the head blocker. | Lock depth <= 1, no idle-in-transaction session, and every statement bounded by lock_timeout and statement_timeout. |
| Workload impact | orders and customers traffic shows elevated latency or saturation while the condition holds. | p95 within the 250 ms OLTP SLO and connection use below 85% of the pool budget. |
| Data integrity and compliance | No data loss observed; the control gap maps to SQLSTATE 55P03 handling. | Constraints validated, checksums clean, and CIS PostgreSQL 17 controls satisfied. |

### Application exception log

```text
2026-10-27 17:12:50.812 UTC [api] ERROR orders.views: request failed route=/orders finding=H11
psycopg.errors.LockNotAvailable: canceling statement due to lock timeout
Traceback (most recent call last):
  File "app/routes/orders.py", line 88, in create_order
    session.commit()
psycopg.errors.DatabaseError: ERROR: canceling statement due to lock timeout (SQLSTATE 55P03)
```

### RDBMS error log

```text
2026-10-27 17:12:50.101 UTC [41892] LOG: process 41892 still waiting after 1000.042 ms
ERROR: canceling statement due to lock timeout (SQLSTATE 55P03)
2026-10-27 17:12:51.204 UTC [41892] STATEMENT: /* finding=H11 */ SELECT pid, EXTRACT(EPOCH FROM (now() - query_start)) AS waited_seconds, pg_blocking_pids(pid) AS blockers, wait_even...
2026-10-27 17:12:52.318 UTC [41892] DETAIL: causal_tag=[CSQL-M03 · INDIRECT] sqlstate=55P03
```

## 4. Timeline of Events

| Phase | UTC Timestamp | Event | Evidence |
| --- | --- | --- | --- |
| Trigger | 2026-10-27 16:59:50 UTC | H11 condition established: Lock blocking chain deeper than the lock budget | finding=H11; tag=[CSQL-M03 · INDIRECT] |
| Alerting | 2026-10-27 17:06:50 UTC | CircuitSQL monitor evaluated the probe and raised the finding on the console. | environment=cloudrun-showcase (synthetic-demo); breached=true |
| Degradation | 2026-10-27 17:12:50 UTC | orders and customers traffic degraded: latency rose and lock or buffer pressure became visible. | H11 measured 4 against threshold 1 (cited) and is BREACHED: Lock blocking chain depth 4 queued behind idle-in-transaction PID 41892 on public.orders [CSQL-M03 · INDIRECT] |
| Triage | 2026-10-27 17:24:50 UTC | On-call opened the per-finding RCA page and confirmed the mechanism against pg_stat_activity, pg_locks, and pg_stat_statements. | RCA page /console/rca/H11 |
| Mitigation | 2026-10-27 17:33:50 UTC | On-call terminated blockers and reloaded the connection pool as emergency mitigation (section 6.1). | pg_terminate_backend + PgBouncer RELOAD |
| Recovery | 2026-10-27 18:02:50 UTC | Latency, lock depth, and connection use returned to baseline. On-call monitored the incident for 30 minutes before closing it. | recovery verified at 18:02:50 UTC |

## 5. Technical Root Cause Analysis

### Causal flow diagram

```text
+------------------------------------------------------------------------------------------------+
| Trigger H11: Lock blocking chain deeper than the lock budget                                   |
+------------------------------------------------------------------------------------------------+
                                                |
                                                v
+------------------------------------------------------------------------------------------------+
| Mechanism: A write transaction held RowExclusiveLock while waiting on external I/O, and lat... |
+------------------------------------------------------------------------------------------------+
                                                |
                                                v
+------------------------------------------------------------------------------------------------+
| Symptom: The queue included checkout updates. Lock depth caused customer-visible latency an... |
+------------------------------------------------------------------------------------------------+
                                                |
                                                v
+------------------------------------------------------------------------------------------------+
| Impact: orders/customers OLTP latency and write availability                                   |
+------------------------------------------------------------------------------------------------+
                                                v
+------------------------------------------------------------------------------------------------+
| Blast radius: checkout writes, orders reads, p95 SLO                                           |
+------------------------------------------------------------------------------------------------+
```

### Primary cause

A write transaction held RowExclusiveLock while waiting on external I/O, and later statements queued behind it. Correlated verdict: Root Cause (H2 · High): PID 41892 idle in transaction for 312.0s holding RowExclusiveLock on public.orders [CSQL-M05 · DIRECT] -> Proximate Cascading Symptoms (2 suppressed): H11 (Lock blocking chain depth 4 queued behind idle-in-transaction PID 41892 on public.orders [CSQL-M03 · INDIRECT]), H12 (public.orders dead-tuple ratio 34.0% (340,000 dead tuples) amplified by H6 autovacuum stall [CSQL-M01 · INDIRECT]).

### Secondary cause

The application set no lock_timeout. Blocked sessions waited indefinitely instead of failing fast. Downstream correlation: Root Cause (H6 · Medium): Autovacuum disabled on public.orders while hot updates accumulate dead tuples [CSQL-M01 · DIRECT] (isolated breach, 0 downstream symptoms).

### Tertiary cause

The queue included checkout updates. Lock depth caused customer-visible latency and 504 responses. Correlated evidence: Check H2 (High, owner DEVELOPER): value=312.0 vs. threshold=60.0 (cited) — PID 41892 idle in transaction for 312.0s holding RowExclusiveLock on public.orders [CSQL-M05 · DIRECT]

### Finding SQL

```sql
SELECT pid, EXTRACT(EPOCH FROM (now() - query_start)) AS waited_seconds,
       pg_blocking_pids(pid) AS blockers, wait_event_type, wait_event
  FROM pg_stat_activity
 WHERE cardinality(pg_blocking_pids(pid)) > 0
 ORDER BY waited_seconds DESC;
```

### Metrics

- Check H11: measured value 4 against threshold 1 (cited); state BREACHED.
- Probe detail: Lock blocking chain depth 4 queued behind idle-in-transaction PID 41892 on public.orders [CSQL-M03 · INDIRECT]
- PG 17/18 pg_stat_checkpointer: requested ratio 11.2%, write 1840.5 ms, sync 210.2 ms.
- Waiters: LWLock buffer 2, IO 1.

- **Causal tag:** [CSQL-M03 · INDIRECT]

## 6. Step-by-Step Resolution & Remediation Procedures

### Emergency mitigation (immediate stabilization)

### Step 1 Capture the blocker set before touching anything

```sql
SELECT pid, usename, state,
       now() - xact_start AS xact_age,
       wait_event_type, wait_event,
       pg_blocking_pids(pid) AS blockers,
       left(query, 160) AS query
  FROM pg_stat_activity
 WHERE state <> 'idle'
   AND (now() - xact_start > interval '30 seconds'
        OR wait_event_type = 'Lock')
 ORDER BY xact_age DESC;
```

Record the head blocker for H11 in the incident timeline before any termination. The capture is read-only and pg_monitor compatible.

### Step 2 Terminate only the head blocking backend, with a bounded wait

```sql
-- Replace 41892 with the head blocker captured in Step 1.
SELECT pg_terminate_backend(41892, 5000);

-- Confirm the queue drains instead of re-queuing.
SELECT count(*) AS queue_depth
  FROM pg_stat_activity
 WHERE cardinality(pg_blocking_pids(pid)) > 0;
```

Do not terminate blocked sessions first. They re-queue behind the same head blocker and the incident restarts.

### Step 3 Drain and reload PgBouncer so pooled sessions pick up the new limits

```sql
-- PgBouncer admin console (port 6432), not the PostgreSQL port.
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c 'SHOW POOLS;'
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c 'RELOAD;'
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c 'SHOW STATS;'
```

RELOAD applies pgbouncer.ini pool_mode and default_pool_size without dropping the listener. Transaction pooling discards session-level state by design.

### Permanent remediation

### 6.2.1 CREATE INDEX CONCURRENTLY & EXPLAIN (ANALYZE, BUFFERS)

```sql
-- 6.2.1 Build the replacement index without blocking writes.
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created
    ON public.orders (customer_id, created_at DESC);

-- Prove the planner uses it before dropping or shipping anything else.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, total_cents
  FROM public.orders
 WHERE customer_id = $1
 ORDER BY created_at DESC
 LIMIT 50;

-- Finding-specific DDL for H11.
ALTER ROLE app_rw SET lock_timeout = '2000ms';
```

Resolve the head blocker; never terminate the queue in arbitrary order.

### 6.2.2 ALTER SYSTEM SET statement_timeout = '5000ms' & idle_in_transaction_session_timeout = '15000ms'

```sql
-- 6.2.2 Bound every session at the cluster and role level.
ALTER SYSTEM SET statement_timeout = '5000ms';
ALTER SYSTEM SET idle_in_transaction_session_timeout = '15000ms';
SELECT pg_reload_conf();

-- Verify the effective values for the application role.
SELECT rolname, rolconfig
  FROM pg_roles
 WHERE rolname IN ('app_rw', 'migration_runner', 'analytics_ro');
```

Per-role settings override cluster defaults. For H11, the idle-in-transaction ceiling is the control that prevents recurrence.

### 6.2.3 Before/After @Transactional application refactor

```sql
# Before: network I/O and retries live inside the transaction.
@transactional
def create_order(payload: OrderIn) -> Order:
    order = Order(**payload.model_dump())
    session.add(order)
    session.flush()
    inventory.reserve(order.id)      # remote call holds the write lock
    payments.authorize(order)        # another remote call
    return order

# After: the transaction only covers database work.
@transactional
def create_order(payload: OrderIn) -> Order:
    order = Order(**payload.model_dump())
    session.add(order)
    session.flush()
    outbox.enqueue("order.created", order.id)  # local insert, no I/O
    return order

# Inventory and payment run after COMMIT, with their own short transactions.
```

Keep only database work inside the transaction boundary. Run remote calls and retries after COMMIT.

## 7. Preventative Action Items & Monitoring Controls

| Action Item | Type | Target Date | Owner | Status |
| --- | --- | --- | --- | --- |
| Add a burn-rate alert for H11 with a 5-minute lookback and route it to the Monitor stage dashboard. | Monitoring control | 2026-10-30 | DBA / Platform Engineer | Open |
| Enforce statement_timeout, lock_timeout, and idle_in_transaction_session_timeout for every application role. | Configuration | 2026-10-30 | DBA / Platform Engineer | Open |
| Ship the reviewed remediation for H11 behind Tier 1 static analysis and Tier 2 shadow verification. | Code change | 2026-11-10 | Backend Engineer | In progress |
| Add a regression test that fails when the finding reproduces against the showcase snapshot. | Test coverage | 2026-11-06 | Backend Engineer | Open |
| Re-audit the control against CIS PostgreSQL 17 Benchmark v1.0.0 and record the evidence in the Govern stage. | Compliance | 2027-01-25 | Software Architect / Technical Writer | Scheduled |

## 8. Related Resources & References

- **Internal Monitoring Dashboard:** /console?stage=monitor&view=rca&finding=H11
- **Related Merge Request:** !0 - fix MR to be opened from section 6.2 (H11)
- **Runbook Reference:** src/circuitsql/skills/root-cause-analysis/SKILL.md section 4-6; docs/specs/v3-spec.md (finding H11)
- **Vendor Documentation:** https://www.postgresql.org/docs/17/runtime-config-client.html