Skip to content
HeliosDB HeliosDB Nano

Concurrency and Transactions

Concurrency and Transactions

Applies to: HeliosDB Nano 4.31.1

What is safe to do from more than one caller depends entirely on how those callers reach the engine. Embedded (in-process) access and wire-protocol (server) access have different transaction models, and the difference is not a tuning knob — it is structural.

Read Deployment Modes first if the words “embedded” and “server” are not yet concrete.

Summary

Embedded library API / REPLPostgreSQL or MySQL wire connection
Concurrent readsYesYes
Single-statement autocommit writesYesYes
Explicit BEGIN … COMMITYes — but one at a time per processYes — independent per connection
Independent overlapping transactions from different callersNoYes
Crash recovery of committed dataYesYes

Embedded mode

What works

  • Concurrent reads. The storage engine is MVCC-based; readers take a snapshot and do not block each other or writers.
  • Autocommit single-statement writes. Each statement executed outside an explicit transaction commits on its own. Multiple threads issuing single-statement writes through the same handle is a supported pattern.

What does not work: independent explicit transactions

An EmbeddedDatabase handle holds one process-global transaction slot (current_transaction, guarded by a mutex, plus a global_txn_active flag). Cloning the handle does not give you a second slot — the clones share it.

The consequence: if two callers in the same process each run BEGIN … work … COMMIT against that handle, they are not in two isolated transactions. They are interleaving statements into one transaction, and whichever one commits or rolls back first ends it for both. A rollback in one caller discards the other caller’s uncommitted work.

This is not a locking bug to be worked around with a retry loop or a longer timeout. There is one transaction per process on the plain embedded API, by construction.

Supported alternatives

  1. Use separate PostgreSQL connections for independent sessions. Start the engine as a server (foreground or daemon) and give each caller its own connection — over TCP or a Unix-domain socket (--pg-socket-dir). This is the supported way to run independent transactions today, and it is what the wire protocol already does internally.

  2. If the application must share one embedded handle, coordinate around the entire transaction lifetime. The mutual exclusion has to span from BEGIN through the final COMMIT or ROLLBACK, including every error path that exits the block early. Serialising only the BEGIN, or only the COMMIT, does not help — the window that matters is the whole transaction. If a caller can hold that exclusion for a long time, that is an argument for moving to server access rather than for holding it anyway.

  3. Use single-statement autocommit where it is sufficient. A statement that is already atomic on its own (a single INSERT, UPDATE … WHERE, INSERT … ON CONFLICT DO UPDATE) needs no explicit transaction and is safe from concurrent callers.

Internal session primitives

The engine does contain lower-level per-session primitives — create_session, begin_transaction_for_session, execute_for_session, commit_transaction_for_session, rollback_transaction_for_session, destroy_session — and each session gets its own transaction with a fresh snapshot. These exist because the wire-protocol server needs them; they are internal plumbing, not a supported public embedded API. They are not documented as a feature, their shape is not covered by any compatibility promise, and they are not the recommended way to get independent transactions in embedded mode. Use a server connection instead.

A first-class public embedded session API is a roadmap item, not a current capability.

Server mode (PostgreSQL / MySQL wire)

Each client connection gets its own session with its own transaction state. Two connections can hold two overlapping explicit transactions, each with its own snapshot, exactly as clients expect from a PostgreSQL server.

Terminal window
# Terminal 1
psql -h 127.0.0.1 -p 5432
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;


# Terminal 2 — an independent transaction, not blocked by the above
psql -h 127.0.0.1 -p 5432
BEGIN;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;

This applies to every PostgreSQL-wire client — psql, psycopg2, asyncpg, JDBC, pgx, sqlx — and to the MySQL-wire listener (--mysql).

--max-connections (default 100) caps how many client connections the server will accept at once.

Isolation levels

  • READ COMMITTED — each statement sees a fresh snapshot of committed data.
  • REPEATABLE READ — one consistent snapshot taken at BEGIN. Concurrent writers to the same row are resolved first-committer-wins; the loser aborts with 40001 serialization_failure. Write skew is permitted. SNAPSHOT is an alias for this level.
  • READ UNCOMMITTED — mapped to READ COMMITTED; there are no dirty reads.

SERIALIZABLE is served as snapshot isolation and does not prevent write skew. The engine does not silently pretend otherwise: the behaviour is governed by storage.serializable_policy.

  • "warn" (default) — the transaction runs with snapshot isolation and a WARNING notice is sent to the client.
  • "error" — a request for SERIALIZABLE is rejected outright, leaving the session untouched.

If your correctness argument depends on true serializability, set the policy to "error" so the assumption fails loudly instead of quietly.

Durability and replication

Two different write-ahead logs are involved, and they answer different questions.

Local crash-recovery WAL. Always on. A committed transaction is recoverable after a crash, in every access mode, including writes made inside explicit transactions. This has never depended on replication being configured.

Logical WAL (replication / CDC). This is what feeds standbys and change-data consumers. Before engine v4.8.0, writes made inside an explicit or session transaction produced no logical-WAL records at all — local durability was fine, but those writes were never shipped downstream, so a primary and its standby diverged silently for anything an ORM wrapped in BEGIN … COMMIT. That is fixed: since v4.8.0 transactional writes reach the logical WAL, and synchronous/semi-synchronous commits actually wait for standby acknowledgement instead of reporting success for something they never sent.

Remaining limitation. A standby applies replicated operations one at a time, so it can transiently observe a partially-applied transaction before converging. The WAL format already carries transaction markers for atomic apply, but nothing emits them yet. Do not build a standby-side reader that assumes it will only ever see whole transactions.

If you have been replicating from a primary older than v4.8.0, the standby is missing every transactional write since it was seeded; upgrading fixes replication going forward but does not backfill. Re-seed any standby whose contents you need to trust.

Frequently asked

Can embedded threads run independent transactions? No, not on the plain embedded API. One process-global transaction slot per handle, shared across clones.

Is “database is locked” a tuning problem? No. It means a second process tried to open a data directory that another process already owns. One process per data directory; use server access if several callers need the same data.

Does a longer timeout fix concurrent embedded transactions? No. Timeouts govern how long a caller waits, not how many transaction slots exist.

Is multi-user access supported at all? Yes — over the wire. Each connection is an independent session with independent transaction state.

Do embedded writes replicate? Yes, including transactional writes, since v4.8.0 — subject to the partially-applied-transaction caveat above.