mysql2/drizzle: a transaction does not pin its pooled connection — statements scatter, breaking atomicity

[proggeramlug]

Summary

A write transaction on a pooled mysql2 connection commits its data but the
transaction is never ended: it stays RUNNING in information_schema.innodb_trx
holding row locks, and the connection returns to the pool still inside it.
Subsequent writes block on those locks and fail with Query timed out; the
service degrades to unusable within a handful of operations.

Perry 0.5.1519 from source, plus #9314 and the fix in #9319. drizzle-orm over
mysql2 pools, MySQL 8.0.46 on the same host. Node 26.8.1 on identical code
does not leak.

What it looks like in a real service

A registration endpoint (db.transaction() → insert org, user, membership,
roles, consent) returns 201 and the rows persist correctly. But afterwards:

mysql> SELECT trx_id, trx_state, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_s, trx_rows_locked
       FROM information_schema.innodb_trx;
+--------+-----------+-------+------------------+
| 182982 | RUNNING   |    11 |                3 |
| 182991 | LOCK WAIT |    11 |                1 |
+--------+-----------+-------+------------------+

— one transaction left open holding three row locks, and the next one already
waiting on it. The owning connection shows as Sleep in the processlist, so
nothing is executing; the transaction is simply never committed or rolled back
from the server's point of view.

After a few requests the pool is full of such connections and every write dies
with:

insert into `orgMemberships` (…) values (default, ?, ?, ?, ?)
params: [ 13, 19, '2026-08-31 22:05:40.269', '2026-08-31 22:05:40.269' ]
cause: Error: Query timed out

It is not request-driven. Ten seconds after a fresh start, with zero
traffic, the process already shows 2 open transactions and its scheduler loop
has never recorded a tick — the scheduler's own write transactions leak the
same way and block it.

Parameterless reads keep working throughout, so a healthcheck stays green:
{"ok":true,"db":true,…} while nothing that writes can proceed.

What I could NOT reduce

Three attempts that do not reproduce it — all behave identically to node:

1. Raw mysql2 on a pool: getConnection → beginTransaction → INSERT →
   commit → release, counting innodb_trx at each step. Zero open
   transactions throughout, on both runtimes.
2. A drizzle read-only db.transaction() — no leak.
3. Drizzle inserts and selects inside a transaction, including
   where inArray(...) and for…of over the result — all correct, and
   bootstrap-admin (no transaction) works perfectly.

So it needs a write transaction with real lock acquisition, and probably the
drizzle wrapper rather than the driver directly. I have a service that
reproduces it 100% of the time and am happy to instrument it, test a patch, or
run any query you want against it.

Why it matters

The data is written correctly, the endpoint returns success, and the
healthcheck stays green — so nothing signals the problem until the next write
times out. On a platform where those writes are auctions and payments, a
transaction that holds locks indefinitely after "succeeding" is worse than one
that fails.

[proggeramlug]

Root cause found — and it is worse than a leak

A transaction does not pin its pooled connection under Perry. Statements inside one db.transaction() are scattered across different sockets.

Three SELECT CONNECTION_ID() reads inside a single transaction:

runtime	connection ids
node 26.8.1	9094 9094 9094	same socket ✓
perry 0.5.1519	9095 9096 9095	different sockets ✗

Same file, same pool, same drizzle version; only the runtime differs. Repro:

const { db } = await connect({ env: { … } });          // drizzle over a mysql2 POOL
const id = async (r) => (await r.execute(sql`SELECT CONNECTION_ID() AS id`)) /* → id */;

console.log("outside:", await id(db));
await db.transaction(async (tx) => {
  console.log("inside:", await id(tx), await id(tx), await id(tx));
});

Why this explains everything reported above

• The leak: BEGIN lands on connection A. Later statements go to B. COMMIT goes to whichever is next. A is never committed, so it stays RUNNING in innodb_trx holding its row locks with the socket idle — exactly what the processlist showed.
• Why writes then time out: subsequent transactions block on A's locks.
• Why it needed a write to show up: a read-only transaction takes no locks, so scattering it is invisible. That is why my raw-mysql2 and read-only reductions came back clean — they were the wrong shape, not the wrong idea.
• Why the scheduler wedges with no traffic: its own write transactions scatter identically.

The part that matters more than the leak

Atomicity is silently broken. A statement that lands on a different connection is not in the transaction at all — it is autocommitted. A rollback will not undo it, and a SELECT … FOR UPDATE taken on one connection does not serialise work done on another.

