Nested withTenantContext Deadlocks the Pool at Max

Conclusion

Holding a transaction open while calling into code that opens a second, independent transaction on the same pool deadlocks every request the moment concurrency reaches the pool’s max. withTenantContext is a transaction that holds one PG connection for as long as the callback (fn) you pass it is running. One SSE handler held this transaction open until the entire response finished streaming. Inside that callback it called a rate-limit check, and the rate limiter’s own store implementation (CloudSqlRateLimiter) opened a second withTenantContext on the same pool to do its own write. The pool’s max was 10, with no acquire timeout configured. At exactly 10 concurrent requests, all ten hold one connection each in their outer transaction while each one’s inner transaction waits for an eleventh connection that will never come free — nobody can proceed. This code path never ran in production: a pre-merge review on the same PR (#1401) that introduced the daily quota feature caught the deadlock and it was fixed before merge.

Symptom (what would have happened if this had shipped)

A per-school daily quota on student chat questions was added to stop unbounded Gemini spend through a leaked anonymous magic link (2026-10-01, following a security audit). The implementation looked like this:

// sse-handler.ts (at 18a5f7b7, before the review caught it)
const stream = new ReadableStream<Uint8Array>({
  async start(controller) {
    try {
      await withTenantContext(getDb(), args.tenantContext, async (tx) => {
        const result = await executeChat({
          tx,
          schoolId: args.schoolId,
          identity: args.identity,
          rawQuestion: question,
          piiEntries: [],
          contextProvider,
          modelClient,
          locale,
          // Per-school daily quota, shared across all instances.
          dailyQuota: getChatDailyQuota(),
        });
        // ... holds this tx through chunk emission + done() (persists the assistant reply)
      });
    } catch { /* ... */ }
  },
});

withTenantContext opens inside the ReadableStream’s start() and stays open through chunk emission and done(). One PG connection is held for the entire lifetime of one SSE response.

executeChat, called from inside that transaction, calls dailyQuota.tryAcquire right before the Gemini call:

// chat-service.ts (at 18a5f7b7)
// 4.5) Per-school daily quota (shared across instances, daily-quota.ts). Checked right
//      before Gemini/embedding usage starts (out-of-scope rejections aren't counted).
//      Fail-closed: if the check can't decide, don't call Vertex.
if (dailyQuota) {
  let allowed: boolean;
  try {
    allowed = await dailyQuota.tryAcquire(schoolId, nowMs);
  } catch {
    return { kind: "rejected", status: 503, reason: "quota_unavailable", message: "..." };
  }
  if (!allowed) {
    return { kind: "rejected", status: 429, reason: "rate_limited_school_daily", message: "..." };
  }
}

dailyQuota.tryAcquire runs inside the outer withTenantContext. Had this shipped as-is, the moment exactly as many concurrent questions as the pool’s max (10) were in flight — from one school, or summed across several — every one of them would hold its outer transaction’s connection while waiting on the inner transaction tryAcquire needs, and none of them would ever get a response back.

Cause

Following tryAcquire one call at a time shows it opening a second, independent transaction on the same pool:

// rate-limit-distributed.ts (at 18a5f7b7)
export class DistributedRateLimiter implements RateLimiter {
  async tryAcquire(key: string, nowMs: number): Promise<boolean> {
    const windowStartMs = Math.floor(nowMs / this.windowMs) * this.windowMs;
    return await this.store.tryAcquireSlot(key, windowStartMs, this.limit);
  }
}

DistributedRateLimiter.tryAcquire is a pure pass-through to its store — it holds no connection of its own. The connection is opened by the store implementation, CloudSqlRateLimiter:

// ai-rate-limit.ts (at 18a5f7b7)
export class CloudSqlRateLimiter implements RateLimitStore {
  async tryAcquireSlot(key: string, windowStartMs: number, limit: number): Promise<boolean> {
    return await withTenantContext(
      this.db,
      { schoolId: key },
      async (tx) => {
        const rows = await tx.execute<{ count: number }>(sql`
          INSERT INTO ai_rate_limit_windows (school_id, window_start_ms, count)
          VALUES (${key}::uuid, ${windowStartMs}, 1)
          ON CONFLICT (school_id, window_start_ms) DO UPDATE
            SET count = ai_rate_limit_windows.count + 1, updated_at = now()
            WHERE ai_rate_limit_windows.count < ${limit}
          RETURNING count
        `);
        return rows.length > 0;
      },
      this.options,
    );
  }
}

CloudSqlRateLimiter.tryAcquireSlot calls withTenantContext on its own account, against the same db client instance the SSE handler is already using, to UPSERT a counter row under RLS. That requests a brand-new connection from the same pool.

The pool itself is configured in one line:

// client.ts (at 18a5f7b7, line 72)
const sqlClient = postgres(url, { max: 10, onnotice: () => {} });

No option besides max: 10 is set. There is no connect/acquire timeout, so a request for a connection waits indefinitely until one frees up.

The call depth is three levels:

sse-handler.ts            withTenantContext (outer tx, held through the whole response)
└ chat-service.ts         dailyQuota.tryAcquire (called from inside the outer tx)
  └ rate-limit-distributed.ts  DistributedRateLimiter.tryAcquire (pure pass-through)
    └ ai-rate-limit.ts    CloudSqlRateLimiter.tryAcquireSlot
      └ withTenantContext (inner tx, a fresh connection from the same pool)

With N concurrent requests, N outer transactions each hold one connection. The moment N reaches the pool’s max (10), all ten connections are already held by outer transactions, so the “eleventh” connection each request’s inner transaction needs never materializes. With no acquire timeout, this isn’t a brief delay — it’s an unbounded wait.

Fix (landed before merge)

The fix (PR #1401, from the review comment) didn’t touch the rate limiter’s own inner withTenantContext call at all. It moved the quota check in the SSE handler to run before the outer transaction opens:

// sse-handler.ts (at 4f34f573)
// 2.5) Per-school daily quota (daily-quota.ts, shared across instances). Checked **before**
//      opening the RLS tx (the tx holds a connection through the whole response, so opening
//      a second connection from inside it deadlocks the pool — Reviewer on #1401).
//      Rejections are returned the same way as other rejections: a single SSE `error` frame.
const quotaRejection = await checkChatDailyQuota(args.schoolId, question);
if (quotaRejection) {
  return errorOnlySse(quotaRejection, args.setCookieHeader ?? null);
}

// 3) SSE stream. The RLS tx opens inside start() and is held through chunk emission + done().
const stream = new ReadableStream<Uint8Array>({
  async start(controller) {
    try {
      await withTenantContext(getDb(), args.tenantContext, async (tx) => {
        const result = await executeChat({ /* ... */ });

checkChatDailyQuota is a new function that isolates just the quota check, outside the outer transaction:

// daily-quota.ts (at 4f34f573)
export async function checkChatDailyQuota(
  schoolId: string,
  rawQuestion: string,
  opts: { quota?: RateLimiter; nowMs?: number } = {},
): Promise<ChatQuotaRejection | null> {
  const v = validateQuestion(rawQuestion);
  if (!v.ok) return null;
  if (classifyScope(v.question).verdict === "out_of_scope") return null;
  const quota = opts.quota ?? getChatDailyQuota();
  let allowed: boolean;
  try {
    allowed = await quota.tryAcquire(schoolId, (opts.nowMs ?? Date.now()) + JST_OFFSET_MS);
  } catch {
    return { status: 503, reason: "quota_unavailable", message: "..." };
  }
  if (allowed) return null;
  return { status: 429, reason: "rate_limited_school_daily", message: "..." };
}

At the point quota.tryAcquire runs — and therefore at the point the inner withTenantContext opens — the outer withTenantContext has not been opened yet. By the time the quota check is done and the outer transaction opens, the inner transaction’s connection has already been released. The path where an outer transaction holds a connection while waiting on an inner transaction’s connection from the same pool no longer exists. Rejections return as the same single SSE error frame used for every other rejection reason (length, out-of-scope, etc.), so the client’s handling doesn’t branch on which check failed.

Why it won’t come back the same way

The condition for this bug was: a caller holds withTenantContext open while a function it calls from inside that callback opens its own, independent withTenantContext. The fix removed the one occurrence that existed (the quota check), but the same hazard class already had partial documentation elsewhere in the same file, for a different helper:

// client.ts (at 18a5f7b7, lines 127-133 — existing comment for tenantReadFork)
// ## Usage discipline (pool exhaustion / consistency)
//
// - **fork only at the leaf query level. Never call fork from inside fork**: if an outer
//   lane holds a connection while waiting on an inner lane's connection, the pool
//   (`createDbClient` max:10) can deadlock everyone once it's full. Pass fork into helpers
//   as an argument; helpers themselves should only call it at the leaf.

This discipline is written for tenantReadFork, a parallel-read helper, and names the identical hazard: not opening a new connection from the same pool while holding one. But its scope was the fork-style helpers specifically — it didn’t cover a rate-limiter store implementation calling withTenantContext from inside an already-open transaction. The hazard class itself was already recognized and written down in this codebase; what was missing was applying that same discipline to every caller that holds withTenantContext open and calls into code that might open another one, not just the fork helpers.