Huge Multiversion Concurrency Control (MVCC) Versions in HANA

What is MVCC?

MVCC is a database concurrency control method that allows multiple transactions to occur concurrently without conflicting with each other. In a nutshell, it ensures that each transaction sees a snapshot of the database at a specific point in time, even if other transactions are making changes concurrently.

MVCC in SAP HANA:

SAP HANA uses MVCC to manage concurrent access to data. Each transaction in HANA sees a consistent snapshot of the data at the time the transaction began. This is achieved by maintaining multiple versions of a data row, each associated with a specific transaction or point in time.

The Issue of Huge MVCC Versions:

Now, the term "Huge MVCC Versions" indicates a situation where there is a significant number of these versions for a particular set of data. Here's why this might become a problem:

Increased Memory Usage:

Each version of a data row consumes memory.

As the number of versions increases, the overall memory consumption by the database grows.

Resource Contentions:

When there are numerous versions of the same data being accessed concurrently, it can lead to resource contentions.

Multiple transactions might contend for the same set of resources, impacting performance.

Transaction Isolation:

While MVCC is excellent for providing transaction isolation, an excessively large number of versions can impact the speed at which transactions can be processed.

Retrieving the correct version for a specific transaction becomes more resource-intensive.

System Cleanup Overhead:

Periodically, the system needs to clean up old and unnecessary versions to reclaim memory.

If there are too many versions, this cleanup process can become resource-intensive and may impact the overall system performance.

Causes of Huge MVCC Versions:

Long-Running Transactions:

Transactions that run for an extended period may contribute to a large number of versions.

Frequent Updates:

If data is frequently updated, it can lead to the creation of numerous versions.

High Transaction Volume:

A high volume of concurrent transactions can result in a larger number of versions being generated.

Mitigation Strategies:

Optimize Queries:

Optimize queries to minimize the number of unnecessary versions that need to be maintained.

Transaction Management:

Manage transactions effectively to reduce the duration of long-running transactions.

Data Archiving and Purging:

Implement data archiving and purging strategies to remove obsolete data versions.

Memory Configuration:

Adjust memory configurations to ensure sufficient resources are allocated for managing versions.

Database Maintenance:

Regularly perform database maintenance tasks to clean up old versions and optimize database performance.

In order to validate that there is a problem with MVCC, check the number of active versions in the row-store MVCC manager monitoring view (M_MVCC_TABLES). Note that in a multihost environment, you have to check the master host

select * from m_mvcc_tables where host='MyHost' and port='30003' and 

(name='NUM_VERSIONS' or name='MAX_VERSIONS_PER_RECORD' or 

name='TABLE_ID_OF_MAX_NUM_VERSIONS'); 

Read more in this SAP NOTE 

Read more in this SAP document