Skip to main content

Row-level concurrency

Row-level concurrency in OrioleDB is as close as possible to regular PostgreSQL tables. However, there are still some differencies because tables in OrioleDB are index-organized.

Update of the primary key​

Update of primary key is typically rare and unusual situation in index-organized table. Unlike regular update, update of primary key is implemented as a sequence of delete and insert. Concurrent update and delete can't follow this update and may result in error. See the example below. Session 2 gets an error due to concurrent primary key update in session 1.

CREATE TABLE tbl
(
  id int4 primary key,
  value numeric NOT NULL
) USING orioledb;

INSERT INTO tbl VALUES (1, 0.0);
Session 1
> BEGIN;
> UPDATE tbl SET id = 2 WHERE id = 1;
UPDATE 1
Session 2
> BEGIN;
> UPDATE tbl SET value = value + 1 WHERE id = 1;
(waiting)
> COMMIT;
COMMIT
ERROR:  tuple to be locked has its primary key changed due to concurrent update
> ROLLBACK;
ROLLBACK

Following the update chain​

If some row was deleted and then new row with same primary key value is immediately inserted, then concurrent update or delete may consider the new row as a new version of the old row. See the example below. Session 1 deletes row and then inserts row with same primary key value. Session 2 were intended to update initial row, but finally updates the newly inserted row.

CREATE TABLE tbl
(
  id int4 primary key,
  value numeric NOT NULL
) USING orioledb;

INSERT INTO tbl VALUES (1, 0.0);
Session 1
> BEGIN;
> DELETE FROM tbl WHERE id = 1;
DELETE 1
> INSERT INTO tbl VALUES (1, 0.0);
INSERT 0 1
Session 2
> BEGIN;
> UPDATE tbl SET value = value + 1 WHERE id = 1;
(waiting)
> COMMIT;
COMMIT
UPDATE 1
> COMMIT;
COMMIT

Transaction ID Allocation and Heap Relations​

OrioleDB optimizes concurrency by utilizing virtual transaction IDs (Virtual XIDs) for operations confined exclusively to OrioleDB tables. This bypasses the overhead of allocating a full PostgreSQL Transaction ID (XID) and the associated Write-Ahead Logging (WAL) required for heap relations.

Modifying any PostgreSQL heap relation within an OrioleDB transaction forces the allocation of a full XID. This alters the concurrency path and negates the Virtual XID optimization.

Sequence Generation Overhead​

PostgreSQL sequences are backed by heap relations. Using nextval() modifies the sequence relation, triggering a full XID allocation. To preserve OrioleDB's optimized transaction mechanics:

  • Enable Sequence Caching: Configure sequences with a CACHE directive (e.g., CREATE SEQUENCE my_seq CACHE 100;).
  • Mechanism: Caching restricts heap modification (and full XID allocation) to the single nextval() call that fetches the cache block. Subsequent calls retrieve values from session memory.
  • Workload Impact: This optimization can be critical for workloads with high volumes of small transactions. In large transactions, the performance penalty of a single XID allocation is heavily amortized.