Serializable Snapshot Isolation

GalaxDB defaults to snapshot isolation with read-your-writes. For workloads that need stronger guarantees, v0.7 adds opt-in Serializable Snapshot Isolation (SSI) via a commit-time certifier that detects and aborts write-skew anomalies.

Default isolation

By default, every transaction runs under snapshot isolation:

• Reads see the committed state of the database at transaction start - later commits by concurrent transactions are invisible.
• Writes made earlier in the same transaction are visible to later reads in that transaction (read-your-writes), even before COMMIT.
• When two concurrent transactions write the same row, the second to commit detects the conflict and fails with SQLSTATE 40001 (write-write conflict).

Snapshot isolation prevents dirty reads, non-repeatable reads, and phantom reads. It does not prevent write-skew.

Write-skew

Write-skew occurs when two concurrent transactions each read a shared condition, then write to disjoint rows based on what they read, producing a state that neither transaction would have permitted had it seen the other's write.

The classic example is an on-call scheduling table:

-- Table: oncall(id INT PRIMARY KEY, flag INT)
-- Invariant: at least one row must have flag = 1

-- Transaction A reads: both rows have flag = 1
BEGIN;
SELECT COUNT(*) FROM oncall WHERE flag = 1;  -- returns 2
UPDATE oncall SET flag = 0 WHERE id = 1;
COMMIT;

-- Transaction B (concurrent) also reads: both rows have flag = 1
BEGIN;
SELECT COUNT(*) FROM oncall WHERE flag = 1;  -- returns 2
UPDATE oncall SET flag = 0 WHERE id = 2;
COMMIT;

-- After both commit: all flags are 0 - invariant violated
-- Under snapshot isolation both transactions succeed, because
-- they wrote to different rows (no write-write conflict).

Under snapshot isolation both transactions commit successfully because they modify different rows. The resulting state violates the invariant, but no conflict is detected.

Serializable mode

Use BEGIN ISOLATION LEVEL SERIALIZABLE to enable the SSI certifier for a transaction. At commit time, the certifier checks whether the transaction's reads and writes are compatible with a serial execution order. If not, it aborts with SQLSTATE 40001.

-- Transaction A
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT COUNT(*) FROM oncall WHERE flag = 1;
UPDATE oncall SET flag = 0 WHERE id = 1;
COMMIT;
-- If a concurrent serializable transaction read and wrote a
-- conflicting row, one of the two aborts with SQLSTATE 40001.

-- Transaction B (concurrent)
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT COUNT(*) FROM oncall WHERE flag = 1;
UPDATE oncall SET flag = 0 WHERE id = 2;
COMMIT;
-- One of these two transactions will abort; retry from the client.

The aborted transaction can be retried. On retry it sees the committed state from the other transaction and can decide whether the write is still needed.

Notes

• Conservative certifier - safe false-positive aborts are possible (the certifier may abort a transaction that would not actually have caused write-skew). False negatives - allowing write-skew to commit - are not possible.
• Default unchanged - existing workloads that use plain BEGIN see no change in behavior. SSI is strictly opt-in per transaction.
• SQLSTATE 40001 - the same error code used for write-write conflicts. Clients that already retry on 40001 handle SSI aborts correctly with no code change.
• Read-only transactions - a transaction that only reads and never writes always commits under SSI (a read-only transaction cannot participate in write-skew).

import galaxdb

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

# Retry loop for SSI transactions
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