Repository navigation
mysql2 + drizzle: transactions deterministically hang after ~195, independent of rows, time, pool size and locking #9356
Description
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_trxis 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 PROCESSLISTshows no running query.- The process stays alive and its event loop keeps running:
epoll_waitreturns 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.
Activity
proggeramlug commentedon Sep 1, 2026
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:
→
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:
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,COMMITare three separaterun_pool_conn_querycalls 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_queryalready carries this comment at the resolve site: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 UPDATEinside 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.[-]mysql2: a transaction containing SELECT … FOR UPDATE intermittently never completes (~1 in 200)[/-][+]mysql2: repeated transactions intermittently hang or segfault (~1 in 200) — likely another #1824 allocation on the blocking-pool thread[/+]proggeramlug commentedon Sep 1, 2026
Correction: the locking read is not the trigger. Row volume is.
I filed this as a
FOR UPDATEproblem. 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.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:
connectionLimitSo: 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
Both raw shapes survive 500 transactions, including the one that issues
begin/commitas 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,sqltemplate 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) whilerun_simple_command,pool_get_connection,create_connection,connection_endandpool_endall callresolve_undefined/resolvedirectly 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.
[-]mysql2: repeated transactions intermittently hang or segfault (~1 in 200) — likely another #1824 allocation on the blocking-pool thread[/-][+]mysql2 + drizzle: transactions returning multiple rows deterministically hang after ~195 iterations (no lock required)[/+]proggeramlug commentedon Sep 1, 2026
One more control, because it was the obvious thing to suspect next: it is not the connection wrapper.
Our
packages/dbinstalls a small wrapper so drizzle'sisPool()seesgetConnection(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: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:
connectionLimit, of elapsed time, of statement lengthFOR UPDATEis not required)mysql2is clean at 500 transactions in bothbeginTransactionandquery("begin")stylesresolve_*calls to the deferred path changed nothing)proggeramlug commentedon Sep 1, 2026
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
LIMITvaried: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:
connectionLimitFOR UPDATE SKIP LOCKED/ plainAnd 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
Including the form that issues
begin/commitas 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.
[-]mysql2 + drizzle: transactions returning multiple rows deterministically hang after ~195 iterations (no lock required)[/-][+]mysql2 + drizzle: transactions deterministically hang after ~195, independent of rows, time, pool size and locking[/+]proggeramlug commentedon Sep 2, 2026
Re-tested against
mainatf1e9c370a(2026-09-02), toolchain rebuilt from source, caches cleared.Still reproduces, unchanged in substance:
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; rawmysql2clean at 500 in bothbeginTransactionandquery("begin")styles; OS threads flat at 2.Happy to test a patch — the repro takes about eight seconds once the toolchain is built.
fix(runtime): native-async promises are non-moving, their tokens are …
changelog: fragment for #9520 (native-async non-moving promises, #9356)
turnloop P7: PostgreSQL and MongoDB on the loop-driven transport
Merge origin/main into turnloop/integration