Transactions

Savepoints

The SAVEPOINT statement sets a named transaction allowing parts of a transaction to be rolled back before COMMIT.

Get the collection object

Assuming the existence of test_schema.test_collection collection.

[{
    "_id": 1,
    "name": "Fred",
    "age": 21
}]

Get the collection object.

session = mysqlx.get_session("root:@localhost:33060")
schema = session.get_schema("test_schema")
collection = schema.get_collection("test_collection")

Set and rollback to a named transaction savepoint

A savepoint name can be provided to create a transaction savepoint, which can later be used to rollback.

A mysqlx.OperationalError will be raised if the savepoint name is an invalid string or if a nonexistent savepoint is being used in mysqlx.Session.rollback_to().

# Start transaction
session.start_transaction()

collection.add({"name": "Wilma", "age": 33}).execute()
assert(2 == collection.count())

# Create a savepoint
session.set_savepoint("sp")

collection.add({"name": "Barney", "age": 42}).execute()
assert(3 == collection.count())

# Rollback to a savepoint
session.rollback_to("sp")

assert(2 == collection.count())

# Commit all operations
session.commit()

Set and rollback to an unnamed transaction savepoint

If a savepoint name is not provided, mysqlx.Session.release_savepoint() will return a generated savepoint name.

# Start transaction
session.start_transaction()

collection.add({"name": "Wilma", "age": 33}).execute()
assert(2 == collection.count())

# Create a savepoint
savepoint = session.set_savepoint()

collection.add({"name": "Barney", "age": 42}).execute()
assert(3 == collection.count())

# Rollback to a savepoint
session.rollback_to(savepoint)

assert(2 == collection.count())

# Commit all operations
session.commit()

Releasing a transaction savepoint

A mysqlx.OperationalError will be raised if a nonexistent savepoint is being used in mysqlx.Session.release_savepoint().

# Start transaction
session.start_transaction()

collection.add({"name": "Wilma", "age": 33}).execute()
assert(2 == collection.count())

# Create a savepoint
session.set_savepoint("sp")

collection.add({"name": "Barney", "age": 42}).execute()
assert(3 == collection.count())

# Release a savepoint
session.release_savepoint("sp")

assert(3 == collection.count())

# Commit all operations
session.commit()