8.1 Transaction Handling — MySQL Connectors 1.0

8.1 Обработка транзакций

Транзакции могут использоваться для группирования операций в атомную единицу. Все операции в рамках транзакции либо завершаются успешно при подтверждении, либо ни одна. Возможен откат транзакции до ее подтверждения.

Транзакции могут быть начаты в сессии с помощью метода startTransaction(), подтверждены с помощью commitTransaction() и отменены или откатаны с помощью rollbackTransaction(). Это проиллюстрировано в следующем примере. Пример предполагает, что схема test существует, а коллекция my_collection не существует.

Код MySQL Shell на JavaScript

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

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#

// 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

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

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++

// 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;
}