Transactions

PizzaSQL supports BEGIN, COMMIT, ROLLBACK, SAVEPOINT, RELEASE, and ROLLBACK TO SAVEPOINT. The implementation is simple and worth understanding precisely, because its guarantees are weaker than PostgreSQL’s.

The model: undo log + global lock

A transaction is not multi-version concurrency control (MVCC). It is two things:

1. A database-wide exclusive lock taken at BEGIN and held until COMMIT or ROLLBACK. While a transaction is open, all other statements on that database (including reads) block.
2. An in-memory undo log. Each INSERT, UPDATE, or DELETE inside the transaction appends an entry recording the operation, the table, the primary key, and (for UPDATE/DELETE) the full previous row.

COMMIT simply discards the undo log and releases the lock — writes have already been applied to storage as the statements ran. ROLLBACK replays the undo log in reverse, restoring previous row state, then releases the lock.

Transaction control

BEGIN;                 -- or BEGIN TRANSACTION
UPDATE accounts SET balance = balance - 100 WHERE name = 'alice';
UPDATE accounts SET balance = balance + 100 WHERE name = 'bob';
COMMIT;

BEGIN;
INSERT INTO log VALUES (1);
SAVEPOINT sp1;
INSERT INTO log VALUES (2);
ROLLBACK TO sp1;       -- undoes the second insert
RELEASE sp1;
COMMIT;                -- keeps the first insert

• BEGIN inside an open transaction is an error.
• COMMIT/ROLLBACK with no open transaction is an error.
• SAVEPOINT outside a transaction implicitly opens one.
• ROLLBACK TO name undoes operations back to the savepoint and removes savepoints created after it.
• RELEASE name removes a savepoint; releasing the outer-most savepoint has no effect on the transaction.

Guarantees and limitations

• Rollback is session-level. For ordinary tables with a declared primary key, ROLLBACK restores row changes while the engine remains running. The undo log is in memory, so a process failure during an open transaction is not crash-atomic; writes already sent to storage can survive without the in-memory log that would undo them.
• Isolation is enforced by the exclusive lock — no other connection sees intermediate state, because they cannot run while the transaction is open. This is stronger than READ COMMITTED (readers block entirely) but means transactions are effectively serialized.
• DDL is not transactional. CREATE TABLE, DROP TABLE, ALTER TABLE, CREATE INDEX, and DROP INDEX are not recorded in the undo log. Rolling back a transaction does not undo DDL executed inside it.
• Durability of individual writes depends on the underlying key-value store’s log, not on the transaction layer. This does not make a multi-statement transaction atomic across an engine crash.

Failed transactions over the wire

On the PostgreSQL protocol, the connection tracks transaction state. If a statement inside a transaction block errors:

• The connection enters a “failed transaction” state and rejects further commands with SQLSTATE 25P02 until the client sends ROLLBACK (or ROLLBACK TO SAVEPOINT).
• This mirrors PostgreSQL’s aborted-transaction behaviour.

Managed service restrictions

• The managed HTTP query and execute endpoints reject transaction statements (BEGIN, COMMIT, ROLLBACK, SAVEPOINT, RELEASE) with 501 Not Implemented, and the /execute endpoint’s transaction: true flag is rejected too. Use the PostgreSQL wire protocol if you need transactions on the managed service.
• The managed PostgreSQL proxy forwards transaction statements to the engine, so transactions work normally over psql/drivers.
• The engine’s own raw HTTP API and CLI do support transaction statements, but those surfaces are internal to the platform.

Practical guidance

• Keep transactions short: they hold a database-wide lock and serialize all traffic on the database.
• Do not rely on rolling back DDL — migrate schema outside of transactions, or test-and-recover manually.
• Prefer the PostgreSQL connection path for anything that needs multi-statement atomicity on the managed service.