---
title: "Concurrency and Transactions"
---

# Concurrency and Transactions


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](https://www.heliosdb.com/docs/nano/guides/deployment_modes/) first if the words "embedded" and
"server" are not yet concrete.

---

## Summary

| | Embedded library API / REPL | PostgreSQL or MySQL wire connection |
|---|---|---|
| Concurrent reads | Yes | Yes |
| Single-statement autocommit writes | Yes | Yes |
| Explicit `BEGIN … COMMIT` | Yes — but **one at a time per process** | Yes — **independent per connection** |
| Independent overlapping transactions from different callers | **No** | Yes |
| Crash recovery of committed data | Yes | Yes |

---

## 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.

```bash
# 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. In older releases, 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: transactional writes now 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 on a release that predates this fix
(see the [changelog](https://www.heliosdb.com/docs/nano/releases/changelog/)), 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. One open issue in the current release: concurrent autocommit
`UPDATE … SET n = n + 1` on the same row can lose increments without raising
`40001` ([#50](https://github.com/HeliosDatabase/HeliosDB-Nano/issues/50)) —
serialise such counters in the application until it is fixed.

**Can a REST or MCP call end my embedded application's transaction?**
No. A statement executed without a session can no longer run
inside, commit, or roll back a transaction the embedding application opened; it
is refused with `No active transaction to commit`.

**Do embedded writes replicate?**
Yes, including transactional writes, subject to the
partially-applied-transaction caveat above.

---

## Related

- [Known Limitations](https://www.heliosdb.com/docs/nano/reference/compatibility/known-limitations/) — open issues and recent fixes
- [Deployment Modes](https://www.heliosdb.com/docs/nano/guides/deployment_modes/) — storage mode vs. access mode
- [Session Management API](https://www.heliosdb.com/docs/nano/api/session_management/) — internal session
  primitives (architecture note, not a supported public API)
- [Multi-User Transactions Architecture](https://www.heliosdb.com/docs/nano/architecture/multi_user_transactions/)
- [Migration Guide](https://www.heliosdb.com/docs/nano/guides/migration/) — the embedded → server transition