Multiversion Concurrency Control (MVCC)
MVCC provides concurrent access to the database without locking the data. This feature improves the performance of database applications in a multiuser environment. Applications will no longer hang because a read cannot acquire a lock.
MVCC provides each user connected to the database with a "snapshot" of the data to work with. The data is consistent with a point in time. Other users of the database see no changes until the transaction is committed. The snapshot can be taken at the start of a transaction, or at the start of each statement, as determined by the isolation level setting.
This release provides full MVCC support, in which readers do not block writers, and writers do not block readers.
The user invokes MVCC protocols for a session or table with the SQL statement:
SET LOCKMODE session | ON table_name WHERE LEVEL = MVCC
The alterdb command has two new options, ‑disable_mvcc and ‑enable_mvcc, which disable and enable MVCC, respectively. By default, MVCC is enabled for all existing and newly created databases.
Using MVCC is optional. Your existing applications that do not use MVCC will execute in the same manner they worked previously. The overhead of MVCC is the cost of maintaining multiple versions of database pages.
For the system administrator, MVCC may require additional buffer manager memory because Consistent Read pages occupy cache space that otherwise might be used by database pages.
The MVCC feature changes the format of many log records, which means that after running upgradedb, previous journals and checkpoints will be invalid.
For details about this feature, see the following:
• The chapters "Understanding the Locking System" and "Understanding Multiversion Concurrency Control" in the Database Administrator Guide
• The SET LOCKMODE and SET SESSION ISOLATION LEVEL statements in the SQL Reference Guide
• The alterdb command in the Command Reference Guide
Last modified date: 01/30/2023