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.11 · 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.11 · 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