---
title: Transactions
description: How BEGIN/COMMIT/ROLLBACK and SAVEPOINT work — an undo log guarded by a database-wide lock, not MVCC.
---

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

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

```sql
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.