mysql2 + drizzle: transactions deterministically hang after ~195, independent of rows, time, pool size and locking

[proggeramlug]

Summary

A transaction containing a locking read (SELECT … FOR UPDATE, with or without SKIP LOCKED) intermittently never completes. The promise never settles, no error is raised, and the connection is left idle. Roughly one occurrence per 200 transactions, on an otherwise idle database with no contention — a single process, one connection in use, nothing else touching the table.

Non-locking transactions and non-transactional queries are unaffected, which is what makes this specific rather than "transactions are flaky".

Perry 0.5.1519, MySQL 8, mysql2@3.24.2, drizzle-orm@0.44.7, Linux x86_64.

Reproduction

Four loops, 400 iterations each, each iteration wrapped in an 8s timeout:

const { db } = await connect({ env: { … } });   // drizzle over a mysql2 POOL

await loop("tx + FOR UPDATE SKIP LOCKED", () => db.transaction(async (tx) =>
  { await tx.execute(sql`SELECT id FROM notifications ORDER BY id LIMIT 5 FOR UPDATE SKIP LOCKED`); }));
await loop("tx + FOR UPDATE",             () => db.transaction(async (tx) =>
  { await tx.execute(sql`SELECT id FROM notifications ORDER BY id LIMIT 5 FOR UPDATE`); }));
await loop("tx + plain SELECT",           () => db.transaction(async (tx) =>
  { await tx.execute(sql`SELECT id FROM notifications ORDER BY id LIMIT 5`); }));
await loop("no tx, plain SELECT",         async () =>
  { await db.execute(sql`SELECT id FROM notifications LIMIT 5`); });

pattern	node 26.8.1	perry 0.5.1519
tx + FOR UPDATE SKIP LOCKED	400/400	HUNG at iteration 193
tx + FOR UPDATE	400/400	HUNG at iteration 239
tx + plain SELECT	400/400	400/400
no tx, plain SELECT	400/400	400/400

A second, independent run hung the first pattern at iteration 194 — so the rate is consistent rather than a one-off.

The locking read is the only variable. Same pool, same table, same transaction wrapper, same process.

What it looks like when it happens

• The awaited promise never settles. No rejection, no timeout from the driver.
• information_schema.innodb_trx is empty — the transaction is not sitting open holding locks, so this is not the lock-wait case and not the same defect as mysql2/drizzle: a transaction does not pin its pooled connection — statements scatter, breaking atomicity #9330.
• SHOW PROCESSLIST shows no running query.
• The process stays alive and its event loop keeps running: epoll_wait returns normally, timers still fire, HTTP requests are still served.

So the connection has been handed a statement that never comes back, while everything around it stays healthy. That last part is what makes it expensive to find.

Why this is the one that matters for us

Our platform serialises every bid, max-bid, buy-now and auction close with SELECT … FOR UPDATE on the auction row — it is the documented rule the whole auction engine rests on. At roughly 1 in 200 that means one bid in every two hundred hangs forever, and because the surrounding request never completes, the connection is never returned to the pool.

We found it through a scheduler loop that guards against overlapping ticks with an in-flight flag. One wedged tick left that flag set, so every subsequent tick was skipped — the service stayed active, the health endpoint kept answering ok (it runs SELECT 1, which has no transaction and no lock), and auctions silently stopped closing. Fifteen clean cycles, then nothing, with no error anywhere.

Related

Same investigation, same driver: #9330 (a transaction does not pin its pooled connection; the options-object query() form is unsupported) and #9349 (JSON columns read back as null, fixed in #9350). This one is independent of both — it reproduces with the pinning workaround in place and with #9350 applied.

I have a host that reproduces it in about two minutes and can test a patch quickly.

[proggeramlug]

More data, and a lead I think is the right one

It also segfaults, not only hangs

The same loop shape with a non-locking but slower statement crashes rather than hanging:

for (400 iterations) await db.transaction(async (tx) => { await tx.execute(sql`SELECT SLEEP(0.01)`); });

→ Segmentation fault, exit 139, before any buffered output reached the file.

So "hangs on FOR UPDATE" was too narrow a description of what I filed. The two failure modes look like one underlying defect showing different faces.

Every pattern is fine in isolation

Run once each, in one program, under Perry — all pass:

A: connected
B: SLEEP outside a transaction ok
C: trivial transaction ok
D: SLEEP inside a transaction ok
E: two statements in a transaction ok
F: done                                  exit=0

