I. Comparison of MVCC Implementations in Relational Databases
Multi-Version Concurrency Control (MVCC) is a feature designed to improve database concurrency. However, different databases implement MVCC using various methods:
1. Oracle, MySQL
These two databases implement MVCC through undo logs.
1.1 Oracle
Oracle's multi-version concurrency control is block-level, utilizing the Oracle UNDO/rollback segment mechanism. The rollback segment retains the pre-image data of a data block before modification.
When querying previous versions of data, Oracle performs the following: First, the query process looks for the pre-image of the data block in the undo segment, then merges the pre-image with the current block to form a CR block (Consistent Read block). By querying the CR block, data consistency is satisfied.
Because the pre-image of data is implemented through CR BLOCKs in the DB BUFFER, no matter how many times the data is modified, it will not have a negative impact on the storage data segment. Furthermore, once a CR BLOCK is generated, it can exist in the buffer for a long time and be used by related transactions. This function is very useful for large concurrent read operations and can greatly improve the performance of related operations.
1.2 MySQL
MySQL's InnoDB engine MVCC is also implemented through the undo segment, but unlike Oracle, MySQL's multi-version concurrency control is record-level. MySQL uses undo to form a version chain for rows.
When a data record is modified by DML, the pre-modification data record is stored in the undo log. When a client reads data, it can roll back through the undo log pointer to find the corresponding visible version.
Long-running or large transactions can cause the undo log to explode in size, which to a certain extent can lead to a surge in disk space occupied by system log files. Only when transactions are committed/rolled back and the relevant version records are no longer needed are the corresponding version data cleaned up and undo system file space released. The specific space release operation depends on the database version and parameter settings.
2. PostgreSQL
It implements MVCC by retaining records before changes.
PostgreSQL does not have the concept of undo. PostgreSQL's multi-version concurrency is implemented through multiple versions of data rows in a table. When updating a record in a table, PostgreSQL does not modify the data directly, but instead inserts a completely new data record and marks the old data.
When a data record is modified by DML, the old version record remains unchanged. It only needs to modify the relevant record's xmin and xmax attributes and insert the changed version record data.
Since historical version data is still retained in the original tablespace, the autovacuum will detect and perform certain cleanup according to a certain parameter setting strategy by default, but frequent data changes can greatly lead to old version data space not being recycled in time, resulting in tablespace bloat.
3. SQL Server
It implements MVCC through the tempdb database.
When a data record is modified by DML, the old version data is written to tempdb for storage. When a client reads data, it can find the corresponding visible version in the tempdb database through a pointer.
Long-running or large transactions can cause tempdb space to explode. Only when transactions are committed and the relevant version records are no longer needed are the relevant version records released. It should be noted that this part of the disk space consumption is not released to the operating system and needs to be manually shrunk.
II. Comparison of Advantages and Disadvantages of MVCC Implementation Methods
Storing old versions through undo logs or tempdb effectively avoids tablespace bloat. Compared to PG's method of directly retaining old version data, each DML operation requires additional log writes, resulting in a certain IO overhead (currently, with SSD storage, the impact is not felt to be very significant).
PG's method of directly retaining old version data does not require the overhead of additional log writes, but to a certain extent, it can lead to old version data not being cleaned up in time, resulting in tablespace bloat and affecting the query efficiency of the table's data (scanning unnecessary data pages).