Long and idle transactions

On MySQL 8.4 InnoDB, this schedule opens a REPEATABLE READ transaction, reads two orders, and leaves the connection idle. The queries assert its age, Sleep command, and zero modified rows. The artificial @session_name tags identify lesson sessions; production attribution needs your own connection identifiers and suitable privileges.

A> SELECT @@session.transaction_isolation AS isolation_level;
 isolation_level 
-----------------
 REPEATABLE-READ 
(1 row)

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 tagged transaction, its owner, and a lower bound on its age.

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: A has modified no rows. This observation does not identify a purge blocker or rely on an implementation-specific transaction ID.

M> SELECT t.trx_rows_modified AS rows_modified
   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' AND sn.variable_value = 'A'
    -- no modified rows in the observed transaction
 rows_modified 
---------------
             0 
(1 row)

A> COMMIT;
Query OK

Verified against MySQL 8.4.11 · Run it yourself · Scenario source

RUNNING in innodb_trx is a transaction state, not proof that a statement is running. The joined processlist supplies the Sleep observation. trx_rows_modified = 0 describes this transaction's modifications, not the age of its read view or a purge diagnosis. The column manual defines TRX_STARTED: "The transaction start time." It does not report snapshot creation time. An idle transaction can retain row locks, a read view, or metadata locks depending on its earlier operations; this detector does not prove all three for every row.

The guardrails

Guardrail 1: max_execution_time applies to eligible read-only SELECTs, not all statements.

A> SET SESSION max_execution_time = 100;
Query OK

A> SELECT SLEEP(2) AS interrupted; -- SLEEP reports interruption; no exact duration is asserted
 interrupted 
-------------
           1 
(1 row)

A> SELECT balance FROM accounts WHERE id = 1;
 balance 
---------
     100 
(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 — with innodb_rollback_on_timeout=OFF it rolls back the waiting statement; ON requests transaction rollback. See the chapter 3 evidence and manual.

Verified against MySQL 8.4.11 · Run it yourself · Scenario source

The MySQL 8.4 variable manual states: "max_execution_time applies to read-only SELECT statements." It excludes SELECTs in stored programs and is not a general statement or transaction deadline. Here, SLEEP(2) reports interruption and a later query proves the connection is usable; no exact elapsed time or general timeout-error behavior is asserted.

For noninteractive connections, wait_timeout limits idle connection time, including idle pooled connections. The demonstrated one-second setting closes A and rolls back its uncommitted update. It does not distinguish healthy idle connections from idle transactions. Choose settings with pool behavior and reconnection handling in mind. Interactive sessions initialize this timeout differently, as documented under wait_timeout.

For InnoDB row-lock waits, chapter 3 asserts that 1205 leaves earlier work and locks in the explicit transaction when innodb_rollback_on_timeout=OFF. The error-handling manual documents transaction rollback with that option enabled. To rerun the whole operation after a timeout, explicitly roll back first. Deadlock 1213 rolls back the transaction. Metadata-lock waits use lock_wait_timeout.

These variables provide different limits; none is a transaction-specific idle deadline like PostgreSQL's idle_in_transaction_session_timeout. Use application deadlines and monitoring appropriate to the workload. A KILL CONNECTION decision must account for rollback, disconnected clients, and whether retrying application effects is safe.

Further reading

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