Documentation Home
X DevAPI User Guide
Download this Manual
PDF (US Ltr) - 1.0Mb
PDF (A4) - 1.0Mb
EPUB - 0.7Mb
HTML Download (TGZ) - 0.6Mb
HTML Download (Zip) - 0.7Mb


X DevAPI User Guide  /  Statement Execution  /  Transaction Handling

Beta Draft: 2017-03-17

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

var mysqlx = require('mysqlx');

// Connect to server
var session = mysqlx.getNodeSession( {
  host: 'localhost', port: 33060,
  dbUser: 'mike', dbPassword: 's3cr3t!' } );

// 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: 'Jack', age: 15, height: 1.76, weight: 69.4}).execute();
  myColl.add({name: 'Susanne', age: 24, height: 1.65}).execute();
  myColl.add({name: 'Mike', age: 39, height: 1.9, weight: 74.3}).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

import mysqlx

# Connect to server
session = mysqlx.get_node_session( {
        'host': 'localhost', 'port': 33060,
        'dbUser': 'mike', 'dbPassword': 's3cr3t!' } )

# Get the Schema test
db = session.get_schema('test')

# Create a new collection
myColl = db.create_collection('my_collection')

# Start a transaction
session.start_transaction()
try:
        myColl.add({'name': 'Jack', 'age': 15, 'height': 1.76, 'weight': 69.4}).execute()
        myColl.add({'name': 'Susanne', 'age': 24, 'height': 1.65}).execute()
        myColl.add({'name': 'Mike', 'age': 39, 'height': 1.9, 'weight': 74.3}).execute()

        # Commit the transaction if everything went well
        session.commit()

        print 'Data inserted successfully.'
except Exception, err:
        # Rollback the transaction in case of an error
        session.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=mike;password=s3cr3t!;");

  // 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 = "Jack", age = 15, height = 1.76, weight = 69.4}).Execute();
	myColl.Add(new { name = "Susanne", age = 24, height = 1.65}).Execute();
	myColl.Add(new { name = "Mike", age = 39, height = 1.9, weight = 74.3}).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);
  }
}

Java Code

import com.mysql.cj.api.xdevapi.*;
import com.mysql.cj.xdevapi.*;

// Connect to server
XSession mySession = new XSessionFactory().getSession("mysqlx://localhost:33060/test?user=mike&password=s3cr3t!");

Schema db = mySession.getSchema("test");

// Create a new collection
Collection myColl = db.createCollection("my_collection");

// Start a transaction
this.session.startTransaction();
try {
    myColl.add("{\"name\":\"Jack\", \"age\":15}", "{\"name\":\"Susanne\", \"age\":24}", "{\"name\":\"Mike\", \"age\":39}");

    this.session.commit();
    System.out.println("Data inserted successfully.");
} catch (Exception err) {
    // Rollback the transaction in case of an error
    this.session.rollback();

    // Printing the error message
    System.out.println("Data could not be inserted: " + err.getMessage());
} 

C++ Code

// Connect to server
XSession session(SessionSettings::HOST, "localhost",
                 SessionSettings::PORT, 33060,
                 SessionSettings::USER, "mike",
                 SessionSettings::PWD, "s3cr3t!");

// 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": "Jack", "age": 15, "height": 1.76, "weight": 69.4})").execute();
  myColl.add(R"({"name": "Susanne", "age": 24, "height": 1.65})").execute();
  myColl.add(R"({"name": "Mike", "age": 39, "height": 1.9, "weight": 74.3})").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;
}

User Comments
Sign Up Login You must be logged in to post a comment.