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

Savepoint syntax is not supported by the current PG wire server. Statements such as:

• SAVEPOINT name
• ROLLBACK TO SAVEPOINT name
• RELEASE SAVEPOINT name

currently return an error instead of creating nested rollback checkpoints.

If you need partial retry logic today, split the workflow into smaller explicit transactions instead of relying on 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;

FOR SHARE

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

1. Keep transactions short.
2. Retry write-conflict failures at the application layer.
3. Use SKIP LOCKED for worker-queue style consumers.
4. Break complex multi-step workflows into smaller transactions instead of depending on savepoints.
5. Pick --txn-mode deliberately; it changes correctness and throughput characteristics more than SQL text does.

Related Pages

• Transaction Engine
• Configuration
• PostgreSQL Wire Protocol