Transactions
DML operations can be wrapped by a transaction for both relational tables and NoSQL collections.
const mysqlx = require('@mysql/xdevapi');
mysqlx.getSession('mysqlx://root@localhost:33060/testSchema')
.then(session => {
return session.startTransaction()
.then(() => {
const collection = session.getDefaultSchema().getCollection('testCollection');
return collection.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 when creating a savepoint is resolved to a string which is the name of the savepoint. If a name for the savepoint is not provided, one is generated in the form connector-nodejs-{string} where {string} is 32 random alphanumeric characters, such as:
connector-nodejs-097D39590784A43511E7B89CD9FB1810
const mysqlx = require('@mysql/xdevapi');
mysqlx.getSession('mysqlx://root@localhost:33060')
.then(session => {
return session.startTransaction();
.then(() => {
// providing a name for the savepoint
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.
const mysqlx = require('@mysql/xdevapi');
mysqlx.getSession('mysqlx://root@localhost:33060')
.then(session => {
return session.startTransaction();
.then(() => {
// using an auto-generated name for the savepoint
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');
mysqlx.getSession('mysqlx://root@localhost:33060')
.then(session => {
const collection = session.getSchema('testSchema').getCollection('testCollection');
return session.startTransaction()
.then(() => {
return collection.add({ _id: '2', name: 'bar' })
.execute();
})
.then(() => {
return session.setSavepoint();
})
.then(savepoint => {
return collection.add({ _id: '3', name: 'baz' })
.execute()
.then(() => {
return session.rollbackTo(savepoint);
});
})
.then(() => {
return session.commit();
})
.then(() => {
return collection.find()
.execute();
})
.then(res => {
console.log(res.fetchAll()); // [{ _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');
mysqlx.getSession('mysqlx://root@localhost:33060')
.then(session => {
const collection = session.getSchema('testSchema').getCollection('testCollection');
return session.startTransaction()
.then(() => {
return session.setSavepoint('sp');
})
.then(() => {
return collection.add({ _id: '2', name: 'bar' })
.execute();
})
.then(() => {
return session.releaseSavepoint('sp');
});
});
Attemping to release a nonexisting savepoint will result in a rejected Promise.
const mysqlx = require('@mysql/xdevapi');
mysqlx.getSession('mysqlx://root@localhost:33060')
.then(session => {
const collection = session.getSchema('testSchema').getCollection('testCollection');
return session.startTransaction()
.then(() => {
return collection.add({ _id: '2', name: 'bar' })
.execute();
})
.then(() => {
return session.releaseSavepoint('sp');
});
})
.catch(err => {
console.log(err.message); // SAVEPOINT sp does not exist
});
Similarly, attempting to rollback to a nonexisting savepoint will result in a rejected Promise.
const mysqlx = require('@mysql/xdevapi');
mysqlx.getSession('mysqlx://root@localhost:33060')
.then(session => {
const collection = session.getSchema('testSchema').getCollection('testCollection');
return session.startTransaction()
.then(() => {
return collection.add({ _id: '2', name: 'bar' })
.execute();
})
.then(() => {
return session.releaseSavepoint('sp');
});
})
.catch(err => {
console.log(err.message); // SAVEPOINT sp does not exist
});
If the savepoint was released and a rollback is attempted to that savepoint, then the Promise will be rejected.
const mysqlx = require('@mysql/xdevapi');
mysqlx.getSession('mysqlx://root@localhost:33060')
.then(session => {
const collection = session.getSchema('testSchema').getCollection('testCollection');
return session.startTransaction()
.then(() => {
return session.setSavepoint('sp');
})
.then(() => {
return collection.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 => {
return session.setSavepoint('fun');
})
.then(savepoint => {
return 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');
mysqlx.getSession('mysqlx://root@localhost:33060')
.then(session => {
return session.startTransaction()
.then(() => {
return session.setSavepoint('fun');
})
.then(savepoint => {
return session.sql('CREATE TABLE schema.table')
.execute()
.then(() => {
return 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.