Transactions

ParticleDB provides full ACID transactions with Multi-Version Concurrency Control (MVCC). Readers never block writers, and writers never block readers.

Quick Start

BEGIN;

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

-- Verify the transfer
SELECT id, name, balance FROM accounts WHERE id IN (1, 2);

COMMIT;

If anything goes wrong, roll back all changes:

BEGIN;
DELETE FROM important_data WHERE created_at < '2024-01-01';
-- Changed my mind
ROLLBACK;
-- Nothing was deleted

---

Transaction Lifecycle

BEGIN

Start a new transaction. All subsequent statements run within this transaction until COMMIT or ROLLBACK.

BEGIN;
-- or
BEGIN TRANSACTION;

COMMIT

Persist all changes made during the transaction. Once committed, changes are durable and visible to other transactions.

COMMIT;
-- or
COMMIT TRANSACTION;

ROLLBACK

Discard all changes made during the transaction, restoring the database to its state at BEGIN.

ROLLBACK;
-- or
ROLLBACK TRANSACTION;

Auto-Commit

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

-- These are two separate transactions
INSERT INTO logs (message) VALUES ('event 1');  -- auto-committed
INSERT INTO logs (message) VALUES ('event 2');  -- auto-committed

---

Savepoints

Create named checkpoints within a transaction. You can roll back to a savepoint without aborting the entire transaction.

BEGIN;

INSERT INTO orders (id, customer_id, total) VALUES (1, 100, 250.00);
SAVEPOINT after_order;

INSERT INTO order_items (order_id, product, quantity) VALUES (1, 'Widget', 5);
-- Oops, wrong product
ROLLBACK TO SAVEPOINT after_order;

-- The order still exists, but the order_item was rolled back
INSERT INTO order_items (order_id, product, quantity) VALUES (1, 'Gadget', 3);

COMMIT;

RELEASE SAVEPOINT

Release a savepoint when it’s no longer needed. This does not commit changes — it simply removes the named checkpoint.

BEGIN;
SAVEPOINT sp1;
INSERT INTO logs (message) VALUES ('step 1');
RELEASE SAVEPOINT sp1;
-- sp1 can no longer be rolled back to, but changes are still part of the transaction
COMMIT;

---

Two-Phase Commit (2PC)

Two-phase commit enables distributed transactions that span multiple database instances or systems. A coordinator prepares all participants before issuing the final commit.

Phase 1: Prepare

BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
PREPARE TRANSACTION 'transfer_tx_001';
-- Transaction is now in "prepared" state
-- Connection can disconnect; the prepared transaction persists

Phase 2: Commit or Rollback

From any connection (including after reconnection):

-- If all participants are ready, commit
COMMIT PREPARED 'transfer_tx_001';

-- If any participant failed, roll back
ROLLBACK PREPARED 'transfer_tx_001';

2PC Guarantees

• A prepared transaction survives connection drops and server restarts
• The transaction ID must be unique across all active prepared transactions
• Prepared transactions hold their locks until committed or rolled back
• Use 2PC when coordinating writes across multiple ParticleDB instances or external systems

---

Isolation and Concurrency

MVCC (Multi-Version Concurrency Control)

ParticleDB uses MVCC to provide snapshot isolation:

• Each transaction sees a consistent snapshot of the database as of its start time
• Reads never block writes, and writes never block readers
• Write-write conflicts are detected and one transaction is rolled back

How MVCC Works

1. Read operations see only data committed before the transaction started
2. Write operations create new versions of rows
3. Concurrent writes to the same row cause a conflict — the second writer is rolled back
4. Committed data is visible to all subsequent transactions

-- Transaction A                    -- Transaction B
BEGIN;                              BEGIN;
SELECT balance FROM accounts
WHERE id = 1;  -- sees 1000
                                    UPDATE accounts SET balance = 900
                                    WHERE id = 1;
                                    COMMIT;
SELECT balance FROM accounts
WHERE id = 1;  -- still sees 1000
-- (snapshot isolation)
COMMIT;
-- Now sees 900

Write Conflicts

