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 copyvar 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 copyfrom 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 copyimport 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 copyimport 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; }