Tutorial: Session Management

Session Management

Transactions

Either you are working with relational tables or document-store collections, the MySQL Connector/Node.js provides support for wrapping DML operations inside a transaction.

const mysqlx = require('@mysql/xdevapi');

mysqlx.getSession('mysqlx://root@localhost:33060')
    .then(session => {
        return session
            .startTransaction()
            .then(() => {
                return session
                    .getSchema('testSchema')
                    .getCollection('testCollection)
                    .modify('name = "foo"')
                    .execute();
            })
            .then(() => {
                return session.commit();
            })
            .catch(err => {
                return session.rollback();
            });
    });

Savepoints

Savepoints can be used for rolling back portions of transactions.

Creating a Savepoint

A transaction savepoint can be created using the Session.setSavepoint() method. The promise returned on creating a savepoint is resolved to a string which is the name of the savepoint. If a name for the savepoint is not provided, the connector generates a savepoint name of the form connector-nodejs-{string} where {string} is 32 random alphanumeric characters, such as:

connector-nodejs-097D39590784A43511E7B89CD9FB1810

Creating a savepoint with a given name

const mysqlx = require('@mysql/xdevapi');

let session;

mysqlx.getSession('mysqlx://root@localhost:33060')
    .then(s => {
        session = s;
        return session.startTransaction();
    })
    .then(() => {
        return session.setSavepoint('sp');
    })
    .then(savepoint => {
        console.log(savepoint); // sp
    });

Note: A defined identifier is taken literally. Empty strings are not allowed even though they are allowed by the server.

Creating a savepoint with an auto-generated name

const mysqlx = require('@mysql/xdevapi');

let session;

mysqlx.getSession('mysqlx://root@localhost:33060')
    .then(s => {
        session = s;
        return session.startTransaction();
    })
    .then(() => {
        return session.setSavepoint();
    })
    .then(savepoint => {
        console.log(savepoint); // connector-nodejs-097D39590784A43511E7B89CD9FB1810
    });

Rolling back to an existing savepoint

The savepoint name which is either provided by the user or generated by the connector is used to rollback to an existing savepoint with the Session.rollbackTo() method.

If the savepoint name is not valid or the savepoint does not exist, the promise will be rejected.

const mysqlx = require('@mysql/xdevapi');

let session;
let docs = [];

mysqlx.getSession('mysqlx://root@localhost:33060')
    .then(s => {
        session = s;
        return session.startTransaction();
    })
    .then(() => {
        return session
            .getSchema('testSchema')
            .getCollection('testCollection')
            .add({ _id: '2', name: 'bar' })
            .execute();
    })
    .then(() => {
        return session.setSavepoint();
    })
    .then(savepoint => {
        return session
            .getSchema('testSchema')
            .getCollection('testCollection')
            .add({ _id: '3', name: 'baz' })
            .execute()
            .then(() => savepoint)
    })
    .then(savepoint => {
        return session.rollbackTo(savepoint);
    })
    .then(() => {
        return session.commit();
    })
    .then(() => {
        return session
            .getSchema('testSchema')
            .getCollection('testCollection')
            .find()
            .execute(doc => doc && docs.push(doc));
    })
    .then(() => {
        console.log(docs); // [{ _id: '2', name: 'bar' }]
    });

Releasing an existing savepoint

The savepoint name which is either provided by the user or generated by the connector is used to release to an existing savepoint via the Session.releaseSavepoint() method.

If the savepoint name is not valid or the savepoint does not exist, the promise will be rejected.

const mysqlx = require('@mysql/xdevapi');

let session;

mysqlx.getSession('mysqlx://root@localhost:33060')
    .then(s => {
        session = s;
        return session.startTransaction();
    })
    .then(() => {
        return session.setSavepoint('sp');
    })
    .then(() => {
        return session
            .getSchema('testSchema')
            .getCollection('testCollection')
            .add({ _id: '2', name: 'bar' })
            .execute();
    })
    .then(() => {
        return session.releaseSavepoint('sp');
    })

Releasing a nonexisting named savepoint

Attemping to release a nonexisting savepoint will result in a rejected Promise.

const mysqlx = require('@mysql/xdevapi');

let session;

mysqlx.getSession('mysqlx://root@localhost:33060')
    .then(s => {
        session = s;
        return session.startTransaction();
    })
    .then(() => {
        return session
            .getSchema('testSchema')
            .getCollection('testCollection')
            .add({ _id: '2', name: 'bar' })
            .execute();
    })
    .then(() => {
        return session.releaseSavepoint('sp');
    })
    .then(() => {
        console.log(err.message); // SAVEPOINT sp does not exist
    });

Rolling back to a nonexisting named savepoint

Similarly, attempting to rollback to a nonexisting savepoint will result in a rejected Promise.

const mysqlx = require('@mysql/xdevapi');

let session;

mysqlx.getSession('mysqlx://root@localhost:33060')
    .then(s => {
        session = s;
        return session.startTransaction();
    })
    .then(() => {
        return session
            .getSchema('testSchema')
            .getCollection('testCollection')
            .add({ _id: '2', name: 'bar' })
            .execute();
    })
    .then(() => {
        return session.releaseSavepoint('sp')
    })
    .catch((err) => {
        console.log(err.message); // SAVEPOINT sp does not exist
    });

Rolling back to a savepoint that has already been released

If the savepoint was released and a rollback is attempted to that savepoint, then the Promise will be rejected.

const mysqlx = require('@mysql/xdevapi');

let session;

mysqlx.getSession('mysqlx://root@localhost:33060')
    .then(s => {
        session = s;
        return session.startTransaction();
    })
    .then(() => {
        return session.setSavepoint('sp');
    })
    .then(() => {
        return session
            .getSchema('testSchema')
            .getCollection('testCollection')
            .add({ _id: '2', name: 'bar' })
            .execute();
    })
    .then(() => {
        return session.releaseSavepoint('sp')
    })
    .then(() => {
        return session.rollbackTo('sp');
    })
    .catch((err) => {
        console.log(err.message); // SAVEPOINT sp does not exist
    });

Savepoints in autocommit mode

The exact behavior of savepoints is defined by the server. This can lead to confusing behavior in combination with autocommit mode, which is enabled by default. Consider a snippet like the following with no explicit BEGIN, startTransaction() or similar call:

const mysqlx = require('@mysql/xdevapi');

mysqlx.getSession('mysqlx://root@localhost:33060')
    .then(session => {
        session.setSavepoint('fun');
    })
    .then(savepoint => {
        session.releaseSavepoint('fun');
    })
    .catch(err => {
        console.log(err.message); // SAVEPOINT fun does not exist
    });

In autocommit mode, the call to setSavepoint() will create a transaction, create a savepoint and then commit, which would erase the savepoint. In consequence, the call to releaseSavepoint() would throw an error SAVEPOINT `fun` does not exist. For the savepoint to survive, the user needs to start an explicit transaction block first.

Savepoints with DDL statements

An open transaction is automatically closed and committed on executing a DDL statement. Consider the following code:

const mysqlx = require('@mysql/xdevapi');

let session;
let savepoint;

mysqlx.getSession('mysqlx://root@localhost:33060')
    .then(s => {
        session = s;
        session.startTransaction();
    })
    .then(() => {
        session.setSavepoint('fun');
    })
    .then(sp => {
        savepoint = sp;
        session.sql('CREATE TABLE schema.table').execute();
    })
    .then(() => {
        session.releaseSavepoint(savepoint);
    })
    .catch(err => {
        console.log(err.message); // SAVEPOINT fun does not exist
    });

Since the transaction is closed, the savepoint also ceases to exist.