Skip to content

Concepts

Transactions

This page shows how to run several operations as one transaction from the Python SDK, so that all of them are applied or none of them are. A client-side transaction stays open on the server while your code runs operations in it, and it needs a WebSocket connection (ws:// or wss://) and SurrealDB 3.0 or later. For other connections and servers, batch() sends the statements as one transaction in one request.

Method

Description

db.begin_transaction()

Begins a transaction on the session of the connection

session.begin_transaction()

Begins a transaction on a session from

new_session()
txn.commit()

Applies the changes of the transaction and ends it

txn.cancel()

Discards the changes of the transaction and ends it

db.batch()

Sends statements as one transaction in one request, on every connection type

Against SurrealDB 2.x, begin_transaction() raises UnavailableFeatureError. HTTP connections have no begin_transaction() method, and embedded connections raise UnsupportedFeatureError. Use batch() on those.

db.begin_transaction() begins a transaction and returns a transaction object. The object has the query and CRUD methods of the connection, and each operation that you run through it is part of the transaction. Use the object as a context manager: the transaction commits when the block finishes, and it is cancelled when an exception leaves the block.

from surrealdb import RecordID, Surreal

with Surreal("ws://localhost:8000") as db:
    db.signin({"username": "root", "password": "secret"})
    db.use("surrealdb", "docs")

    db.create(RecordID("account", "tobie"), {"balance": 100})
    db.create(RecordID("account", "jaime"), {"balance": 50})

    with db.begin_transaction() as txn:
        txn.query("UPDATE account:tobie SET balance -= 20").execute()
        txn.query("UPDATE account:jaime SET balance += 20").execute()

    tobie = db.select(RecordID("account", "tobie")).execute()
    jaime = db.select(RecordID("account", "jaime")).execute()
    print(tobie["balance"], jaime["balance"])
Output
80 70

On an asynchronous connection, begin_transaction() is a coroutine, so the block starts with async with await. Until the transaction commits, its changes are visible to the operations of the transaction and not to any other operation, also not to the connection that began it.

When an exception leaves the block, the transaction is cancelled and the exception continues to your code. None of the changes in the block are applied:

from surrealdb import RecordID, ServerError

try:
    with db.begin_transaction() as txn:
        txn.query("UPDATE account:tobie SET balance -= 500").execute()
        txn.query(
            "IF account:tobie.balance < 0 { THROW 'Insufficient funds' }"
        ).execute()
except ServerError as e:
    print(e)

print(db.select(RecordID("account", "tobie")).execute()["balance"])
Output
An error occurred: Insufficient funds
80
Important

A statement that fails raises its error, but it does not end the transaction. If you catch the error inside the block, the transaction stays open and commits when the block finishes, with the changes of the statements that succeeded. To discard them, let the exception leave the block, or call txn.cancel().

Without a with block, call commit() to apply the changes, or cancel() to discard them. Cancel in an except block, so that an error does not leave the transaction open, and commit after the try block:

txn = db.begin_transaction()
try:
    txn.query("UPDATE account:tobie SET balance -= 20").execute()
    txn.query("UPDATE account:jaime SET balance += 20").execute()
except Exception:
    txn.cancel()
    raise
txn.commit()

Keep commit() out of the try block. A commit that fails has already ended the transaction, so a cancel() after it raises ValidationError: Transaction not found, and that error then hides the error of the commit.

After commit() or cancel(), the transaction has ended on the server, and every operation on the object raises ValidationError. Inside a with block, a call to commit() or cancel() ends the transaction, and the end of the block then does nothing more.

Two transactions that change the same record conflict. The server applies the one that commits first, and the commit of the other raises QueryError with its is_transaction_conflict property set to True. The transaction that failed changed nothing, so you can run it again. This function reads a balance and writes a new one, which conflicts when another client changes the same account at the same time:

from surrealdb import QueryError, RecordID

def withdraw(db, amount, attempts=3):
    account = RecordID("account", "tobie")
    for _ in range(attempts):
        try:
            with db.begin_transaction() as txn:
                balance = txn.select(account).execute()["balance"] - amount
                txn.update(account).merge({"balance": balance})
            return balance
        except QueryError as e:
            if not e.is_transaction_conflict:
                raise
    raise RuntimeError(f"withdraw failed after {attempts} attempts")

The try block holds the whole with block, because the commit at the end of the block is where the conflict is raised. The error message also says that the transaction can be retried:

Error output
QueryError: There was a problem with the key-value store: Transaction conflict: Write conflict, retry the transaction. This transaction can be retried

session.begin_transaction() begins a transaction on a session instead of the session of the connection. The transaction uses the namespace, the database and the authentication of that session:

session = db.new_session()
session.use("surrealdb", "docs")

with session.begin_transaction() as txn:
    txn.update(RecordID("account", "jaime")).merge({"active": True})

db.batch() collects statements and sends them in one request, wrapped in BEGIN TRANSACTION and COMMIT TRANSACTION. Nothing stays open on the server between requests, so it works where a client-side transaction does not: over HTTP, on embedded connections and against SurrealDB 2.x. When a statement fails, .execute() raises the error of that statement, and none of the statements are applied:

from surrealdb import ServerError

try:
    (
        db.batch()
        .query("UPDATE account:tobie SET balance -= 500")
        .query("IF account:tobie.balance < 0 { THROW 'Insufficient funds' }")
        .execute()
    )
except ServerError as e:
    print(e)
Output
An error occurred: Insufficient funds

Against SurrealDB 2.x, every server error is an InternalError, so catch ServerError in code that supports both versions. Executing queries describes the rules for the statements of a batch. The two kinds of transaction compare as follows:

Client-side transactionbatch()
ConnectionWebSocketEvery connection type
ServerSurrealDB 3.0 or laterEvery supported version
Code between operationsYes: your code can read a result and then choose the next operationNo: you write every statement before the batch is sent
RequestsOne for each operation, and one to commitOne

A single query() can hold its own BEGIN TRANSACTION and COMMIT TRANSACTION statements, on every connection type. Against SurrealDB 3.0 or later, BEGIN and COMMIT have results of their own, None, so the list from .execute() has an entry for each of them as well. Against SurrealDB 2.x, the list holds only the results of the statements between them.

results = db.query("""
    BEGIN TRANSACTION;
    UPDATE account:tobie SET balance -= 20;
    UPDATE account:jaime SET balance += 20;
    COMMIT TRANSACTION;
""").execute()

print(len(results))
Output
4

When a statement in such a query fails, the error that .execute() raises belongs to the first statement that the failure stopped, QueryError: The query was not executed due to a failed transaction, and not to the statement that caused the failure. batch() raises the error of the statement that failed, and returns results only for the statements that you added, so it is the simpler choice.

  • Files. txn.files.put() and the other file helpers of a transaction write at once. The file stays when the transaction is cancelled.

  • Session variables. txn.let() defines a variable on the session of the transaction. The variable stays after the transaction ends, also after cancel().

  • Streams. Inside a transaction, query() is never streamed. .rows() and iterating a builder still stream, so read the stream to the end before you commit.

db.begin() begins a transaction and returns its id, a uuid.UUID. Pass the id as the txn_id= keyword argument of a connection method to run it in the transaction, and end the transaction with db.commit(txn_id) or db.cancel(txn_id). Each of these methods also takes a session_id= keyword argument for a transaction on a session.

txn_id = db.begin()
db.query("UPDATE account:tobie SET balance -= 20", txn_id=txn_id).execute()
db.commit(txn_id)

The object from begin_transaction() passes these ids for you. Use the ids directly only where your code keeps the id instead of the object.

Was this page helpful?