Why Length Hurts
A long running transaction stays open for a long time, often because it does heavy work, waits on an external call, or simply forgets to commit. Its length amplifies every cost of isolation and recovery.
The Damage
- Lock holding: under locking schemes it keeps locks the whole time, so other transactions wait and throughput collapses.
- Version bloat: under MVCC it pins old versions because they might still be visible to it, blocking garbage collection and growing storage.
- Long undo log: its uncommitted work fills the log, slowing recovery and consuming space.
- Abort cost: if it fails near the end, all its work is rolled back, wasting effort.
How to Avoid It
- Keep transactions short, doing only the work that must be atomic.
- Never hold a transaction open across user input or slow network calls.
- Break large batch jobs into smaller chunks that commit independently.
- Move heavy read only analysis to a replica or snapshot outside the write path.
Key idea
Long running transactions hold locks, pin old versions, and bloat logs, so keep transactions short and chunk large jobs into independent commits.