[BUG]: QueryQueueManager deadlocks when handler disconnects mid-transaction

[vvolfster]

Description

QueryQueueManager.processQueue() in pglite-socket permanently deadlocks when a handler that started a transaction disconnects before another handler's query can be processed.

When isInTransaction() returns true and lastHandlerId points to a handler that no longer has queries in the queue (because it disconnected), processQueue() sets query = null, breaks out of the while loop, sets processing = false, and never processes queued queries from other handlers again.

Impact

This makes pglite-socket unusable with any client that opens multiple connections where one connection may issue a SET or BEGIN before the other connection's queries are processed. Hasura (GraphQL engine) reliably triggers this on every startup because it opens 2+ connections and sets transaction isolation level on its first connection.

Steps to Reproduce

import { PGlite } from "@electric-sql/pglite";
import { PGLiteSocketServer } from "@electric-sql/pglite-socket";

const db = new PGlite();
await db.waitReady;

const server = new PGLiteSocketServer({
  db,
  host: "0.0.0.0",
  port: 5432,
  maxConnections: 10,
});
await server.start();

// Now connect Hasura (or any client that opens 2+ connections):
// docker run --rm hasura/graphql-engine:v2.47.0 \
//   -e HASURA_GRAPHQL_DATABASE_URL=postgres://postgres@host.docker.internal:5432/postgres?sslmode=disable
//
// Hasura opens connection A, sends startup + SET commands (enters transaction state).
// Hasura opens connection B, sends a query.
// processQueue sees isInTransaction()=true, lastHandlerId=A.
// Looks for A's queries in the queue — finds none (A's startup is done).
// Sets query=null, breaks. Queue permanently stuck.

With debug logging enabled, the last log line before the deadlock is:

[QueryQueueManager] transaction started, but no query from the same handler id found in queue <handlerId>

Root Cause

In processQueue() (source):

if (this.db.isInTransaction() && this.lastHandlerId) {
  const idx = this.queue.findIndex(q => q.handlerId === this.lastHandlerId);
  if (idx === -1) {
    // BUG: sets query=null, which breaks out of the while loop
    // and permanently stops queue processing
    query = null;
  } else {
    query = this.queue.splice(idx, 1)[0];
  }
}

The clearTransactionIfNeeded() method exists and handles cleanup on disconnect, but there's a race: if processQueue() is already executing when the disconnect fires, processQueue() exits before clearTransactionIfNeeded() can fix the state.

Proposed Fix

When no matching handler query is found, rollback the stale transaction and process the next available query instead of deadlocking:

if (idx === -1) {
  this.log("rolling back stale transaction from disconnected handler", this.lastHandlerId);
  await this.db.exec("ROLLBACK");
  this.lastHandlerId = null;
  query = this.queue.shift(); // process any waiting query
} 

This is safe because:

• The handler that started the transaction is gone — its queries were already cleared by clearQueueForHandler()
• The transaction has no useful work to preserve
• Rolling back returns PGlite to a clean state so other handlers can proceed

Environment

• @electric-sql/pglite: 0.4.5
• @electric-sql/pglite-socket: 0.1.5
• Node.js: 22.14.0
• Triggering client: Hasura GraphQL Engine v2.47.0 (uses libpq, opens multiple pooled connections)

[heatherhelen]

Wanted to flag a couple of findings from spiking pglite-socket against SQLAlchemy + asyncpg this week — the analysis below may inform what shape the fix takes.

The repro shows an alive handler, not a disconnect

The issue title says "deadlocks when handler disconnects mid-transaction," but the reproduction steps describe a different scenario:

Hasura opens connection A, sends startup + SET commands (enters transaction state).
Hasura opens connection B, sends a query.
processQueue sees isInTransaction()=true, lastHandlerId=A.
Looks for A's queries in the queue — finds none (A's startup is done).

At step 4, connection A is still attached — its prior messages (startup + SET) just completed and it's now waiting for the app to send the next query. Hasura's app code is sync-blocked waiting for B's response, so A never sends another message. That's the same wedge pattern we reproduced from Python (sync SQLAlchemy):

s1.execute("SELECT 1")   # SQLA auto-begin tx on s1, returns control to Python
s2.execute("SELECT 2")   # blocks: s1 holds the single PGlite tx, never commits

If A actually disconnected, PGLiteSocketHandler.detach() already calls queryQueue.clearTransactionIfNeeded(handlerId), which rolls back and resumes processing. We verified that path works on 0.1.5.

The deadlock fires when A stays alive but idle in transaction. It's not a queue manager state-tracking bug — it's a fundamental consequence of PGlite being single-transaction with multiple cross-handler clients.

The proposed fix would corrupt alive sessions

if (idx === -1) {
  await this.db.exec("ROLLBACK");      // ← unconditional
  this.lastHandlerId = null;
  query = this.queue.shift();
}

In the alive-handler-idle-in-tx case, this silently rolls back A's in-flight transaction the moment B's query arrives — A's app code has no idea its work just vanished. Postgres-the-spec would say this is undefined behavior for the holding session and probably a CWE-class data integrity bug.

Postgres itself handles the same situation via idle_in_transaction_session_timeout — defaults to 0 (disabled), ops opt-in. The semantic is: "if a session sits idle in tx for N ms, terminate it with a clear error." Adopting that pattern would give pglite-socket a Postgres-aligned story.

Design options that come to mind

	Behavior	Tradeoff
(a)	Configurable idleInTransactionTimeout option; rollback + send ErrorResponse to holding handler after timeout	Postgres-aligned, opt-in, alive handler gets a clear signal
(b)	Unconditional rollback (current Hasura proposal)	Simple, fixes Hasura case, silently corrupts alive sessions in other cases
(c)	Return "another transaction in progress, retry" error to the waiting handler instead of queueing	Fail-fast for callers, no rollback risk, but breaks the "queue and serialize" abstraction
(d)	Leave the wedge in place, document the single-tx constraint	No code change, requires clients to use NullPool / one-conn-at-a-time patterns

Any of (a)/(c)/(d) seem more defensible than (b) on correctness grounds.

Adjacent in-flight work

#977 is a substantial QueryQueueManager refactor that adds activeHandlerId ownership tracking through ReadyForQuery boundaries — different bug (extended-query protocol state isolation), but the fix touches the same hot path. Whatever lands here should probably coordinate with that.

Happy to take a swing at any of (a)/(c) if there's a maintainer signal on which direction makes sense. We'd benefit downstream — we're spiking pglite-socket as a local-Postgres bridge for a desktop app and any of these would unblock us, whereas (b) would not.

---

Posted by the Builder AI agent (Claude Opus) on behalf of @heatherhelen.