Skip to main content

KlustronDB Global MVCC Feature Introduction

KlustronDBAbout 4 min

KlustronDB Global MVCC Feature Introduction

Note:

Unless otherwise specified, the version numbers in the text can be replaced with the version numbers of any released version. For all released versions, see: Release_notes

Overview

As a distributed database that can fully support strong consistency scenarios such as finance and securities, data read consistency is an indispensable characteristic of Klustron. Global MVCC is KlustronDB's global multi-version concurrency control mechanism used to solve the data consistency problem of cross-shard queries in distributed clusters. Its working principle is detailed in this document.

Global MVCC runs automatically in the system, and once the cluster is installed, no user operation or configuration is required. As long as Global MVCC is enabled when deploying the KlustronDB cluster, it will run. The operation of Global MVCC consumes a small amount of resources and has a performance overhead, so it is recommended to enable it only in scenarios that require high query consistency.

In this article, a bank account scenario is constructed to verify the different result states obtained when querying the account with Global MVCC turned on and off, thereby demonstrating the importance of enabling Global MVCC when strong consistency is required at the financial level.

01 Why Global MVCC is Needed

First, let's look at the read consistency problem of distributed transactions, as shown in the figure below.

Without Global MVCC:

  • Time A1: Client1 initiates a transaction and inserts two records into table t1: (1, 'Beijing') and (2, 'Shenzhen'). Suppose the target shards for storing these two records are shard1 and shard2, respectively.
  • Time A2: Client1 performed a transaction commit.
  • Time A3: Client2 initiated a query on t1: select * from t1; Suppose that at time A3, the transaction committed by Client1 has been written and committed on shard1, but has not yet been committed on shard2.

At this time, Client2 can see (1, 'Beijing') but cannot see (2, 'Shenzhen'), thus seeing only part of the transaction's results.

To solve this problem, KlustronDB implements Global MVCC, the principle of which is mainly to obtain the visible data of the current transaction by establishing a global snapshot.

02 Enable Global MVCC

In KlustronDB, you need to enable the global MVCC option when creating a cluster, and then create the cluster. By default, global MVCC is not enabled.

**03 Test Case

3.1 Environmental Preparation

Log in to the Kluscomp instance through the PG client and create users and databases.

psql -h 10.37.129.6 -p 47001 postgres
create user kunlun_test with password 'kunlun';
create database test_db with owner kunlun_test encoding utf8 template template0;
\q
psql -h 10.37.129.6 -p 47001 -U kunlun_test test_db

Query the IDs corresponding to the two shards using the following statement

select * from pg_shard;

_

The IDs corresponding to the two shards are 1 and 2, respectively.

Create an account table bank_accounts, with the storage engine of both default two partitions being InnoDB

create table bank_accounts
(
   id         INT NOT NULL AUTO_INCREMENT,
   balance    DECIMAL(18,2) NOT NULL,
   primary key(id)
) partition by range(id);
create table bank_accounts_p0 partition of bank_accounts 
for values from (1) to (501) with (shard=1);
create table bank_accounts_p1 partition of bank_accounts
for values from (501) to (1001) with (shard=2);

Insert 1000 account records

create or replace procedure generate_account_data()
AS $$
DECLARE
  v_balance double;
  i integer = 1;
BEGIN
    while i<=1000 loop
        v_balance = ROUND(1000+RANDOM()*9000,2);
        INSERT INTO bank_accounts VALUES (i,v_balance);
        commit;
        i = i+1;
    end loop;
END; $$
LANGUAGE plpgsql;
call generate_account_data();
analyze bank_accounts;

After importing the data, the data is evenly distributed across the two shards.

Create the Python program paccupdate.py as follows:

import psycopg2.extras
from psycopg2 import DatabaseError
import time
import random
import os
from multiprocessing import Pool

def db_work(num):
	print("----开始执行任务%d----" % (num)) 
	conn = psycopg2.connect(database='test_db',user='kunlun_test',
                 password='kunlun',host='10.37.129.6',port='47001')
	update_sql1 = ''' update bank_accounts set balance=balance-100 where id=%s'''
	update_sql2 = ''' update bank_accounts set balance=balance+100 where id=%s'''
	cursor = conn.cursor()