For this application that is not theoretical: every bid, max-bid, buy-now and auction close is serialised by SELECT … FOR UPDATE on the auction row inside a transaction. Under this defect two concurrent bidders can both pass that lock, because their statements are on different sockets. The data written looks plausible and the endpoint returns success.

So the visible symptom (locks held, queries timing out) is the benign half. The dangerous half is that transactions which appear to succeed were never atomic.

Where to look

connect() returns mysql.createPool(...). Under node, drizzle's mysql2 session calls pool.getConnection() for a transaction and issues every statement on that dedicated connection. Under Perry it evidently keeps using the pool, so each statement is dispatched to an arbitrary free socket — consistent with the "getConnection" in client branch resolving differently, or getConnection() returning something that still round-robins.

Note mysql.createConnection was separately broken until #9319 (Invalid connection handle), so the single-connection path has not had much exercise; this may be adjacent.

I have a service that reproduces this 100% of the time and can test a patch immediately — the CONNECTION_ID() check above is a two-minute verification.

[proggeramlug]

Correction and a precise location

My comment above named the right defect but missed why it only bites some programs. It is not every mysql2 pool — it is the builtin sqlx-backed implementation, and a program opts into it by accident.

How a program gets routed there

detect_bundled_mysql2_create (crates/perry-hir/src/lower/expr_call/native_module.rs:188) inspects the key names of the createPool config literal. A connection key (host / uri / socketPath) plus any mysql2-only option (connectionLimit, waitForConnections, queueLimit, maxIdle, idleTimeout, namedPlaceholders, rowsAsArray, enableKeepAlive, multipleStatements, typeCast) lowers the call to NativeMethodCall { module: "mysql2/promise" } — the native implementation.

Ours matches on host + connectionLimit, so we were on the sqlx path without ever asking for it.

Measured, same binary, same file, only the config literal differing:

createPool config	"getConnection" in pool	query({sql}, values)
{host, port, user, password, database}	false	1064
{host, …, connectionLimit, waitForConnections}	false	1064
same keys behind a spread, real mysql2 in perry.compilePackages	true	ok

The third row is the control: defeat the key heuristic so the npm package is used, and both behaviours match Node.

The defect

The sqlx pool and connection are opaque handles. pool.getConnection() works because the call is lowered to a native dispatch at compile time, but the object carries no reflective surface, so every non-call query disagrees with it:

"getConnection" in pool      false     // drizzle's isPool()
Reflect.has(pool, …)         false
typeof pool.getConnection    "undefined"
Object.keys(pool).length     0

Other native handles are fine — EventEmitter, http.Server, stream.Readable, Map, Headers, AbortController, Date all answer in correctly — so the machinery exists (class_instance_has_member, whose comment already records this exact failure for NestJS's 'listen' in receiver). mysql2's handles are simply not registered with it. URL has the same gap: "toJSON" in new URL(…) is false.

A second, separate one

The sqlx query() only accepts string SQL. Every options-object form fails 1064, because the object pointer is read as the SQL string:

query("INSERT … VALUES (?)", [1])                          ok
query({sql: "INSERT … VALUES (?)"}, [2])                    1064   <- drizzle's hot path
query({sql: "INSERT … VALUES (?)", values: [3]})            1064
query({sql: "…", rowsAsArray: true}, [4])                   1064
execute("INSERT … VALUES (?)", [5])                         ok

values actually stored: 1,5 — the three object forms silently stored nothing. It reproduces only when the first argument is statically an object literal; passed as a dynamic value it takes a path that handles it.

Suggested fixes, in the order I would want them

1. Give the mysql2 handles a reflective surface, as the other native instances have. That alone fixes isPool and therefore the leak and the atomicity break.
2. Accept the options-object form in the sqlx query().
3. Consider whether a config-key heuristic should be able to silently move a program onto an implementation with a different observable surface. An explicit opt-out would have saved a long hunt — the failure is invisible until you look in innodb_trx.

I have a host that reproduces all of it and can verify a patch quickly.

[proggeramlug]

Closing: fixed in #9564, landed via merge train #9569 on 2026-09-02. The bundled fallback now exposes getConnection through reflective and dynamic dispatch so Drizzle pins one checked-out connection per transaction; verified with Drizzle 0.44.7 against live MySQL (one stable CONNECTION_ID across statements). Not in release candidate r27.