When two concurrent transactions modify the same row, one will be rolled back:

-- Transaction A                    -- Transaction B
BEGIN;                              BEGIN;
UPDATE accounts                     UPDATE accounts
SET balance = balance - 100         SET balance = balance - 50
WHERE id = 1;                       WHERE id = 1;
                                    -- Blocked or rolled back
COMMIT;                             -- (conflict detected)

---

Row-Level Locking

FOR UPDATE

Acquire an exclusive lock on selected rows. Other transactions cannot modify or lock these rows until the current transaction completes.

BEGIN;

-- Lock the account row so no other transaction can modify it
SELECT * FROM accounts WHERE id = 42 FOR UPDATE;

-- Safe to read and update without race conditions
UPDATE accounts SET balance = balance - 100 WHERE id = 42;

COMMIT;

FOR SHARE

Acquire a shared lock. Multiple transactions can hold FOR SHARE on the same row, but FOR UPDATE is blocked.

BEGIN;

-- Allow concurrent readers but prevent writers
SELECT * FROM products WHERE id = 1 FOR SHARE;

-- Read-only operations...
SELECT * FROM inventory WHERE product_id = 1;

COMMIT;

FOR NO KEY UPDATE / FOR KEY SHARE

Finer-grained lock modes:

-- Lock rows but allow updates that don't modify the key
SELECT * FROM orders WHERE id = 1 FOR NO KEY UPDATE;

-- Shared lock that's compatible with NO KEY UPDATE
SELECT * FROM orders WHERE id = 1 FOR KEY SHARE;

NOWAIT

Return an error immediately if the row is already locked, instead of waiting:

BEGIN;
SELECT * FROM accounts WHERE id = 42 FOR UPDATE NOWAIT;
-- ERROR if another transaction holds a lock on this row

SKIP LOCKED

Skip over rows that are currently locked by other transactions. This is the building block for job queue patterns:

-- Worker picks up the next available job
BEGIN;

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

-- Process the job...
UPDATE jobs SET status = 'processing' WHERE id = <selected_id>;

COMMIT;

Locking Specific Tables in Joins

BEGIN;

SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.id = 100
FOR UPDATE OF orders;
-- Only the orders row is locked; customers row is not

COMMIT;

---

Cursors

For processing large result sets without loading everything into memory:

BEGIN;

-- Declare a cursor
DECLARE order_cursor CURSOR FOR
    SELECT id, total FROM orders WHERE status = 'pending';

-- Fetch rows in batches
FETCH 100 FROM order_cursor;
FETCH 100 FROM order_cursor;

-- Fetch backwards
FETCH BACKWARD 10 FROM order_cursor;

-- Other fetch directions
FETCH FIRST FROM order_cursor;
FETCH LAST FROM order_cursor;
FETCH ALL FROM order_cursor;

-- Close the cursor
CLOSE order_cursor;

COMMIT;

---

Transaction Best Practices

1. Keep transactions short. Long-running transactions hold locks, delay garbage collection, and increase the chance of write conflicts.

2. Use auto-commit for independent reads. Don’t wrap read-only SELECT statements in explicit transactions unless you need snapshot consistency across multiple queries.

3. Handle conflicts with retry logic. When a write conflict occurs, catch the error and retry the entire transaction:

   for attempt in range(3):
       try:
           with db.transaction():
               db.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
               db.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
           break  # success
       except ConflictError:
           continue  # retry

4. Use SKIP LOCKED for work queues instead of application-level locking.

5. Prefer savepoints for partial rollback instead of aborting entire complex transactions.

---

WAL Sync Modes

ParticleDB supports three durability modes, configured at server start:

Mode	Durability	Performance	Use Case
sync	Full (fsync per entry)	Baseline	Production data you can’t lose
groupsync	Batched (fsync per group)	~3x faster	Default production mode
nosync	None (WAL disabled)	~5x faster	Benchmarks, ephemeral data, caching layers

# Start with grouped sync (recommended for production)
particledb start --wal-sync-mode groupsync

# Start with WAL disabled (maximum throughput)
particledb start --wal-sync-mode nosync