Transaction Isolation

TransactionsACIDMVCCSnapshot Reads

Stable On by default · production-ready

What it is

AetheriusDB gives every transaction full ACID guarantees without the row-level locks that cause gridlock in traditional engines. Reads run against a consistent snapshot of the database taken when the query starts, so a long analytical scan always sees a single, frozen point in time. Writes are checked for conflicts at commit time: if another transaction has already modified the same data, your commit is rejected and rolled back — there is no waiting and no deadlock to detect.

The default behaviour is snapshot isolation for reads and serializable commits for writes. Together these prevent dirty reads, non-repeatable reads, and phantom reads automatically.

Why it matters

Lock-based engines force you to choose between correctness and throughput once you scale past a few hundred concurrent connections. AetheriusDB lets you keep both: long-running analytical scans coexist with high-frequency writes against the same table.

• Readers never block writers — analytical scans run against an immutable snapshot.
• No deadlocks — conflicts are detected at commit time and rolled back, not waited on.
• Clean rollback — an aborted transaction leaves no partial state to clean up.

flowchart LR
  classDef r fill:#1f2640,stroke:#00d4ff,color:#e8eaf0
  classDef w fill:#1f2640,stroke:#7c5cff,color:#e8eaf0
  classDef g fill:#1f2640,stroke:#4ade80,color:#4ade80

  R1[Reader]:::r --> SNAP[Consistent snapshot]:::r
  W1[Writer A]:::w --> COMMIT[Commit<br/>conflict check]:::g
  W2[Writer B]:::w --> COMMIT
  COMMIT -- no conflict --> OK[Committed]:::g
  COMMIT -- conflict --> ABORT[Rolled back]:::w

How you use it

Transactions look familiar — BEGIN, COMMIT, ROLLBACK. Each statement outside an explicit transaction is its own snapshot.

-- A normal transaction: snapshot reads + serializable commit.
BEGIN;
SELECT balance FROM accounts WHERE id = 42;
UPDATE accounts SET balance = balance - 100 WHERE id = 42;
UPDATE accounts SET balance = balance + 100 WHERE id = 99;
COMMIT;

If you want the strongest guarantee explicitly, set the level per transaction:

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ... reads and writes ...
COMMIT;

Handling serialization conflicts

If a concurrent transaction commits a conflicting change before yours lands, the server aborts your transaction and returns SQLSTATE 40001 (serialization failure). There is no partial state to clean up — just retry the whole transaction. Wrap write-heavy transactions in a retry loop:

import psycopg

while True:
    try:
        with conn.transaction():
            conn.execute(
                "UPDATE accounts SET balance = balance - 100 WHERE id = 42"
            )
            conn.execute(
                "UPDATE accounts SET balance = balance + 100 WHERE id = 99"
            )
        break  # committed
    except psycopg.errors.SerializationFailure:
        continue  # conflict — retry the whole transaction

Notes & caveats

Tip

For read-heavy analytical work you do not need an explicit BEGIN — each statement is its own snapshot, and they all see consistent data.

Retry on conflict

On a serialization conflict the engine rolls back and returns SQLSTATE 40001. Your driver should surface this; wrap commits in a retry loop for write-heavy workloads rather than treating it as a fatal error.

HTAP write-conflict behaviour is opt-in

When the mixed transactional + analytical (HTAP) mode is enabled with enable_htap = true, writes land in a fast mutable tier with snapshot isolation and write-conflict detection, and queries read both the mutable and the analytical tiers. HTAP is off by default — see the configuration matrix for the flag and its default.

Group Tables →

Update a parent group and have changes propagate to child tables atomically.

Scaling & Concurrency →

How concurrency holds up as data and node count grow.

Raft Coordination →

How isolation extends safely across multiple nodes.

Configuration & Availability →

Which features are on by default vs. opt-in.