Identical output to Node. So no individual statement or transaction shape is broken. It takes repetition — a few hundred transactions — and then it either hangs or dies.

Non-transactional queries at the same volume (400) are clean, and so is a transaction wrapping a single trivial SELECT 1. What correlates is the number of round-trips inside a transaction: BEGIN, statement, COMMIT are three separate run_pool_conn_query calls on the same pooled connection, so a transaction churns the blocking pool three times where a plain query churns it once.

The lead

run_pool_conn_query already carries this comment at the resolve site:

// #1824: build the JS result on the MAIN thread. outcome_to_jsvalue
// allocates arrays/objects/strings, which is UB on this blocking-pool
// thread (worker thread-local arena → dangling on the main thread once
// the pooled thread idles out). `out` is plain Send Rust data.
Ok(out) => promise.resolve_with(move || outcome_to_jsvalue(&out, rows_as_array)),

That is exactly the failure signature I am seeing — intermittent, volume-dependent, and expressed as either a lost wakeup or a segfault depending on what the dangling memory happened to be. #1824 fixed one instance of it. My reading is that another allocation on the blocking-pool thread survives somewhere on this path, and it only bites once a pooled thread idles out, which is why it needs hundreds of iterations and no contention to appear.

I have not found the second allocation site — I ran out of the time I could justify spending inside Perry rather than on the application. But given the comment, whoever wrote #1824 will likely recognise it faster than I would.

Impact restated

Unchanged and still the blocker: our auction engine serialises every bid with SELECT … FOR UPDATE inside a transaction, so this is on the hot path of the one operation the platform exists to perform. We are moving that service to Node until this is fixed, and will move it back — it is a one-line unit-file change.

[proggeramlug]

Correction: the locking read is not the trigger. Row volume is.

I filed this as a FOR UPDATE problem. That was wrong, and the title is now wrong with it. Each loop below was run as the first loop in its own process, because I found that running loops sequentially pollutes the later ones — an abandoned transaction leaves a connection stuck, so the second and third loops in one program are measuring a different pool than the first. Several numbers in my original report suffer from that; these do not.

tx + SELECT … LIMIT 5 FOR UPDATE SKIP LOCKED   HUNG at 195
tx + SELECT … LIMIT 5   (no lock at all)       HUNG at 195

Identical. Remove the lock entirely and it still hangs, at the same iteration. What moves the threshold is how many rows come back, not whether the read is locking.

What it is not

Each of these was varied independently, single loop per process:

varied	values	threshold
connectionLimit	5, 10, 20	193, 193, 193
delay between iterations	0 ms, 50 ms	193 @ 8.1 s, 192 @ 17.9 s
SQL length (padding comment)	0, 200, 1000 bytes	195, 198, 197

So: not a race (it is deterministic to within a couple of iterations), not pool exhaustion, not elapsed time or a thread idling out, not allocation volume in the statement text. OS thread count stays at 2 for the entire run, so nothing is exhausting a thread pool either.

It needs drizzle — raw mysql2 is clean

raw: getConnection + beginTransaction + query + commit + release   500/500 ok
raw: getConnection + query("begin") + query + query("commit") + release   500/500 ok
raw: same, but release() omitted   HUNG at 10   ← control: equals connectionLimit, as expected

Both raw shapes survive 500 transactions, including the one that issues begin/commit as ordinary queries exactly as drizzle does. The control confirms the harness detects a genuine stall.

So perry-ext-mysql2's transaction and query paths are not obviously at fault on their own. It takes drizzle's per-transaction machinery — its session/transaction objects, sql template objects and result mapping — on top of a multi-row result.

A wrong turn, recorded so nobody repeats it

