BEGIN

Each statement within SurrealDB is run within its own transaction by default. The BEGIN statement can be used to modify this behaviour by running a group of statements inside a single transaction, either succeeding as a whole, or failing. Once all of the statements within a transaction succeed, then all of the data modifications can be made permanent by finalizing the transaction with a COMMIT statement at the end. If any statement within a transaction encounters an error or the transaction is manually cancelled (CANCEL), then any data modification made within the transaction is rolled back, and will not become a permanent part of the database.

Transactions run under snapshot isolation. Concurrent transactions do not block each other, and when two of them write the same record or the same unique index value, the one that commits second fails with a transaction conflict error and can be retried. See Transactions for the full isolation rules, and Unique indexes under concurrent writes for inserting a record exactly once.

Statement syntax

BEGIN [ TRANSACTION ]
	[ ISOLATION LEVEL [ SNAPSHOT | SERIALIZABLE ] ]
	[ READ ONLY [ MAX STALENESS @duration [ STRICT ] ] ];

Example usage

The following query shows example usage of this statement.

-- Start a new database transaction. Transactions are a way to ensure multiple operations
-- either all succeed or all fail, maintaining data integrity.
BEGIN TRANSACTION;

-- Create a new account with the ID 'one' and set its initial balance to 135605.16
CREATE account:one SET balance = 135605.16;

-- Create another new account with the ID 'two' and set its initial balance to 91031.31
CREATE account:two SET balance = 91031.31;

-- Update the balance of account 'one' by adding 300.00 to the current balance.
-- This could represent a deposit or other form of credit on the balance property.
UPDATE account:one SET balance += 300.00;

-- Update the balance of account 'two' by subtracting 300.00 from the current balance.
-- This could represent a withdrawal or other form of debit on the balance property.
UPDATE account:two SET balance -= 300.00;

-- Finalise the transaction. This will apply the changes to the
-- database. If there was an error during any of the previous steps
-- within the transaction, all changes would be rolled back and the
-- database would remain in its initial state.
COMMIT TRANSACTION;

Returning early from a transaction

While all transactions require a final COMMIT or CANCEL statement in order to run, an early return can take place via the following:

• •

  An error inside one of the statements inside the transaction,

• •

  A THROW statement to return early with an error.

A RETURN statement is often used to customise the output of a transaction. From SurrealDB 4.0, a RETURN placed directly inside the transaction does not end it: its value is one of the results, and the statements after it still run. To end part of a transaction early, put those statements in a block, where a RETURN ends the block. Before SurrealDB 4.0, a RETURN ended the transaction early.

An example of the above:

BEGIN;

CREATE account:one SET balance = 135605.16;
CREATE account:two SET balance = 91031.31, wants_to_send_money = true;

-- Read the field into a parameter before testing it. As of SurrealDB
-- 3.2.x, reading a record field directly inside an IF condition fails
-- with "Specify a database to use".
LET $wants_to_send = account:two.wants_to_send_money;

IF !$wants_to_send {
    THROW "Customer doesn't want to send any money!";
};

LET $first = UPDATE ONLY account:one SET balance += 300.00;
LET $second = UPDATE ONLY account:two SET balance -= 300.00;

RETURN "Money sent! Status:\n" + <string>$first + '\n' +
  <string>$second;

COMMIT;

'Money sent! Status:
{ balance: 135905.16f, id: account:one }
{ balance: 90731.31f, id: account:two, wants_to_send_money: true }'

Read-only transactions

Available since: v4.0.0

BEGIN READ ONLY starts a transaction that can only read. Any write inside it fails, and the whole transaction fails with it:

CREATE note:1;

BEGIN READ ONLY;
SELECT * FROM note;
CREATE note:2;
COMMIT;

"Invalid statement: Cannot run CREATE on a read only transaction"

A read-only BEGIN returns a description of the snapshot it reads, where a plain BEGIN returns NONE. age is how far the snapshot is behind the current state, and stale is whether it is behind at all:

BEGIN READ ONLY;

{ age: 0ns, position: NONE, stale: false }

Reading a recent snapshot with MAX STALENESS

MAX STALENESS lets the transaction read from a snapshot that the datastore has already published, as long as the snapshot is no older than the duration given, instead of reading the current state. On a datastore that publishes snapshots, this lets reads that can accept slightly older data avoid the work of reading the latest state. The duration can be a literal or a parameter bound before the transaction:

BEGIN READ ONLY MAX STALENESS 5s;
SELECT count() FROM order GROUP ALL;
COMMIT;

SurrealDS publishes snapshots. A datastore that publishes none, which includes every storage engine of the open-source release, reads the current state, which meets every bound, so the same statement works everywhere. When no published snapshot is recent enough, the datastore reads the current state unless the server sets SURREAL_READ_SNAPSHOT_FALLBACK otherwise. Add STRICT to make the transaction fail instead of reading the current state in that case. A duration above the server's SURREAL_READ_SNAPSHOT_MAX_STALENESS_SECS, one hour by default, fails the transaction, and COMMIT reports the error:

BEGIN READ ONLY MAX STALENESS 2h;
COMMIT;

"Cannot COMMIT: The requested MAX STALENESS of 7200s is above the maximum of 3600s this datastore allows"

Clients can start the same transaction without writing BEGIN: the begin RPC method takes a read_only option, and the HTTP /sql endpoint takes read_only_max_staleness and read_only_strict.

Isolation level

Available since: v4.0.0

Transactions run under snapshot isolation by default, and ISOLATION LEVEL SNAPSHOT states this explicitly. ISOLATION LEVEL SERIALIZABLE asks for serializable isolation, which also prevents the anomalies that snapshot isolation allows, such as write skew, where two transactions each read what the other changes. The in-memory engine (SurrealMX) and SurrealDS offer serializable isolation, which they provide by adding every read of the transaction to its commit-time conflict check. SurrealKV and TiKV refuse it rather than running the transaction at a weaker level. See Isolation by storage engine for the full table, and Serializable isolation for an example of the write skew it prevents.

-- On the in-memory engine or SurrealDS
BEGIN ISOLATION LEVEL SERIALIZABLE;

On another engine, the statements inside the transaction fail with an error that names the levels the datastore offers:

"The isolation level SERIALIZABLE is not available on this datastore; it offers SNAPSHOT"

The two clauses can be combined in either order, as in BEGIN ISOLATION LEVEL SNAPSHOT READ ONLY. Combining SERIALIZABLE with a MAX STALENESS above zero fails with ISOLATION LEVEL SERIALIZABLE cannot be combined with a MAX STALENESS above zero.