Login Register Actian.com  

Actian Community Wiki

Learn About
Developing With
Ingres Talk


From Ingres Community Wiki

Jump to: navigation, search



For a brief overview of the feature, see this document.

For guidance on how to use MVCC, see the MVCC User Guide.

The DDS is available here.

The MVCC Code Walkthrough document is available here.

MVCC (Multi Version Concurrency Control)

Concurrency control allows transactions to be performed concurrently without violating data integrity. Transactions should be executed reliably, and follow the ACID rules (Atomicity, Consistency, Isolation, Durability).

Isolation defines when changes to data are visible to concurrent transactions. Providing transaction isolation is the main goal of consistency control. ANSI defines four isolation levels, which allow the application to choose an appropriate level of concurrency and consistency.

  • Serializable
  • Repeatable Read
  • Read Committed
  • Read Uncommitted

Currently Ingres uses locks to provide concurrency control. This method can cause performance degradation due to lock bottlenecks. Decision support type queries that read a lot of data can interfere with efficient processing of OLTP type updates.

MVCC is a concurrency control method used to provide concurrent access to data without locking the data. It allows readers to get consistent data without blocking updaters. It provides readers a consistent view of the data by maintaining previous versions of the data. The overhead is the cost of maintaining multiple row versions.

MVCC provides each transaction a snapshot of the data. All reads are consistent to some point in time. In most database systems that have implemented MVCC, the point in time where the snapshot of the data is taken is defined by the isolation level, e.g.

  • Serializable: Point in time for MVCC snapshot is the start of the transaction, providing transaction level read consistency.
  • Read Committed: Point in time MVCC snapshot is the start of the query, providing statement level read consistency.

Most databases today support Multi-version concurrency control. MVCC was first available in Oracle so most application developers understand how to build applications for MVCC databases and the lack of this capability make porting applications to Ingres difficult and increase the learning curve for application developers who try to develop applications for Ingres.

MVCC is a major architectural change for Ingres. It needs to be introduced into the product in such a way that it is optional. Ingres must continue to provide a means to execute all existing applications in the same manner they work today, but allow new applications to be written that require MVCC. Ideally, the ability to use MVCC should be at the program level using ISOLATION LEVELS. Architectural issues may require MVCC to be restricted to DATABASE or INSTANCE level.

MVCC should be introduced in phases to shorten the delivery schedule. Phase 1 should be 2VCC, "Readers don't block writers".

MVCC should be implemented as in Oracle as this will provide the largest opportunity for migrations and has the largest developer base.

Ingres Enhancement Number


DDS Review (Nov 3, 2009)

  • Issue an error or a warning if MVCC was requested but requirements not met for MVCC. Whether an error or warning is returned depends on whether the user has explicitly requested MVCC locking.
  • When a cursor is defined for update, rows will be exclusively locked as they are fetched.
  • The default page type for tables will also support rows spanning pages in case you later need to alter the table such that the new row size doesn't fit on the page. A new option will be added to ALTER TABLE to override the default of supporting page spanning rows. This will not be included in the first code submission.
  • MVCC protocols can be used for blobs, although not in the first code submission.
  • Updated the DDS to explain how isolation level, lock level and readlock settings affect the locking strategy used by Ingres.

Test Considerations

We need to test this through Set, Star, ESQL, ABF, TM, DBPs, LIBQ, JDBC, ODBC, OpenAPI.

User:Durwin OpenROAD can be covered on Windows by using Query Tool Query Tool. It consists of two files: (1) qt.exe and (2) orrun.dll.

OS Dependencies


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 do not see any changes until the transaction is committed.

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. By default, MVCC is enabled for all existing and newly created databases.

Use of MVCC is optional. Use of MVCC is at the program level using isolation levels. It is also restricted to the database level. The overhead of MVCC is the cost of maintaining multiple versions of database pages. Your existing applications that do not use MVCC will execute in the same manner they work today.

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 guidance on how to use MVCC, see the MVCC User Guide.

Personal tools
© 2011 Actian Corporation. All Rights Reserved