Transactions

ParticleDB supports explicit transactions with BEGIN, COMMIT, and ROLLBACK, plus two-phase commit with PREPARE TRANSACTION. The exact concurrency behavior depends on the server’s --txn-mode; see the Transaction Engine page for engine-level details.

Quick Start

BEGIN;

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

COMMIT;

If anything goes wrong:

BEGIN;
DELETE FROM important_data WHERE created_at < '2024-01-01';
ROLLBACK;

Core Statements

BEGIN

BEGIN;
BEGIN TRANSACTION;

COMMIT

COMMIT;
COMMIT TRANSACTION;

ROLLBACK

ROLLBACK;
ROLLBACK TRANSACTION;

Auto-Commit

Without an explicit BEGIN, each statement runs in its own auto-committed unit:

INSERT INTO logs (message) VALUES ('event 1');
INSERT INTO logs (message) VALUES ('event 2');

Savepoints

Savepoints are supported for partial rollback inside an open transaction:

SAVEPOINT name — capture the transaction’s current state under a name
ROLLBACK TO SAVEPOINT name — discard writes made after the named savepoint (and remove any savepoints established after it), keeping the transaction open
RELEASE SAVEPOINT name — forget the named marker without rolling back its writes

BEGIN;
INSERT INTO logs (message) VALUES ('event 1');
SAVEPOINT sp1;
INSERT INTO logs (message) VALUES ('event 2');
ROLLBACK TO SAVEPOINT sp1;   -- 'event 2' is discarded, 'event 1' remains
COMMIT;

Savepoints must be issued inside a transaction block (otherwise the server returns 25P01), and there is a per-transaction cap on the number of active savepoints.

Two-Phase Commit

Prepared transactions are implemented:

BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
PREPARE TRANSACTION 'transfer_tx_001';

Then later:

COMMIT PREPARED 'transfer_tx_001';
-- or
ROLLBACK PREPARED 'transfer_tx_001';

Useful properties of the current implementation:

prepared transaction IDs must be unique
prepared state survives connection loss
COMMIT PREPARED and ROLLBACK PREPARED are available from a later session

Row-Locking Clauses

The current server supports:

FOR UPDATE
FOR SHARE
NOWAIT
SKIP LOCKED

FOR UPDATE

BEGIN;
SELECT * FROM accounts WHERE id = 42 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 42;
COMMIT;

BEGIN;
SELECT * FROM products WHERE id = 1 FOR SHARE;
COMMIT;

NOWAIT

BEGIN;
SELECT * FROM accounts WHERE id = 42 FOR UPDATE NOWAIT;

SKIP LOCKED

BEGIN;

SELECT *
FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

COMMIT;

Unsupported Lock Variants

The PostgreSQL clauses below are not part of the current implementation:

FOR NO KEY UPDATE
FOR KEY SHARE

Isolation-Level Caveat

ParticleDB does not currently accept PostgreSQL SERIALIZABLE requests over the PG wire protocol. Clients that issue:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

or BEGIN ISOLATION LEVEL SERIALIZABLE receive an error telling them to use read committed.

Operationally, choose the engine’s behavior with --txn-mode at server start instead.

Best Practices

Keep transactions short.
Retry write-conflict failures at the application layer.
Use SKIP LOCKED for worker-queue style consumers.
SAVEPOINT, RELEASE SAVEPOINT, and ROLLBACK TO SAVEPOINT are supported for partial rollback within a transaction; still prefer short transactions where practical.
Pick --txn-mode deliberately; it changes correctness and throughput characteristics more than SQL text does.