Each statement within SurrealDB is run within its own transaction by default. If a set of changes need to be made together, then groups of statements can be run together as a single transaction. If all of the statements within a transaction succeed, and the transaction is successful, then all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be cancelled or rolled back, then any data modification made within the transaction is rolled back, and will not become a permanent part of the database.
Starting a transaction
The BEGIN or BEGIN TRANSACTION statement starts a transaction in which multiple statements can be run together.
BEGIN [ TRANSACTION ];The following query shows example usage of this statement.
-- 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;
-- Start a new database transaction. Transactions are a way to ensure multiple operations
-- either all succeed or all fail, maintaining data integrity.
BEGIN TRANSACTION;
-- 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;Committing a transaction
The COMMIT statement is used to commit the statements within a transaction, ensuring that all data modifications become a permanent part of the database.
COMMIT [ TRANSACTION ];The following query shows example usage of this statement.
-- Setup accounts
CREATE account:one SET balance = 135605.16;
CREATE account:two SET balance = 91031.31;
BEGIN TRANSACTION;
-- Move money
UPDATE account:one SET balance += 300.00;
UPDATE account:two SET balance -= 300.00;
-- Finalise all changes
COMMIT TRANSACTION;Cancelling a transaction
The CANCEL statement can be used to cancel the statements within a transaction, reverting or rolling back any data modification made within the transaction as a whole.
CANCEL [ TRANSACTION ];The following query shows example usage of this statement.
-- Setup accounts
CREATE account:one SET balance = 135605.16;
CREATE account:two SET balance = 91031.31;
BEGIN TRANSACTION;
-- Move money
UPDATE account:one SET balance += 300.00;
UPDATE account:two SET balance -= 300.00;
-- Rollback all changes
CANCEL TRANSACTION;THROW to conditionally cancel a transaction
While transactions are automatically rolled back if an error occurs in any of its statements, THROW can also be used to explicitly break out of a transaction at any point. THROW can be followed by any value which serves as the error message, usually a string.
CREATE account:one SET dollars = 100;
CREATE account:two SET dollars = 100;
LET $transfer_amount = 150;
BEGIN TRANSACTION;
UPDATE account:one SET dollars -= $transfer_amount;
UPDATE account:two SET dollars += $transfer_amount;
IF account:one.dollars < 0 {
THROW "Insufficient funds, would have $" + <string>account:one.dollars + " after transfer"
};
COMMIT TRANSACTION;
SELECT * FROM account;'An error occurred: Insufficient funds, would have $-50 after transfer'[
{
dollars: 50,
id: account:one
},
{
dollars: 150,
id: account:two
}
]Nested transactions and savepoints
A transaction cannot contain another transaction, and SurrealQL has no savepoints to roll back part of one. A BEGIN inside an open transaction aborts the whole outer transaction, so none of its changes are written.
BEGIN;
CREATE account:three SET dollars = 100;
BEGIN;
CREATE account:four SET dollars = 100;
COMMIT;
COMMIT;'Tried to start a transaction while another transaction was open'
'Cannot COMMIT: the transaction was aborted due to a nested BEGIN'A transaction always commits or cancels as a whole. To keep the earlier steps when a later one fails, put them in separate transactions that each commit on their own.
Transactions across several requests
A transaction written with BEGIN and COMMIT has to begin and end in the same query: one call to query() in an SDK, or one request to the HTTP /sql endpoint. When the query ends, a transaction that is still open is cancelled, and a COMMIT sent in a later request has no transaction to commit:
-- First request
BEGIN;
CREATE step:one;Missing COMMIT statement-- Second request
COMMIT;Invalid statement: Cannot COMMIT without starting a transactionTo keep a transaction open while an application runs several queries, for example between migration steps that each need a result from the one before, use a client-side transaction from the SDK. It is started and finished by the client, and every query sent through it runs in the same transaction until the client commits or cancels it. Client-side transactions work over a WebSocket connection and with an embedded database, but not over HTTP. See manual transactions in Rust and transactions in JavaScript.