Documentation / Transactions

Transactions

NusaDB uses multi-version rows and optimistic concurrency control. Readers work from a snapshot and never block writers; conflicting writers are told to retry rather than made to wait.

Isolation levels

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT balance FROM accounts WHERE id = 1;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
COMMIT;

Conflicts instead of waiting

Locks do not wait. When two transactions write the same row, one of them fails with SQLSTATE 40001 and the message says it must be retried. Nothing is lost and nothing hangs, but the losing transaction has to run again.

The trade is visible in both directions. Without contention this is faster than queuing, because no writer waits behind another. Under contention on one hot row it means retries, so an application that updates a shared counter needs to handle 40001.

The server retries a single auto-commit statement

A statement outside an explicit transaction has committed nothing and has shown the application no intermediate result, so the server re-runs it after a short randomised back-off instead of returning the conflict. This is what a correct client retry loop would do, done in the server.

Inside BEGIN … COMMIT the same retry would be wrong: a statement there may follow others whose results the application already acted on, and only the application knows whether the whole transaction can be replayed. So it is not retried, and the conflict is returned.

SET max_autocommit_retries = 50;  -- default; 0 turns the retry off

The budget is a bound, not a promise. A conflict that outlives it is reported unchanged, so an application's own retry loop still sees its 40001. Two things follow from a retry being a re-run: volatile expressions are evaluated again, so now() and random() produce fresh values and a sequence consumed by a failed attempt leaves a gap. Pair the retry budget with statement_timeout if you want an upper bound on how long one statement may occupy a connection.

Savepoints

A savepoint marks a point a transaction can roll back to without abandoning the whole transaction.

BEGIN;
INSERT INTO audit (event) VALUES ('import started');
SAVEPOINT before_rows;

INSERT INTO rows_in (payload) VALUES ('…');
ROLLBACK TO SAVEPOINT before_rows;   -- audit row survives

COMMIT;

An error aborts the whole transaction

After a failed statement the transaction is in an aborted state and further statements are refused until it ends. Rows written earlier in the transaction do not leak: either everything commits or nothing does. Use a savepoint when you want to recover from an expected failure without discarding earlier work.

How versions are kept

Each row version records the transaction that created it and, once removed, the transaction that removed it. A reader compares those against its own snapshot, which is why it never has to wait for a writer. Versions no live snapshot can still reach are removed by a background worker, so the version store does not grow without bound under sustained updates.

One consequence is worth knowing when sizing a deployment: deleting rows frees pages for reuse but does not reduce the resident memory the engine reports, because page memory is recycled rather than returned. See limits and capacity.

Explicit locking

SELECT … FOR UPDATE and FOR SHARE are available, including SKIP LOCKED, and so is LOCK TABLE. NOWAIT is not accepted: because locks already do not wait, a conflicting request fails immediately, which is what NOWAIT asks for.