I suspected the asymmetry at the resolution sites: the query path uses resolve_with (deferred to the main thread, the #1824 fix) while run_simple_command, pool_get_connection, create_connection, connection_end and pool_end all call resolve_undefined/resolve directly from the blocking-pool thread. Converting all five to the deferred path changed nothing — it still hung at 193.

That asymmetry may still be worth tidying on its own merits, but it is not this bug.

Where I would look next

Something accumulating per row decoded, on the drizzle path specifically, that reaches a limit at roughly a thousand rows. Given raw mysql2 is clean, my guess is the JS side rather than the Rust side — but I have not proved that and I would rather hand over an honest boundary than a confident guess.

Repro takes about eight seconds. Happy to run anything against it.

[proggeramlug]

One more control, because it was the obvious thing to suspect next: it is not the connection wrapper.

Our packages/db installs a small wrapper so drizzle's isPool() sees getConnection (the workaround for #9330), which means every transaction now checks a connection out and releases it. That churn was a fair suspect for something accumulating per transaction. It is not:

drizzle + wrapper (checks a connection out per tx)   HUNG at 387
drizzle, no wrapper (runs straight on the pool)      HUNG at 423

Both hang, at the same magnitude. Removing the checkout/release cycle entirely does not fix it.

Note these thresholds are higher than the 193–195 in my earlier comments. The difference is the drizzle instance: this harness is drizzle(pool, { mode: "default" }) with no schema, whereas the 193 figure came from our real client, which passes the full 43-table schema. Less drizzle machinery per transaction, later threshold — which is consistent with the rest of the picture, and is another reason to think the accumulation is on the JS side rather than in the driver.

Summarising the boundary as it now stands:

• deterministic, not a race — same iteration to within a couple, run after run
• independent of connectionLimit, of elapsed time, of statement length
• independent of whether the read locks (FOR UPDATE is not required)
• scales with rows returned, and with how much drizzle machinery is in play
• raw mysql2 is clean at 500 transactions in both beginTransaction and query("begin") styles
• OS thread count stays at 2 throughout
• not the resolution-site asymmetry I patched (converting all five direct resolve_* calls to the deferred path changed nothing)

[proggeramlug]

Correcting myself again: it is not row volume either. It is ~195 transactions, flat.

I said above that the threshold scales with rows returned. That was wrong, and it was wrong for the same reason my first report was — I compared a clean first loop against a later loop in the same process, which is measuring a pool that an abandoned transaction has already degraded.

One loop per process, only LIMIT varied:

rows per query	hangs at iteration	iterations × rows
1	196	196
5	195	975
10	195	1950

Flat at ~195 transactions. Ten times the rows decoded, same threshold. So nothing accumulating per row explains it.

The full negative space, all single-loop-per-process

The threshold does not move with:

varied	values	threshold
rows returned	1, 5, 10	196, 195, 195
connectionLimit	5, 10, 20	193, 193, 193
delay between iterations	0 ms, 50 ms	193 @ 8.1 s, 192 @ 17.9 s
SQL length (padding comment)	0, 200, 1000 B	195, 198, 197
locking vs not	FOR UPDATE SKIP LOCKED / plain	195 / 195

And OS thread count stays at 2 throughout.

So: deterministic, ~195 transactions, independent of time, concurrency, statement size, result size, and locking. The only things that change it are (a) whether drizzle is involved at all, and (b) how much drizzle machinery each transaction carries — the full-schema client hangs at ~195 while a bare drizzle(pool, { mode: "default" }) reaches ~390.

Still clean, for contrast

raw mysql2: getConnection + beginTransaction + query + commit + release   500/500 ok
raw mysql2: getConnection + query("begin") + query + query("commit") + release   500/500 ok

Including the form that issues begin/commit as ordinary queries, which is exactly what drizzle does. Same statements, same connection lifecycle, no hang.

What I think this narrows it to

Something with a fixed capacity that each drizzle transaction consumes a roughly constant number of, exhausted at a couple of hundred transactions and never released. Not rows, not connections, not threads, not time. The scaling with "how much drizzle machinery" rather than with any property of the SQL is what makes me think it is per-JS-object rather than per-query.

I have not identified it, and I would rather leave a clean boundary than keep guessing — but every hypothesis above is now eliminated with a measurement rather than an argument, which should save whoever picks this up a fair amount of time.

Repro is about eight seconds; happy to run anything.

[proggeramlug]

Re-tested against main at f1e9c370a (2026-09-02), toolchain rebuilt from source, caches cleared.

Still reproduces, unchanged in substance:

rows=5 -> HUNG at 201     (was 195 on 23a211383)

That includes the two mysql2 fixes merged since I filed this — #9319 (prepared-statement parameters) and #9350 (JSON column decoding), plus the follow-up that folded the JSON array arm. So neither of those was implicated, which is worth knowing: it rules out the decode path I had been circling.

Everything in the characterisation above still holds. Deterministic at ~200 transactions, independent of rows returned, connectionLimit, elapsed time, statement length and whether the read locks; raw mysql2 clean at 500 in both beginTransaction and query("begin") styles; OS threads flat at 2.

Happy to test a patch — the repro takes about eight seconds once the toolchain is built.