What is MVCC and how do PostgreSQL, InnoDB, Oracle and SQL Server implement it?
What is MVCC and how do PostgreSQL, InnoDB, Oracle and SQL Server implement it?
Multi-Version Concurrency Control (MVCC) keeps several versions of a row so readers see a consistent snapshot without blocking writers, and writers do not block readers. Each transaction reads the version that was valid for its snapshot.
In a purely lock-based system a reader takes shared locks and blocks writers. With MVCC, SELECT normally takes no row locks, so read-heavy and mixed workloads scale. Writers still block each other on the same row.
| Engine | Where old versions live | Cleanup |
|---|---|---|
| PostgreSQL | In the table itself: an update writes a new tuple; each tuple has xmin (creator) and xmax (deleter) transaction ids. |
VACUUM / autovacuum removes dead tuples; unvacuumed tables bloat. |
| MySQL InnoDB | The row is updated in place; previous versions are rebuilt from the undo log using a read view. | A purge thread removes undo records no longer needed. Long transactions inflate the history list. |
Đăng nhập để tham gia thảo luận cùng mọi người.
Chưa có bình luận nào
Hãy là người đầu tiên chia sẻ suy nghĩ của bạn.