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

Activity

1. proggeramlug commented on Aug 31, 2026

   

   proggeramlug

   on Aug 31, 2026

   ContributorAuthor

   More actions

   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.
2. proggeramlug

   changed the title [-]mysql2/drizzle: a committed write transaction is never ended — connection returns to the pool holding row locks[/-] [+]mysql2/drizzle: a transaction does not pin its pooled connection — statements scatter, breaking atomicity[/+] on Aug 31, 2026
3. proggeramlug commented on Sep 1, 2026

   

   proggeramlug

   on Sep 1, 2026

   ContributorAuthor

   More actions

   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.
4. proggeramlug commented on Sep 2, 2026

   

   proggeramlug

   on Sep 2, 2026

   ContributorAuthor

   More actions

   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.
5. proggeramlug

   closed this as completedon Sep 2, 2026