8.1 Transaction Handling

8.1.1 Processing Warnings

8.1.2 Error 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.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;
}