Transactions

GalaxDB is a transactional, analytical relational engine - not a vector store with a SQL veneer. Explicit transactions support snapshot isolation, read-your-writes, and savepoints.

BEGIN / COMMIT / ROLLBACK

SQL

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Expressions on the right-hand side of SET are evaluated per row against the current value - balance = balance - 100 reads the existing balance and subtracts, it does not overwrite with a literal.

SQL

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- something goes wrong, discard the transaction
ROLLBACK;

Snapshot Isolation & Read-Your-Writes

Each transaction sees a consistent snapshot of the database taken at BEGIN. Writes made earlier in the same transaction are visible to later reads in that transaction (read-your-writes), even before COMMIT.

SAVEPOINT / ROLLBACK TO / RELEASE

Savepoints let you roll back part of a transaction without discarding the whole thing:

SQL

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

SAVEPOINT before_fee;
UPDATE accounts SET balance = balance - 5 WHERE id = 1;

-- Undo just the fee, keep the transfer
ROLLBACK TO before_fee;

-- Or drop the savepoint once you no longer need it
RELEASE before_fee;

COMMIT;

Write-Write Conflicts

When two concurrent transactions modify the same row, the second transaction to commit detects the conflict and fails with SQLSTATE 40001. Retry the transaction from the client.

Python

import galaxdb

conn = galaxdb.connect("host=localhost port=5433 dbname=galaxdb sslmode=disable")

try:
    conn.execute("BEGIN")
    conn.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
    conn.execute("COMMIT")
except RuntimeError as e:
    # SQLSTATE 40001 - serialization failure, safe to retry
    print(f"Conflict, retrying: {e}")

Serializable Snapshot Isolation (v0.7)

Default snapshot isolation prevents dirty reads, non-repeatable reads, and phantom reads, but allows write-skew. Use BEGIN ISOLATION LEVEL SERIALIZABLE to enable the SSI certifier for a transaction, which aborts with SQLSTATE 40001 when write-skew is detected at commit time.

SQL

BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT id FROM oncall WHERE flag = 1;
UPDATE oncall SET flag = 0 WHERE id = 1;
COMMIT;
-- If another serializable transaction concurrently read and wrote
-- a conflicting row, one of them aborts with SQLSTATE 40001.

Only transactions opened with BEGIN ISOLATION LEVEL SERIALIZABLE are checked by the certifier. Transactions using plain BEGIN continue to use snapshot isolation and are unaffected.

Python

import galaxdb

conn = galaxdb.connect("host=localhost port=5433 dbname=galaxdb sslmode=disable")

for attempt in range(5):
    try:
        conn.execute("BEGIN ISOLATION LEVEL SERIALIZABLE")
        rows = conn.execute("SELECT COUNT(*) FROM oncall WHERE flag = 1")
        if rows[0]['count'] > 1:
            conn.execute("UPDATE oncall SET flag = 0 WHERE id = 1")
        conn.execute("COMMIT")
        break
    except RuntimeError as e:
        # SQLSTATE 40001 - serialization failure, safe to retry
        conn.execute("ROLLBACK")
        if attempt == 4:
            raise

Typed Errors

Arithmetic and constraint errors return typed SQLSTATE codes, not silent failures:

SQLSTATE	Condition
23505	Duplicate PRIMARY KEY - never a silent overwrite
22012	Division by zero
22003	Numeric overflow
42804	Type mismatch
40001	Write-write conflict (serialization failure)
42501	Insufficient privilege (RBAC - see Server docs)

Examples

Analytical query inside a read-only flow

SQL

SELECT d.name, COUNT(*), AVG(e.salary)
FROM employees e JOIN departments d ON e.dept = d.name
GROUP BY d.name
HAVING COUNT(*) > 1
ORDER BY AVG(e.salary) DESC
LIMIT 10;

Joins, aggregates, GROUP BY / HAVING, DISTINCT, and ORDER BY / LIMIT run on an embedded DataFusion engine. Single-table point reads, filtered scans, and vector search use the native fast path.

Python client transaction

Python

import galaxdb

db = galaxdb.Database("./data")

db.execute("BEGIN")
db.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
db.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
db.execute("COMMIT")

UPDATE & DELETE

Version Tags