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.
API references
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.
Running a transaction
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"])80 70On 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.
Handling errors
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"])An error occurred: Insufficient funds
80 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().
Committing and cancelling yourself
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.
Retrying after a transaction conflict
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:
QueryError: There was a problem with the key-value store: Transaction conflict: Write conflict, retry the transaction. This transaction can be retriedTransactions in a session
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}) Sending statements in one request with batch()
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)An error occurred: Insufficient fundsAgainst 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 transaction | batch() | |
|---|---|---|
| Connection | WebSocket | Every connection type |
| Server | SurrealDB 3.0 or later | Every supported version |
| Code between operations | Yes: your code can read a result and then choose the next operation | No: you write every statement before the batch is sent |
| Requests | One for each operation, and one to commit | One |
Writing BEGIN and COMMIT in one query
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))4When 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.
What a transaction does not cover
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 aftercancel().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.
Low-level transaction ids
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.
Learn more
SurrealTransaction API reference for every method of a transaction
Executing queries for
batch()andquery()Multiple sessions for sessions
Error handling for the errors that a transaction raises
Transactions in SurrealQL for
BEGIN,COMMITandCANCEL