TopolearnExplains slowdowns from long-running transactions and how InnoDB's MVCC differs from PostgreSQL's.
InnoDB implements MVCC differently from PostgreSQL. It updates rows in place in the clustered index and writes the previous version to an undo log. Each row carries hidden fields: the id of the transaction that last changed it (DB_TRX_ID) and a roll pointer to its undo record. Undo records chain back through older versions.
A consistent read uses a read view: the list of transactions active when the snapshot was taken. If the current row version was written by a transaction the read view can't see, InnoDB follows the roll pointer and reconstructs the older version from undo. In REPEATABLE READ the read view is created at the first consistent read in the transaction; in READ COMMITTED a new one per statement.
Secondary indexes don't store versions; they are marked for deletion and checked against the clustered record, so consistent reads through secondary indexes can need extra lookups.
Old undo records are removed by purge threads once no read view needs them. The backlog is the history list length (shown in SHOW ENGINE INNODB STATUS and information_schema.innodb_metrics as trx_rseg_history_len). A transaction left open for hours — or a long analytical query on the primary — prevents purge: the history list grows into millions, undo tablespaces swell, and every read has to walk longer version chains, slowing the whole server.
Monitor information_schema.innodb_trx for old transactions and keep long analytics on replicas.
Example
-- oldest open transactions: the usual cause of purge lag
SELECT trx_id, trx_state, trx_started,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_s,
trx_rows_modified, trx_mysql_thread_id
FROM information_schema.innodb_trx
ORDER BY trx_started
LIMIT 5;
-- history list length (undo records waiting for purge)
SELECT name, count
FROM information_schema.innodb_metrics
WHERE name = 'trx_rseg_history_len'; -- healthy: thousands; trouble: millions
-- same figure in the status report
SHOW ENGINE INNODB STATUS\G
-- ------------
-- TRANSACTIONS
-- ------------
-- History list length 1834Remember
- InnoDB updates in place and keeps old versions in undo logs
- Read views decide which version a consistent read reconstructs
- Long transactions block purge and grow the history list
Watch out for
- Leaving an idle transaction open in a console or connection pool for hours.