X DevAPI User Guide
Transactions can be used to group operations into an atomic unit. Either all operations of a transaction succeed when they are committed, or none. It is possible to roll back a transaction as long as it has not been committed.
Transactions can be started in a session using the
startTransaction()
method, committed with
commitTransaction()
and cancelled or rolled
back with rollbackTransaction()
. This is
illustrated in the following example. The example assumes that the
test
schema exists and that the collection
my_collection
does not exist.
MySQL Shell JavaScript Code
var mysqlx = require('mysqlx');
// Connect to server
var session = mysqlx.getSession( {
host: 'localhost', port: 33060,
user: 'user', password: 'password
' } );
// Get the Schema test
var db = session.getSchema('test');
// Create a new collection
var myColl = db.createCollection('my_collection');
// Start a transaction
session.startTransaction();
try {
myColl.add({name: 'Rohit', age: 18, height: 1.76}).execute();
myColl.add({name: 'Misaki', age: 24, height: 1.65}).execute();
myColl.add({name: 'Leon', age: 39, height: 1.9}).execute();
// Commit the transaction if everything went well
session.commit();
print('Data inserted successfully.');
}
catch (err) {
// Rollback the transaction in case of an error
session.rollback();
// Printing the error message
print('Data could not be inserted: ' + err.message);
}
MySQL Shell Python Code
from mysqlsh import mysqlx
# Connect to server
mySession = mysqlx.get_session( {
'host': 'localhost', 'port': 33060,
'user': 'user', 'password': 'password
' } )
# Get the Schema test
myDb = mySession.get_schema('test')
# Create a new collection
myColl = myDb.create_collection('my_collection')
# Start a transaction
mySession.start_transaction()
try:
myColl.add({'name': 'Rohit', 'age': 18, 'height': 1.76}).execute()
myColl.add({'name': 'Misaki', 'age': 24, 'height': 1.65}).execute()
myColl.add({'name': 'Leon', 'age': 39, 'height': 1.9}).execute()
# Commit the transaction if everything went well
mySession.commit()
print('Data inserted successfully.')
except Exception as err:
# Rollback the transaction in case of an error
mySession.rollback()
# Printing the error message
print('Data could not be inserted: %s' % str(err))
C# Code
// Connect to server
var session = MySQLX.GetSession("server=localhost;port=33060;user=user;password=password
;");
// Get the Schema test
var db = session.GetSchema("test");
// Create a new collection
var myColl = db.CreateCollection("my_collection");
// Start a transaction
session.StartTransaction();
try
{
myColl.Add(new { name = "Rohit", age = 18, height = 1.76}).Execute();
myColl.Add(new { name = "Misaki", age = 24, height = 1.65}).Execute();
myColl.Add(new { name = "Leon", age = 39, height = 1.9}).Execute();
// Commit the transaction if everything went well
session.Commit();
Console.WriteLine("Data inserted successfully.");
}
catch(Exception err)
{
// Rollback the transaction in case of an error
session.Rollback();
// Printing the error message
Console.WriteLine("Data could not be inserted: " + err.Message);
}
Python Code
import mysqlx # Connect to server my_session = mysqlx.get_session({ 'host': 'localhost', 'port': 33060, 'user': 'user', 'password': 'password' }) # Get the Schema test my_schema = my_session.get_schema('test') # Create a new collection my_coll = my_schema.create_collection('my_collection') # Start a transaction session.start_transaction() try: my_coll.add({'name': 'Rohit', 'age': 18, 'height': 1.76}).execute() my_coll.add({'name': 'Misaki', 'age': 24, 'height': 1.65}).execute() my_coll.add({'name': 'Leon', 'age': 39, 'height': 1.9}).execute() # Commit the transaction if everything went well my_session.commit() print('Data inserted successfully.') except Exception as err: # Rollback the transaction in case of an error my_session.rollback() # Printing the error message print('Data could not be inserted: {0}'.format(str(err)))
Java Code
import com.mysql.cj.xdevapi.*; // Connect to server Session mySession = new SessionFactory().getSession("mysqlx://localhost:33060/test?user=user&password=password"); Schema db = mySession.getSchema("test"); // Create a new collection Collection myColl = db.createCollection("my_collection"); // Start a transaction mySession.startTransaction(); try { myColl.add("{\"name\":\"Rohit\", \"age\":18}", "{\"name\":\"Misaki\", \"age\":24}", "{\"name\":\"Leon\", \"age\":39}"); mySession.commit(); System.out.println("Data inserted successfully."); } catch (Exception err) { // Rollback the transaction in case of an error mySession.rollback(); // Printing the error message System.out.println("Data could not be inserted: " + err.getMessage()); }
C++ Code
// Connect to server Session session(SessionOption::HOST, "localhost", SessionOption::PORT, 33060, SessionOption::USER, "user", SessionOption::PWD, "password"); // Get the Schema test Schema db = session.getSchema("test"); // Create a new collection Collection myColl = db.createCollection("my_collection"); // Start a transaction session.startTransaction(); try { myColl.add(R"({"name": "Rohit", "age": 18, "height": 1.76})").execute(); myColl.add(R"({"name": "Misaki", "age": 24, "height": 1.65})").execute(); myColl.add(R"({"name": "Leon", "age": 39, "height": 1.9})").execute(); // Commit the transaction if everything went well session.commit(); cout << "Data inserted successfully." << endl; } catch (const Error &err) { // Rollback the transaction in case of an error session.rollback(); // Printing the error message cout << "Data could not be inserted: " << err << endl; }