Long and idle transactions

Every chapter has ended up pointing here. The idle transaction holds locks, pins purge, blocks DDL — and does it all silently, because doing nothing is its defining feature. This lesson is the detection kit:

A starts a 'quick report'… and never gets around to committing.

A> BEGIN;
Query OK

A> SELECT count(*) AS orders FROM orders;
 orders 
--------
      2 
(1 row)

Detector 1 — the oldest open transaction, its owner, and how long it's been open:

M> SELECT CAST(sn.variable_value AS CHAR) AS session_name, t.trx_state,
          timestampdiff(SECOND, t.trx_started, now()) >= 1 AS older_than_1s
   FROM information_schema.innodb_trx t
   JOIN performance_schema.threads th ON th.processlist_id = t.trx_mysql_thread_id
   JOIN performance_schema.user_variables_by_thread sn ON sn.thread_id = th.thread_id
   WHERE sn.variable_name = 'session_name'
   ORDER BY t.trx_started LIMIT 1;
 session_name | trx_state | older_than_1s 
--------------+-----------+---------------
 A            | RUNNING   |             1 
(1 row)

Detector 2 — transactions whose session is idle (command Sleep) while holding them open:

M> SELECT CAST(sn.variable_value AS CHAR) AS session_name, p.command
   FROM information_schema.innodb_trx t
   JOIN performance_schema.processlist p ON p.id = t.trx_mysql_thread_id
   JOIN performance_schema.threads th ON th.processlist_id = p.id
   JOIN performance_schema.user_variables_by_thread sn ON sn.thread_id = th.thread_id
   WHERE sn.variable_name = 'session_name' AND p.command = 'Sleep' AND p.time >= 1;
 session_name | command 
--------------+---------
 A            | Sleep   
(1 row)

Detector 3 — why it matters even for a read-only report: A never wrote a row (it doesn't even have a real transaction ID), yet its read view is exactly what purge must wait for.

M> SELECT t.trx_rows_modified AS rows_modified,
          CAST(t.trx_id AS UNSIGNED) > 281474976710656 AS never_wrote
   FROM information_schema.innodb_trx t
    -- read-only — and still the oldest read view on the server
 rows_modified | never_wrote 
---------------+-------------
             0 |           1 
(1 row)

A> COMMIT;
Query OK

Verified against MySQL 8.4.10 · Run it yourself · Scenario source

Detector 3 is the counterintuitive one: the report transaction never wrote a row — it doesn't even have a real transaction ID — yet it's the oldest read view on the server, which makes it exactly what purge waits for. Read-only is not harmless.

The guardrails

PostgreSQL lets you cap transaction and idle-in-transaction time server-side. MySQL's toolbox is smaller — one per-statement ceiling, one session-idle killer, and nothing in between:

Guardrail 1 — max_execution_time: a per-session (or per-query) ceiling for SELECTs.

A> SET SESSION max_execution_time = 100;
Query OK

A> SELECT SLEEP(2) AS interrupted; -- cut short at 100ms — SLEEP reports 1 when its wait is interrupted
 interrupted 
-------------
           1 
(1 row)

A> SET SESSION max_execution_time = 0;
Query OK

Guardrail 2 — wait_timeout: the server hangs up on a session that goes quiet. A opens a transaction, updates a row… and stops talking.

A> SET SESSION wait_timeout = 1;
Query OK

A> BEGIN;
Query OK

A> UPDATE accounts SET balance = 999 WHERE id = 1;
Query OK, 1 row affected

A> SELECT balance FROM accounts WHERE id = 1;
ERROR ERR_MYSQL_CONNECTION_CLOSED (HY000): Connection closed

The app discovers the corpse on its next statement — and the uncommitted UPDATE is gone.

B> SELECT balance FROM accounts WHERE id = 1; -- rolled back with the session
 balance 
---------
     100 
(1 row)

The third guardrail, innodb_lock_wait_timeout, was proven in chapter 3 — remember it rolls back the STATEMENT, not the transaction.

Verified against MySQL 8.4.10 · Run it yourself · Scenario source

The gap matters, and it's a real one: MySQL has no idle_in_transaction_session_timeout equivalent. The session that opens a transaction and then waits on a slow API for 90 seconds is invisible to max_execution_time (no statement running) and untouched by any sane wait_timeout (that would also kill healthy idle pool connections). Your options are the detectors above on a schedule, an application-side transaction deadline, or a proxy that enforces one. This is why ORM pitfall #2 has to be fixed in code — the server won't save you.

The detection kit and the guardrails split the work between them. innodb_trx finds the long transactions by trx_started and, joined to a command = Sleep processlist row, the idle ones that are worse; it flags the read-only offenders too, since those pin purge all the same, so age-alert on the oldest trx_started whether or not it ever wrote a row. The guardrails then cap what they can — max_execution_time for a runaway SELECT, wait_timeout for a dead-quiet session (rolling back whatever it held), and innodb_lock_wait_timeout (chapter 3) for a lock wait, though that last one rolls back only the statement, so a half-done transaction plus a naive retry is a data bug. None of the three closes the idle-in-transaction gap; that one you close in code.

Further reading

• MySQL docs: information_schema.INNODB_TRX
• The same lesson on PostgreSQL