Experience the hot row update capability of OceanBase Database

As online transactions and e-commerce continue to grow, business systems face increasing pressure from high-concurrency hotspot data access. Common examples include rapid balance updates on popular accounts and flash sales of hot products during marketing campaigns. The essence of hotspot row updates is the highly concurrent modification of certain field values (such as balance or inventory) in the same row of the database within a brief timeframe. The bottleneck is that, to maintain transaction consistency, a relational database must process each row update through a serial sequence: acquire lock → update → write log and commit → release lock. Therefore, the key to improving hotspot row update capability is to minimize lock hold time.

Although the "Early Lock Release" (ELR) technique was proposed in academia long ago, its complex exception-handling scenarios have resulted in few mature production implementations. OceanBase Database has addressed this issue through continuous exploration, proposing a distributed architecture-based implementation to enhance concurrent single-row update capability in similar business scenarios. ELR is a key capability within OceanBase Database's Scalable OLTP feature set.

In this article, we will demonstrate how to use the ELR feature of OceanBase Database and compare its performance through a highly concurrent single-row update scenario. Because the tests involve high-concurrency workloads, we recommend using node specifications at least equivalent to those in this example. The design and implementation details of OceanBase Database's ELR are beyond the scope of this article.

For this example, we use a node with a 16C-128GB configuration. The following steps walk you through the ELR feature of OceanBase Database.

Step 1: Create a test table and insert test data

First, create a table in the test database and insert test data.

CREATE TABLE `sbtest1` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `k` int(11) NOT NULL DEFAULT '0',
   `c` char(120) NOT NULL DEFAULT '',
   `pad` char(60) NOT NULL DEFAULT '',
   PRIMARY KEY (`id`)
);

INSERT INTO sbtest1 VALUES(1,0,'aa','aa');

In this example, we use a statement like UPDATE sbtest1 SET k=k+1 WHERE id=1 to perform concurrent updates on the k column through a primary key lookup. You can insert more data for testing, but since this is a stress test targeting a single row, it has little impact on the overall result.

Step 2: Construct a concurrent update scenario

In this example, we use Python multithreading to simulate concurrent updates. We start 50 threads simultaneously, each concurrently incrementing the value of the k field by 1 for the row with id=1. You can use the following script ob_elr.py in your own environment. Simply update the database connection information in the script.

#!/usr/bin/env python3

from concurrent.futures import ThreadPoolExecutor
import pymysql
import time
import threading

# database connection info
config = {
    'user': 'root@test',
    'password': '****',
    'host': 'xxx.xxx.xxx.xxx',
    'port': 2881,
    'database': 'test'
}

# parallel thread and updates in each thread
parallel = 50
batch_num = 2000

# update query
def update_elr():
    update_hot_row = ("update sbtest1 set k=k+1 where id=1")
    cnx = pymysql.connect(**config)
    cursor = cnx.cursor()

    for i in range(0,batch_num):
        cursor.execute(update_hot_row)
    cursor.close()
    cnx.close()

start=time.time()

with ThreadPoolExecutor(max_workers=parallel) as pool:
    for i in range(parallel):
        pool.submit(update_elr)

end = time.time()
elapse_time = round((end-start),2)

print('Parallel Degree:',parallel)
print('Total Updates:',parallel*batch_num)
print('Elapse Time:',elapse_time,'s')
print('TPS on Hot Row:' ,round(parallel*batch_num/elapse_time,2),'/s')

Step 3: Execute the test with default configurations

As a baseline, we first run the test with ELR disabled (the default setting). Execute the ob_elr.py script directly on the test machine.

In this example, we use 50 concurrent threads to perform a total of 100,000 updates.

./ob_elr.py

After execution, the test script outputs the execution time and TPS:

[root@obce00 ~]# ./ob_elr.py
Parallel Degree: 50
Total Updates: 100000
Elapse Time: 54.5 s
TPS on Hot Row: 1834.86 /s

The test result is as follows:

With ELR disabled under the default configuration, the TPS for concurrent single-row updates in this test environment is 1834.86/s.

Step 4: Enable the ELR configuration in OceanBase Database

Next, we enable the hot row update feature in OceanBase Database. First, log in to the sys tenant of the cluster as the root user.

[root@obce00 ~]# obclient -h127.0.0.1 -P2881 -uroot@sys  -Doceanbase -A -p -c

Then set the following two parameters. The enable_early_lock_release parameter can be set for a specific tenant or all tenants (tenant=all).

ALTER SYSTEM SET _max_elr_dependent_trx_count = 1000;
ALTER SYSTEM SET enable_early_lock_release=true tenant= test;

Step 5: Enable ELR in OceanBase Database for testing

After enabling the hot row update feature, we run the test again. Before execution, check the record with id=1 in table sbtest1. The value of the k field is 100000 because 100,000 updates were just performed under the default configuration.

SELECT * FROM sbtest1 WHERE id=1;
+----+--------+----+-----+
| id | k      | c  | pad |
+----+--------+----+-----+
|  1 | 100000 | aa | aa  |
+----+--------+----+-----+
1 row in set 

Next, execute ob_elr.py again on the test machine. Use the same 50 concurrent threads for a total of 100,000 updates.

./ob_elr.py

After execution, the test script outputs the execution time and TPS:

[root@obce00 ~]# ./ob_elr.py
Parallel Degree: 50
Total Updates: 100000
Elapse Time: 12.16 s
TPS on Hot Row: 8223.68 /s

The test results are as follows:

With ELR enabled, OceanBase Database achieves a single-row update TPS of 8223.68/s, approximately 3.5 times the default configuration.

We can also see that the k value in table sbtest1 is 200000, indicating that 100,000 updates were performed this time as well.

SELECT * FROM sbtest1 WHERE id=1;
+----+--------+----+-----+
| id | k      | c  | pad |
+----+--------+----+-----+
|  1 | 200000 | aa | aa  |
+----+--------+----+-----+
1 row in set 

This example covers only concurrent single-row updates. OceanBase Database's ELR capability also supports concurrent updates for multi-statement transactions. Significant performance improvements can be achieved depending on the number of statements and the scenario.

Furthermore, OceanBase Database's ELR can be applied in multi-region deployment scenarios with high network latency. For example, if a single transaction takes 30 ms by default, enabling ELR in a concurrent scenario can result in nearly a hundredfold increase in TPS.

Since OceanBase Database's log protocol is built on Multi-Paxos and optimizes the 2PC commit process, transaction consistency is still guaranteed after enabling ELR, even in cases of node failure and restart or Leader switchover. You can design these experiments to verify the behavior yourself.