SyndrDB provides full ACID transaction support with MVCC (Multi-Version Concurrency Control) snapshot isolation. Transactions ensure that your data modifications are atomic, consistent, isolated, and durable.
Key Concept: SyndrDB uses MVCC to allow readers and writers to operate concurrently without blocking each other. Each transaction sees a consistent snapshot of the database as of the time the transaction began.
Starts a new transaction. All subsequent commands execute within this transaction context until a COMMIT or ROLLBACK is issued.
BEGIN TRANSACTIONBEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READWhen no isolation level is specified, the default is REPEATABLE READ (snapshot isolation).
Note: Only one transaction can be active per session at a time. Attempting to start a new transaction while one is already active will return an error.
Commits the current transaction, making all changes permanent. All document-level write locks are released, commit sequences are updated, and the WAL is flushed.
COMMITOn commit:
Aborts the current transaction and undoes all changes made since BEGIN TRANSACTION. SyndrDB uses before-images to restore documents to their pre-transaction state.
ROLLBACKOn rollback:
Savepoints allow you to set a marker within a transaction that you can roll back to, without aborting the entire transaction.
SAVEPOINT "my_savepoint"ROLLBACK TO SAVEPOINT "my_savepoint"Rolling back to a savepoint undoes all changes made after the savepoint was created, but keeps the transaction active so you can continue with other operations.
Note: SyndrDB supports single-level savepoints. Creating a new savepoint replaces any existing savepoint within the same transaction.
BEGIN TRANSACTION
-- Insert first document
ADD DOCUMENT TO BUNDLE "orders" WITH ({"customer_id" = 100, "status" = "pending", "total" = 50.00})
-- Create a savepoint
SAVEPOINT "before_second_insert"
-- Insert second document
ADD DOCUMENT TO BUNDLE "orders" WITH ({"customer_id" = 200, "status" = "pending", "total" = 75.00})
-- Oops, undo the second insert
ROLLBACK TO SAVEPOINT "before_second_insert"
-- Only the first insert remains
COMMITSyndrDB supports four standard SQL isolation levels. The default is REPEATABLE READ, which provides MVCC snapshot isolation.
| Isolation Level | Dirty Reads | Non-Repeatable Reads | Phantom Reads | Notes |
|---|---|---|---|---|
| READ UNCOMMITTED | Prevented | Possible | Possible | Mapped to READ COMMITTED (PostgreSQL behavior) |
| READ COMMITTED | Prevented | Possible | Possible | Each statement sees latest committed data |
| REPEATABLE READ | Prevented | Prevented | Prevented | Default. MVCC snapshot isolation |
| SERIALIZABLE | Prevented | Prevented | Prevented | SSI (Serializable Snapshot Isolation) |
In SyndrDB, READ UNCOMMITTED is automatically upgraded to READ COMMITTED, following PostgreSQL's convention. Dirty reads are never permitted.
Each statement within the transaction sees data as of the moment that statement begins executing. Different statements may see different snapshots if other transactions commit in between.
The transaction sees a consistent snapshot of the database as of the time BEGIN TRANSACTION was executed. All reads within the transaction return the same data, regardless of concurrent commits by other transactions.
Uses Serializable Snapshot Isolation (SSI) to detect and prevent serialization anomalies. Transactions appear to execute one at a time, even though they actually run concurrently. If a read/write dependency cycle is detected, one of the transactions is aborted with the error:
could not serialize access due to read/write dependencies among transactionsTip: SERIALIZABLE provides the strongest consistency guarantees but may cause more transaction aborts under high contention. Use it when correctness is critical and you can handle retries.
Set the isolation level when starting a transaction:
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLEOr change it after beginning (before any data access):
BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL SERIALIZABLESet the default isolation level for all future transactions in the current session:
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLESHOW TRANSACTION ISOLATION LEVELReturns the current transaction's isolation level, or the session default if no transaction is active.
If a command fails during a transaction, SyndrDB automatically rolls back the failed operation. The transaction remains active, but the failed operation has no effect. This allows you to handle errors and continue with the transaction or explicitly roll back.
Important: Unlike some databases that put the transaction in an "aborted" state after any error, SyndrDB allows you to continue the transaction after a non-fatal error.
Server-side cursors are tied to the transaction in which they were declared. When a transaction ends (via COMMIT or ROLLBACK), all cursors opened within that transaction are automatically closed. This follows PostgreSQL semantics.
BEGIN TRANSACTION
-- Declare a cursor within the transaction
DECLARE my_cursor CURSOR FOR SELECT * FROM "large_dataset"
-- Fetch some rows
FETCH 100 FROM my_cursor
-- Committing closes the cursor
COMMIT
-- This would fail: cursor no longer exists
-- FETCH 100 FROM my_cursor-- Transfer funds between accounts
BEGIN TRANSACTION
UPDATE DOCUMENTS IN BUNDLE "accounts" (balance = balance - 100.00) CONFIRMED
WHERE account_id == 1001
UPDATE DOCUMENTS IN BUNDLE "accounts" (balance = balance + 100.00) CONFIRMED
WHERE account_id == 1002
ADD DOCUMENT TO BUNDLE "transactions" WITH ({
"from_account" = 1001,
"to_account" = 1002,
"amount" = 100.00,
"type" = "transfer"
})
COMMITBEGIN TRANSACTION
-- Create the order
ADD DOCUMENT TO BUNDLE "orders" WITH ({
"customer_id" = 500,
"status" = "pending",
"total" = 0.00
})
SAVEPOINT "before_items"
-- Add order items (might fail if product is out of stock)
ADD DOCUMENT TO BUNDLE "order_items" WITH ({
"order_id" = 1,
"product_id" = 42,
"quantity" = 2,
"price" = 29.99
})
-- If something goes wrong with items, roll back just the items
-- ROLLBACK TO SAVEPOINT "before_items"
-- Update order total
UPDATE DOCUMENTS IN BUNDLE "orders" (total = 59.98) CONFIRMED
WHERE customer_id == 500 AND status == "pending"
COMMIT-- Ensure no concurrent modifications can cause anomalies
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- Read current inventory
SELECT quantity FROM "inventory" WHERE product_id == 42
-- Check and update (SSI ensures this is safe from race conditions)
UPDATE DOCUMENTS IN BUNDLE "inventory" (quantity = quantity - 1) CONFIRMED
WHERE product_id == 42 AND quantity > 0
COMMIT
-- If another transaction modified the same data, this COMMIT may fail
-- with a serialization error. Retry the entire transaction if so.-- Set default isolation for all transactions in this session
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED
-- Check current setting
SHOW TRANSACTION ISOLATION LEVEL
-- This transaction will use READ COMMITTED
BEGIN TRANSACTION
SELECT * FROM "users" WHERE active == TRUE
COMMIT
-- Override for a specific transaction
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- This transaction uses SERIALIZABLE
COMMIT| Command | Description |
|---|---|
BEGIN TRANSACTION |
Start a new transaction (default: REPEATABLE READ) |
BEGIN TRANSACTION ISOLATION LEVEL <level> |
Start transaction with specific isolation |
COMMIT |
Commit and make all changes permanent |
ROLLBACK |
Abort and undo all changes |
SAVEPOINT "name" |
Create a savepoint within a transaction |
ROLLBACK TO SAVEPOINT "name" |
Roll back to a savepoint |
SET TRANSACTION ISOLATION LEVEL <level> |
Change isolation for current transaction |
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL <level> |
Set default isolation for session |
SHOW TRANSACTION ISOLATION LEVEL |
Display current isolation level |
Last updated: March 2026