Comparison
Idle in transactionvsMVCC
Idle in transaction
someone opened a transaction, went off to call an HTTP API, and left locks and dead row versions pinned for eight seconds.
A transaction that is open but not doing work, usually because network I/O happened between BEGIN and COMMIT. It holds locks, blocks vacuum or version cleanup, and pins the replication slot. Keeping transactions short and free of external calls is one of the highest-value rules in application database code.
Full entry →MVCC
an update writes a new version of the row instead of overwriting it, so readers already in flight keep seeing the old one.
Keeping multiple versions of each row so that readers see the version valid for their snapshot rather than waiting on a lock. It is what makes concurrent reads cheap. The cost is garbage: dead versions accumulate and must be reclaimed, and a single long-running transaction can pin them all, bloating tables and slowing everything down.
Full entry →