Multiversion Concurrency Control

Introduction

Multiversion concurrency control (MVCC) is a concurrency control technique that allows multiple versions of the same record to exist simultaneously during a given time and provides a consistent view of the database at a point in the past identified by the timestamp.

With this method, every active transaction in the database sees a snapshot of the data at that point. The new transactions won’t see any changes made by other transactions until the database commits the transaction.

In addition, multiversion concurrency control techniques prevent nonrepeatable reads problems.

Implementation

When a database transaction starts, the database generates a unique, monotonically increasing transaction identifier (txid). The database tags every data inserted or updated with the txid.

Additionally, every record in the database has a created_by field that contains the txid that inserted a particular record. Similarly, every record has a deleted_by field that initially remains empty, but is updated with the new txid that modified the record.

When the database updates data, it creates a new version of the data instead of overwriting it. Thus, the implementation creates multiple versions of the same data at a given time. The most common isolation level implemented with MVCC is snapshot isolation.