Documentation Home
X DevAPI User Guide
Download this Manual
PDF (US Ltr) - 1.4Mb
PDF (A4) - 1.4Mb


X DevAPI User Guide  /  Statement Execution  /  Transaction Handling

8.1 Transaction Handling

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

Press CTRL+C to copy
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

Press CTRL+C to copy
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

Press CTRL+C to copy
// 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

Press CTRL+C to copy
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

Press CTRL+C to copy
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

Press CTRL+C to copy
// 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; }