Sequelize Transaction Rollback to Specific Savepoint

Question

I'm using managed transactions in my project and I have a case where I'd like to use a single transaction and rollback to a specific savepoint if it fails, but always commit in the end.

For example, I want to create an Event and try to run an action. If the action fails, I want to rollback any db activity that occurred during the action but keep the Event record and subsequently update it with the result.

const transaction = await sequelize.transaction();

const event = await Event.create({ ... }, { transaction });

// I want to mark the savepoint here

try {
  const action = await runAction(transaction);
  event.status = 'success';
  event.action_id = action.id;
} catch (err) {
  // This is where I want to rollback any changes since the savepoint
  event.status = 'failure';
  event.failure_message = err.message;
}

await event.save({ transaction });
await transaction.commit();

As far as I understand, I should be able to achieve this by creating a new transaction with the existing one, effectively marking a savepoint:

const transaction = await sequelize.transaction();

const event = await Event.create({ ... }, { transaction });

const savepoint = await sequelize.transaction({ transaction });

try {
  const action = await runAction(savepoint);
  ...
} catch (err) {
  await savepoint.rollback();
  ...
}

await event.save({ transaction });
await transaction.commit();

However this doesn't work, and all operations using the transaction within runAction are still committed. Is there any way to achieve this?

user_2855707 · Answer

I realized that I have a findOrCreate being called within runAction. Sequelize creates savepoints in findOrCreate when provided a transaction in the options.

When creating a new transaction and providing a transaction in the options, it automatically creates a savepoint as shown here. This savepoint is given an id (which is based on the index of the new savepoint on the parent) and a db query is executed to set the savepoint in the transaction. So when I ran sequelize.transaction({ transaction }); it created a savepoint with name "transaction-id-1". Later on when findOrCreate is called and provided the "savepoint" transaction, it creates a savepoint with the same name "transaction-id-1", effectively overwriting the previous savepoint.

So I should have been passing the parent transaction to runAction rather than savepoint:

const transaction = await sequelize.transaction();

const event = await Event.create({ ... }, { transaction });

const savepoint = await sequelize.transaction({ transaction });

try {
  // Pass parent transaction, rather savepoint transaction
  const action = await runAction(transaction);
  ...
} catch (err) {
  await savepoint.rollback();
  ...
}

await event.save({ transaction });
await transaction.commit();

Sequelize Transaction Rollback to Specific Savepoint

1 Answers