When a transaction stays open for a long time, it keeps holding its locks and the DB can’t clean up (purge) old versions of data, so everything gradually slows down.
Why A transaction stays open while waiting for another server’s response, or a long aggregate query runs on the primary during service → Effect Its locks are never released, and old row versions awaiting cleanup keep piling up → On screen Features that use those rows time out, and saves and lookups slow down across the board over several hours
Primary owner Game team (Server development) · Also Infra team (DB infrastructure)
Game team action items
Don’t wait on network calls or user input inside a transaction, run aggregate queries on a replica.
Infra team action items
Alert on and kill long-open transactions, provide a replica for aggregation, watch undo log and dead row growth.
On the graph
Slow climb · Undo log length (History list length), dead row count
Where to look
MySQL: find the oldest transaction by trx_started in INFORMATION_SCHEMA.INNODB_TRX, and check History list length (undo log not yet purged) in the TRANSACTIONS section of SHOW ENGINE INNODB STATUS. PostgreSQL: xact_start in pg_stat_activity, sessions whose state is idle in transaction, and n_dead_tup in pg_stat_user_tables
Confirmed if
A transaction minutes to hours old exists, History list length or n_dead_tup keeps climbing while it’s open, then falls as cleanup (purge, VACUUM) runs after that transaction ends
Ruled out if
Slow across the board with no old transactions: checkpoints (db-checkpoint) or the disk
Check with
Infra tools (no game code needed)
Learn more
The DB keeps old versions so readers can see data as it was before a change (MVCC). These records can only be removed once the oldest transaction ends, so if one transaction stays open for hours, MySQL piles up undo logs and PostgreSQL piles up dead rows (dead tuples) that VACUUM can’t clean up. In SQL Server, the transaction log can’t shrink and may fill the disk.
Sources
InnoDB Multi-VersioningMySQL While a transaction that can see old versions remains, update undo logs can’t be discarded and the rollback segment grows; recommends committing often, even for read-only transactions
Routine Vacuuming (PostgreSQL Documentation)PostgreSQL Old row versions can’t be removed while other transactions can still see them; long-open transactions must be ended or their sessions terminated
Purge ConfigurationMySQL Purge cleans up the list of undo logs from committed transactions (history list); the backlog shows as History list length in the TRANSACTIONS section of SHOW ENGINE INNODB STATUS