MVCC
Multi-Version Concurrency Control is a database concurrency control mechanism that maintains multiple versions of data so that reads do not block writes and writes do not block reads, preserving transaction isolation while maintaining high concurrency performance.
Timeline (last 90 days)
OLTP systems almost all require strict ACID, using row-level locks or MVCC to manage concurrency.
In HTAP scenarios, MVCC's version chain and visibility determination mechanism allow analytical queries to read the latest committed data version without stopping transactional writes, thereby eliminating read-write conflicts and avoiding ETL synchronization latency.
Multi-version concurrency control (MVCC) generates new versions of data while retaining old versions, allowing read operations to choose a consistent snapshot based on the transaction start time without waiting for write operations to complete.
InnoDB's MVCC is implemented by modifying rows in place and tracking old versions with an undo chain: each row hides a transaction ID and a rollback pointer, and the rollback pointer strings together old versions in the undo log to form a version chain; read committed (RC) generates a new snapshot on each statement execution, while repeatable read (RR) generates a snapshot at transaction start.
MySQL's default isolation level is repeatable read, which implements snapshot reads through MVCC to avoid non-repeatable reads.
MVCC (Multi-Version Concurrency Control) avoids read-write blocking by generating a new version for each write operation and having read operations read a consistent snapshot.
Linux's fork() system call, ZFS filesystem snapshot functionality, and the MVCC mechanisms of many modern databases all rely on the copy-on-write principle.
PostgreSQL internally contains multiple highly coupled subsystems, including a query parser, planner/optimizer, executor, storage manager (including MVCC multi-version concurrency control), and WAL write-ahead logging.
PostgreSQL's MVCC mechanism, ZFS/Btrfs filesystem snapshots, and some cloud database services use copy-on-write or similar storage tiering technology as their underlying foundation.
When the number of subtransactions exceeds 64 and a subxid overflow occurs, other transactions must frequently access the pg_subtrans SLRU cache during MVCC visibility checks, leading to intensified lock contention, increased disk I/O, and reduced throughput.
All Facts (11)
PostgreSQL的MVCC机制下，每次UPDATE操作实际上是写入一条新的行版本，对大JSONB字段频繁增量更新会导致写放大问题
95%UnverifiedOLTP systems almost all require strict ACID, using row-level locks or MVCC to manage concurrency.
50%UnverifiedMulti-version concurrency control (MVCC) generates new versions of data while retaining old versions, allowing read operations to choose a consistent snapshot based on the transaction start time without waiting for write operations to complete.
50%UnverifiedIn HTAP scenarios, MVCC's version chain and visibility determination mechanism allow analytical queries to read the latest committed data version without stopping transactional writes, thereby eliminating read-write conflicts and avoiding ETL synchronization latency.
50%UnverifiedInnoDB's MVCC is implemented by modifying rows in place and tracking old versions with an undo chain: each row hides a transaction ID and a rollback pointer, and the rollback pointer strings together old versions in the undo log to form a version chain; read committed (RC) generates a new snapshot on each statement execution, while repeatable read (RR) generates a snapshot at transaction start.
50%UnverifiedMySQL's default isolation level is repeatable read, which implements snapshot reads through MVCC to avoid non-repeatable reads.
50%UnverifiedMVCC (Multi-Version Concurrency Control) avoids read-write blocking by generating a new version for each write operation and having read operations read a consistent snapshot.
50%UnverifiedLinux's fork() system call, ZFS filesystem snapshot functionality, and the MVCC mechanisms of many modern databases all rely on the copy-on-write principle.
50%UnverifiedPostgreSQL internally contains multiple highly coupled subsystems, including a query parser, planner/optimizer, executor, storage manager (including MVCC multi-version concurrency control), and WAL write-ahead logging.
50%UnverifiedPostgreSQL's MVCC mechanism, ZFS/Btrfs filesystem snapshots, and some cloud database services use copy-on-write or similar storage tiering technology as their underlying foundation.
50%UnverifiedWhen the number of subtransactions exceeds 64 and a subxid overflow occurs, other transactions must frequently access the pg_subtrans SLRU cache during MVCC visibility checks, leading to intensified lock contention, increased disk I/O, and reduced throughput.
50%