withTenantContext Self-Deadlocks the Connection Pool

When a transaction holds one connection until the response finishes, and something inside it asks the same pool for a second, hitting the limit makes every request deadlock waiting for itself.

The short version

When a transaction that holds one DB connection until the response completes has, from inside it, another operation ask the same connection pool for a second connection, the moment concurrent requests hit the pool’s maximum connection count, every request ends up deadlocked waiting for each other to release. A chat handler that streams answers over SSE opened an RLS-scoped transaction inside ReadableStream’s start() and held the connection until the stream finished. Inside that same transaction, it called out to check a school’s daily generation-count limit. That check also tried to grab a connection from the identical pool (max 10) via getDb(), so the instant exactly 10 concurrent requests arrived, every one of them was left holding its outer connection while waiting for the inner one — and none of them could ever be released. The fix was to reorder the check to run before the transaction opens.

Symptom

The student/teacher chat Q&A answers one question at a time over SSE (Server-Sent Events). Because generating the answer and persisting it to the DB are scoped inside a single RLS transaction, the transaction stays open until the stream ends.

// sse-handler.ts (gist of the pre-fix shape, at 18a5f7b7)
async start(controller) {
  const send = (event: string, data: unknown) =>
    controller.enqueue(encoder.encode(sseFrame(event, data)));
  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 cap shared across all instances; fixes the quota ceiling structurally.
        dailyQuota: getChatDailyQuota(),
      });
      // ...

The moment withTenantContext is called, this request grabs one connection from the pool (the postgres driver, max: 10) and holds it as tx. executeChat receives that tx and proceeds, but the dailyQuota check it was handed (the per-school daily generation cap) didn’t use tx at all — internally it called its own getDb() and grabbed a separate connection.

// chat-service.ts (before the fix, at 18a5f7b7, inside executeChat)
// 4.5) Per-school daily cap (shared across all instances, daily-quota.ts). From here on,
//      embedding/Gemini usage-based cost is incurred, so count right before that point
//      (deterministic out-of-scope rejections aren't counted). If the check can't be made,
//      stop without calling Vertex (fail-closed).
if (dailyQuota) {
  let allowed: boolean;
  try {
    allowed = await dailyQuota.tryAcquire(schoolId, nowMs);
  } catch {
    return {
      kind: "rejected",
      status: 503,
      reason: "quota_unavailable",
      message: "Unable to respond right now. Please try again shortly.",
    };
  }
  if (!allowed) {
    return {
      kind: "rejected",
      status: 429,
      reason: "rate_limited_school_daily",
      message: "Today's question limit has been reached. Please try again tomorrow.",
    };
  }
}

dailyQuota.tryAcquire goes through CloudSqlRateLimiter, using the connection that getChatDailyQuota() holds internally via getDb() (daily-quota.ts). That’s a different connection from the tx that sse-handler.ts is holding, newly acquired from the same pool.

// daily-quota.ts (at 18a5f7b7)
let memo: RateLimiter | null = null;
export function getChatDailyQuota(): RateLimiter {
  if (memo) return memo;
  memo = createChatDailyQuota(new CloudSqlRateLimiter(getDb()));
  return memo;
}

The pool’s max connection count is 10 (packages/db/src/client.ts), with no wait-timeout configured.

// packages/db/src/client.ts
const sqlClient = postgres(url, { max: 10, onnotice: () => {} });

With 9 or fewer concurrent requests, each request using 2 connections total — one outer (tx) and one inner for the check — still fits within the 10-connection ceiling, so nothing happens. But the instant 10 concurrent requests arrive, every one of them grabs its outer tx first, exhausting the pool’s capacity. When each request then asks for a second connection for its inner check, none are free. No one can release their outer tx either (release is waiting on the inner check and everything after it to finish), so every request ends up waiting on everyone else’s connection to be released. With no wait-timeout, this state never resolves on its own.

Why

What makes this bug’s structure distinctive is that it isn’t “happens under high load” — it’s “happens at exactly the concurrency that equals the pool’s ceiling,” which is easy to miss in load testing. Everything works up through 9 concurrent requests, and only at 10 does everything jam at once, so a gradually-ramping load test only ever reports “still plenty of headroom” unless it happens to test the exact number that matches the ceiling.

The root cause was a structure where a single request’s processing asked the same connection pool for a connection twice, for two different purposes. The first (the RLS transaction) is a long-lived connection held until the response finishes; the second (the rate-limit check) is a short-lived connection requested from inside the first. The pool doesn’t distinguish between the two. From the pool’s perspective, this is simply “N requests lined up, each holding connection #1 while requesting connection #2” — and once N reaches the pool’s ceiling, the condition for releasing connection #1 (acquiring #2 and finishing everything after it) can never be satisfied.

The fix

The fix reorders the per-school daily cap check to run before the transaction opens (4f34f573).

// sse-handler.ts (after the fix, at 4f34f573)
// 2.5) Per-school daily cap (daily-quota.ts, shared across all instances). Count this
//      **before opening the RLS tx** (the tx holds a connection until the very end, so
//      grabbing a second connection from inside it jams the pool — reviewer comment on #1401).
//      A rejection is returned the same way as other rejections: a single 200 SSE `error`
//      frame, so the client handles all rejection kinds uniformly.
const quotaRejection = await checkChatDailyQuota(args.schoolId, question);
if (quotaRejection) {
  return errorOnlySse(quotaRejection, args.setCookieHeader ?? null);
}

// 3) The SSE stream. The RLS tx opens inside start() and is held through chunk emission
//    and done() (persisting the assistant's message).

Calling checkChatDailyQuota before withTenantContext separates the connection request for the check from the connection request for tx in time. A rejected request never opens a tx at all, so the pool only ever sees “at most one connection requested per request at any given moment” — the state of waiting for a second connection simply can’t arise anymore. The dailyQuota argument and its check logic were removed entirely from executeChat’s side (32 lines deleted from chat-service.ts), consolidating the check into sse-handler.ts.

The rejection response shape was also unified. Rather than returning a rejection made before opening tx at the HTTP level, a new errorOnlySse helper returns it the same way as the other existing rejections (invalid scope, etc.) — a single 200 SSE error frame — so the client doesn’t need separate handling branches.

Preventing a repeat

When a single request’s handling touches a connection pool more than once, each access can look correct in isolation, but you still need to check whether the structure has an outer access holding a connection while calling an inner access from inside it. As long as the held connection and the newly requested connection come from the same pool, a self-deadlock is guaranteed the instant concurrency hits the pool’s ceiling. The fix isn’t to raise the connection count — it’s to reorder the processing so that a single request never asks for more than one connection at a time.