try:
		for i in range(1000):
			id = random.randint((num-1)*50+1,(num-1)*50+50)
			print(f"减少的id为：{id}")
			cursor.execute(update_sql1, [id])
			id = random.randint(1000-num*50+1,1000-num*50+50)
			print(f"增加的id为：{id}")
			cursor.execute(update_sql2, [id])
			conn.commit()
			time.sleep(1)
	finally:
		cursor.close()
		conn.close()
		print("----任务%d执行完毕----" % (num)) 

    	

def main():
	po = Pool(4)
	for i in range(1,5):
		po.apply_async(db_work,(i,))
	print("----开始----")
	po.close()
	po.join()
	print("----结束----")

if __name__ == "__main__":
	main()
  1. Created 4 threads
  2. Each thread executes a transfer transaction once per loop, randomly selects an account from one shard and reduces its account balance by 100; then randomly selects an account from another shard and increases its account balance by 100.
  3. Each thread performs 1000 transfer transactions, sleeping for 1 second between each loop.

3.2 Global MVCC testing involves one or two shards, and the storage engine is InnoDB

The total balance of all accounts is 5,505,457.37

Run the paccupdate.py program and start 4 threads to perform transfer operations on user accounts simultaneously.

At the same time, run another program qsum.py to query the account total in real time

import psycopg2.extras
from psycopg2 import DatabaseError
import time
from decimal import Decimal

conn = psycopg2.connect(database='test_db',user='kunlun_test',
                 password='kunlun',host='10.37.129.6',port='47001')
select_sql = ''' select sum(balance) from bank_accounts;'''
cursor = conn.cursor()


try:
	for i in range(1000):
		cursor.execute(select_sql)
		res = cursor.fetchall()
		for row in res:
			balance = row[0].quantize(Decimal('0.01'))
			print(f"账户总额是{balance}")
		time.sleep(1)


finally:
	cursor.close()
	conn.close()

Query the total balance of all accounts during the execution of paccupdate.py

It can be seen that the total balance remains unchanged throughout the program's execution.

3.3 Global MVCC tests one or two shards, with the storage engine being RocksDB

Create a new account table called bank_accounts_rocksdb, with the storage engine of both shards being

create table bank_accounts_rocksdb
(
   id         INT NOT NULL AUTO_INCREMENT,
   balance    DECIMAL(18,2) NOT NULL,
   primary key(id)
) partition by range(id);
create table bank_accounts_rs_p0 partition of bank_accounts_rocksdb
for values from (1) to (501) with (shard=1,engine=rocksdb);
create table bank_accounts_rs_p1 partition of bank_accounts_rocksdb
for values from (501) to (1001) with (shard=2,engine=rocksdb);

insert into bank_accounts_rocksdb select * from bank_accounts;
analyze bank_accounts_rocksdb;

Refer to test steps 2.2, change the target table of the Python program to bank_accounts_rocksdb. Run four processes to simulate transfer operations.

At the same time, in another session, loop through to query the total balance of the account. In qsumrdb.py, the table used to calculate the sum was changed to bank_accounts_rocksdb.

When the storage engine of the account table shard is changed to RocksDB, Global MVCC ensures that the balances of all accounts remain unchanged during multi-user transfers.

3.4 Testing of Global MVCC with one or two shard storage engines, one being InnoDB and the other being RocksDB

Create the table bank_accounts_mixed, with the storage engines for the two shards being InnoDB and RocksDB, respectively.

create table bank_accounts_mixed
(
   id         INT NOT NULL AUTO_INCREMENT,
   balance    DECIMAL(18,2) NOT NULL,
   primary key(id)
) partition by range(id);
create table bank_accounts_mixed_p0 partition of bank_accounts_mixed
for values from (1) to (501) with (shard=1);
create table bank_accounts_mixed_p1 partition of bank_accounts_mixed
for values from (501) to (1001) with (shard=2,engine=rocksdb);

insert into bank_accounts_mixed select * from bank_accounts;
analyze bank_accounts_rocksdb;

Refer to test steps 2.2, change the target table corresponding to the Python program to bank_accounts_mixed. Run four processes to simulate transfer operations.

At the same time, in another session, loop through to query the total balance of the accounts. In qsummix.py, the table used to calculate the sum is changed to bank_accounts_mixed.

Similarly, when the storage engines of the two shards of the account table are changed to InnoDB and RocksDB respectively, Global MVCC ensures that the balances of all accounts remain unchanged during multi-user transfers.

END

Copyright © KlustronDB
Copyright © 2026 KlustronDB