IT Solutions Technology Blog

  Starting with Ingres 10.0 databases are created with Multi Version Concurrency Control (MVCC) as a default also databases upgraded from prior releases also have MVCC enabled

Table Requirements
MVCC only applies to tables that meet the following criteria:

They were not created with a page size of 2K
They are permanent tables; i.e., they are not temporary tables
They are not using the rtree storage structure
They are not one of the core system catalogs (i.e., iirelation, iiattribute)
They are not gateway tables
They do not contain data rows that are larger than the table’s page size

Other Requirements
You are not using an isolation level of read uncommitted
You are not using readlock = nolock
Logging has not been disabled, unless you are only issuing read-only queries
Fast commits have not been disabled
You are not running in a cluster environment

The easiest method to check a database’s MVCC status is to use:

infodb <dbname>

==================Fri Dec 17 08:27:01 2021 Database Information=================

Database : (testdb,actian) ID : 0x0B3687C5 Default collation :
Unicode enabled : Yes
Always logged : No
X100 supported : Yes
Database Encryption : No
Default unicode collation : udefault Unicode normalization : NFC
Extents : 5 Last Table Id : 265
Config File Version Id : 0x00090001 Database Version Id : 12
Mode : DDL ALLOWED, ONLINE CHECKPOINT ENABLED
Access : PUBLIC
Status : VALID,JOURNAL,CKP,DUMP,CFG_BACKUP

The Database has been Checkpointed.
The Database is Journaled.

MVCC is enabled in this database.

Journals are valid from checkpoint sequence : 2

We can however disable MVCC using:

alterdb -disable_mvcc <dbname>
Fri Dec 17 08:28:48 2021 ALTERDB: MVCC disabled for Database ‚testdb‘.

Use infodb again to verify

==================Fri Dec 17 08:29:42 2021 Database Information=================

Database : (testdb,actian) ID : 0x0B3687C5 Default collation :
Unicode enabled : Yes
Always logged : No
X100 supported : Yes
Database Encryption : No
Default unicode collation : udefault Unicode normalization : NFC
Extents : 5 Last Table Id : 265
Config File Version Id : 0x00090001 Database Version Id : 12
Mode : DDL ALLOWED, ONLINE CHECKPOINT ENABLED
Access : PUBLIC
Status : VALID,JOURNAL,CKP,DUMP,NOMVCC,CFG_BACKUP

The Database has been Checkpointed.
The Database is Journaled.

MVCC is disabled in this database.

Journals are valid from checkpoint sequence : 2