What is Multiversion Concurrency Control (MVCC)?

MVCC is a concurrency control mechanism used in databases to allow multiple transactions to operate on the same data simultaneously by maintaining multiple versions of data items. Instead of locking rows, MVCC creates a new version of a row whenever a write occurs, ensuring that:

• Transactions can read consistent snapshots of the database.
• Write operations do not block read operations.

How MVCC Works

1. Data Versioning:

   • Each data item in the database is associated with multiple versions, each identified by a transaction.
   • Each version contains metadata, such as:
     • created_by: The transaction that created the version.
     • deleted_by: The transaction that invalidated or deleted the version.

2. Snapshot Isolation:

   • Transactions work on a snapshot of the database taken at their start time.
   • Changes made by other transactions are not visible until committed.

3. Conflict Resolution:

   • MVCC avoids conflicts by allowing reads and writes to occur on different versions of the same data.

Example

In the image below, data versions are created as transactions modify balances in two accounts.

Initial State (T1):

The database starts with two accounts:

• Account A has an initial balance of $500.
• Account B has an initial balance of $300.

Each account has a version associated with it, created by the transaction that initialized the data. Both accounts currently have no deleted versions.

Transaction 1 (T2): Increase Account A’s Balance by $200

• A new version of Account A is created to reflect the updated balance of $700.
• The previous version of Account A, which had a balance of $500, is marked as invalid (deleted) by the current transaction.
• Account B remains unchanged, as this transaction does not interact with it.

Transaction 2 (T3): Decrease Account A’s Balance by $100

• Another new version of Account A is created, reducing the balance to $600.
• The version created in Transaction 1, with a balance of $700, is now marked as invalid (deleted) by this transaction.
• Similar to the previous transaction, Account B remains unaffected.

Key Points in the Example

1. Version Creation:

   • Every write operation creates a new version of the data.
   • The deleted_by field indicates which transaction invalidated the previous version.

2. Consistent Snapshots:

   • Transactions can read the most recent committed version of a row that existed at their start time.
   • For example, while Transaction 3 updates Account A, another transaction can still read the previous version with a balance of $500.

3. No Locks:

   • MVCC eliminates the need for locks, allowing reads and writes to occur simultaneously.

Real-World Use Cases

1. E-Commerce Platforms: Users can browse product listings while others update inventory or prices.
2. Banking Systems: Customers can view account balances while transactions are being processed.
3. Content Management Systems: Editors can read and edit content simultaneously without conflicts.