Support retry logic

[christianhuening]

Apparently sequelize-cockroachdb does not support the retry logic (aka Error 40001). When looking at the CRDB docs, the Transaction (with retry logic) bullet point dissappears from the menu, after selecting Use Sequelize.

Does sequelize-cockroachdb in fact support retry or can it be added?

[robert-s-lee]

Can we get code sample for retry that can be implemented in the app as a shorty term solution?

[christianhuening]

@robert-s-lee we really need an update here!

[awoods187]

We reached out to sequelize directly and they informed us that Sequelize doesn't support any transaction level retries. Instead, they only support query level retries (http://docs.sequelizejs.com/class/lib/sequelize.js~Sequelize.html#instance-method-query) see options.retry. To retry transaction they also recommend to retry whole block of transaction for some error codes like 40001, something like this (https://github.com/sequelize/sequelize/issues/8352#issuecomment-332126781)

[Nican]

I have written my own retry utility:

/**
 * Retries the transaction up to 3 times
 * Retries only occur when database signals that a retry is required
 * https://www.cockroachlabs.com/docs/v21.2/build-a-nodejs-app-with-cockroachdb.html
 */
export async function transactionWithRetry<T>(
  db: Sequelize,
  fn: (transaction: Transaction) => PromiseLike<T>,
): Promise<T> {
  const maxRetryCount = 3;

  for (let i = 0; i < maxRetryCount; i++) {
    try {
      // eslint-disable-next-line no-await-in-loop
      return await db.transaction(fn);
    } catch (error) {
      if (i < maxRetryCount - 1 && error instanceof DatabaseError) {
        const { original } = error;
        if (original instanceof PgDatabaseError && original.code === '40001') {
          // eslint-disable-next-line no-await-in-loop
          await delay(2 ** i * 100 + Math.random() * 100);
          continue;
        }
      }

      throw error;
    }
  }

  throw new UnreachableError();
}

And accompanying test:

describe('database utils', () => {
  const { db } = container;

  it('test transcation retries', async () => {
    let didComplete = false;
    let executionCount = 0;

    await transactionWithRetry(db, async (transaction) => {
      executionCount++;
      await db.query('SELECT now()', { transaction });

      if (executionCount === 1) {
        // Force CRDB to do a transaction retry
        await db.query("SELECT crdb_internal.force_retry('1s':::INTERVAL)", { transaction });
      }

      didComplete = true;
    });

    expect(didComplete).to.eq(true);
    expect(executionCount).to.eq(2);
  });
});

Hope this helps!