Batch $transaction() intermittently never settles on driver adapters — query lost inside client engine before adapter's startTransaction; interactive tx with same payload always succeeds

[telecom47]

Bug description

We use the RLS pattern from the official client-extensions docs: a query extension wraps every operation in a batch transaction [SET app.company_id, query]. Under production load (NestJS app, ~4 concurrent sync jobs + cron jobs), some of these batch transactions never settle — the promise neither resolves nor rejects, forever:

• CPU is 0%, the event loop is alive, other queries (including other batch transactions) keep working.
• pg_stat_activity at the moment of the hang: 0 active queries, 0 transactions (all backend connections idle, none idle in transaction). The BEGIN of the hanging transaction is never sent to Postgres.
• DEBUG=prisma:* trace of the hanging transaction ends at [js::startTransaction] — the adapter's next step [js::begin] never appears.
• Pool telemetry at the same moment (we pass our own pg.Pool into PrismaPg and track events): totalCount=4, idleCount=4, waitingCount=0, acquire count == release count, i.e. the pool is completely healthy and idle — pool.connect() was seemingly never reached / never awaited through.

So the operation is lost somewhere between the client-engine transaction machinery and the driver adapter, with no timeout and no error.

Frequency & shape

• Roughly up to ~50% of affected calls during heavy sync phases; sometimes the same logical call stalls 2–3 retries in a row (looks deterministic for a given call while the process state persists), then a later retry passes.
• Affected calls in our app are update/upsert on models that go through two stacked query extensions where the outer extension awaits another query first and only then calls query(args):

// outer extension (simplified)
async update({ args, query }) {
  const existing = await guarded.model.findUnique({ where: args.where }) // own batch tx
  args.data = guard(existing, args.data)
  return query(args) // continuation invoked AFTER an await -> this one stalls
}
// inner extension (official RLS pattern)
async $allOperations({ args, query }) {
  const [, r] = await client.$transaction([
    client.$executeRawUnsafe(`SELECT set_config('app.company_id', $1, true)`, cid),
    query(args),
  ])
  return r
}

Replacing the stale continuation with a fresh base[model].update(args) call reduced the stall rate ~5x but did not eliminate it — fresh batch transactions still stall occasionally under load.

• Key fact: retrying the stalled operation as an interactive transaction succeeds every time (18/18 in our logs so far):

client.$transaction(async tx => {
  await tx.$executeRawUnsafe(SET_SQL, cid)
  return tx[model][operation](args)
})

Same payload, same load, same pool — the interactive path never stalled once, while the batch path kept stalling around it. This is what points at the batch-transaction machinery specifically.

What we ruled out

• Postgres / locks: no active queries, no open transactions, no lock waits at hang time.
• Pool exhaustion / connection leak: own pg.Pool with max=25, connectionTimeoutMillis=30s; telemetry shows idle pool, waiting=0, acquired==released, no leak. Raising idleTimeoutMillis 60s→10min (to remove pool churn) did not eliminate stalls.
• fix(client-engine-runtime): send ROLLBACK when discarding a timed-out transaction start #29727 (dirty connection after ITX maxWait timeout): upgrading 7.4.0 → 7.9.0 and setting transactionOptions: { maxWait: 15_000, timeout: 120_000 } changed nothing.
• Event-loop starvation: process serves HTTP and other queries normally while the promise hangs.
• Diagnostic reports (process.report) at hang time: all TCP handles to Postgres idle, no pending connects.

Environment

• prisma / @prisma/client / @prisma/adapter-pg: 7.9.0 (also reproduced on 7.4.0)
• Node.js v24.15.0 (official node:24 image), Linux x64, Docker
• PostgreSQL 16 (docker, same compose network), RLS enabled (FORCE ROW LEVEL SECURITY, non-owner role)
• NestJS 10; client built as: PrismaClient subclass → $extends(companyExtension) → $extends(dailyGuard)

Reproduction

We could not reproduce in isolation (400 operations × 8 workers with the same two-extension structure + background interactive transactions pass cleanly). It only fires in the long-running loaded process — we are happy to run any instrumented build / extra DEBUG namespaces on our staging setup and report back. Attaching our isolated repro scripts anyway for the structure.

repro.js (isolated attempt, does not fire)

// base -> companyExtension (batch [SET, query]) -> dailyGuard (findUnique, then query(args))
// 8 workers x 50 update() each + background interactive transactions - passes cleanly.
// Available on request; structure identical to the snippet above.

Workaround we ship now

Wrap every batch transaction in Promise.race with a 30s timer; on stall retry once more via batch, then fall back to an interactive transaction (which, so far, always succeeds). This keeps production alive but adds 30–60s latency per stall, so we would love a real fix or pointers on what extra tracing would help you localize this.

---

Nikolai Shishanov
n.shishanov@ituslugi.pro · Telegram @telecom47