Preventing Race Conditions in Sequelize with Transactions and Locks

Recently, I have stumbled upon a particularly pernicious issue with Sequelize when trying to access a Postgresql database in a concurrent application, such as a Node.js express server. This particular issue led to a nasty race condition in security-relevant code. Sequelize offers transactions and locks to make sure nothing phony happens in our application but these features are difficult to use and poorly documented. For that reason, I would like to provide you with a couple of insights and tricks to make your Sequelize-using applications more robust and free from race conditions.

A code sample for the issue I ran into is given below. The route accepts a phone number that shall be verified. We first check whether the phone number has already been submitted in the last 15 minutes to be verified. If that's the case, we increment the number of initiations so that the user cannot initiate the same email more than 10 times in a given time window. And if not, we insert a new entry into the database. In either case, we afterwards send the verification code by SMS and return the verification ID to the application.

This is all pretty standard code. But the problem is that if a user quickly submits multiple requests, he can get away with more than 10 initiations. This error is due to the fact that both requests may read the phoneVerification entries from the database before either of them manages to modify the entry or create a new one. This kind of bug is known as a serialization anomaly.

import { Op } from "sequelize";
import { v4 } from "uuid";

import sequelize from "db/connection";
import { PhoneAlreadyInitiatedError, TooManyPhoneVerificationInitiations } from "utils/error";
import generatePIN from "utils/generatePIN";

const importantRouteHandler = async (req, res, next) => {
  const { phone } = req.body;

  // Omitted: Perform checks that phone is valid

  const lastUpdatedAt = new Date(new Date().getTime() - 900000); // 15 minutes ago
  let phoneVerification;
  try {
    phoneVerification = await PhoneVerification.findAll({
      limit: 1,
      where: { phone, updatedAt: { [Op.gte]: lastUpdatedAt } },
    });
  } catch (err) {
    return next(err);
  }


  const code = generatePIN(config.EMAIL_VERIFY_PIN_LENGTH);
  let verificationID = v4();

  if (phoneVerification) {
    if (phoneVerification.initiations >= 10) return next(TooManyPhoneVerificationInitiations());

    phoneVerification.initiations = phoneVerification.initiations + 1;
    phoneVerification.code = code;


    try {
      await phoneVerification.save();
    } catch (err) {
      return next(err);
    }

    verificationID = phoneVerification.verificationID;
  } else {
    try {
      await PhoneVerification.create({ code, phone, verificationID });
    } catch (err) {
      return next(err);
    }
  }

  // Omitted: Send code by phone

  return res.json({ verificationID });
}

export default importantRouteHandler;

To overcome this issue, we would hope to use Sequelize's transaction and lock features, as shown in the next code sample. In most cases, using locks as provided by Sequelize works perfectly fine. But locks only work on rows that exist, and not on ones that we may want to create in the future. And since the table entry for the phone verification may not exist at the beginning of the route, we may not be able to lock it.

One way to overcome this problem would be to configure Sequelize to use SERIALIZABLE as isolation level. And while this works well, it will cause an exception each time the isolation is violated. This must be taken care of in the code and the query must be re-executed if an exception happens. Since this requires *a lot* of code changes and introduces all sorts of new challenges, this was not a viable solution for me.

import { Op } from "sequelize";
import { v4 } from "uuid";

import sequelize from "db/connection";
import { PhoneAlreadyInitiatedError, TooManyPhoneVerificationInitiations } from "utils/error";
import generatePIN from "utils/generatePIN";

const importantRouteHandler = async (req, res, next) => {
  const { phone } = req.body;

  // Omitted: Perform checks that phone is valid

  const transaction = await sequelize.transaction();

  const lastUpdatedAt = new Date(new Date().getTime() - 900000); // 15 minutes ago
  let phoneVerification;
  try {
    phoneVerification = await PhoneVerification.findAll({
      limit: 1,
      lock: true,
      transaction,
      where: { phone, updatedAt: { [Op.gte]: lastUpdatedAt } },
    });
  } catch (err) {
    await transaction.rollback();
    return next(err);
  }

  const code = generatePIN(config.EMAIL_VERIFY_PIN_LENGTH);
  let verificationID = v4();

  if (phoneVerification) {
    if (phoneVerification.initiations >= 10) {
      await transaction.rollback();
      return next(TooManyPhoneVerificationInitiations());
    }

    phoneVerification.initiations = phoneVerification.initiations + 1;
    phoneVerification.code = code;


    try {
      await phoneVerification.save({ transaction });
    } catch (err) {
      await transaction.rollback();
      return next(err);
    }

    verificationID = phoneVerification.verificationID;
  } else {
    try {
      await PhoneVerification.create({ code, phone, verificationID }, { transaction });
    } catch (err) {
      await transaction.rollback();
      return next(err);
    }
  }

  await transaction.commit();

  // Omitted: Send code by phone

  return res.json({ verificationID });
}

export default importantRouteHandler;

The second way, and the way I chose, is to simply lock the entire table. Now, Sequelize only allows you to perform row-level locks. That means you can only lock a certain row (and that row must already exist). But luckily, we can perform raw queries with Sequelize. This patch simply requires us to lock the table at the beginning of the transaction, and once the transaction is committed (or rolled back), the lock will be released. You can find the final, working code down below.

As a final word of warning, you should use table locks sparingly! They will slow down your application code since they lock the entire table from access and if you are not careful may even lead to deadlocks. In most cases, you will want to use row-level locks which are fully supported by Sequelize.

import { Op } from "sequelize";
import { v4 } from "uuid";

import sequelize from "db/connection";
import { PhoneAlreadyInitiatedError, TooManyPhoneVerificationInitiations } from "utils/error";
import generatePIN from "utils/generatePIN";

const importantRouteHandler = async (req, res, next) => {
  const { phone } = req.body;

  // Omitted: Perform checks that phone is valid

  const transaction = await sequelize.transaction();

  const lastUpdatedAt = new Date(new Date().getTime() - 900000); // 15 minutes ago
  let phoneVerification;
  try {
    await sequelize.query('LOCK TABLE "phoneVerifications" IN ACCESS EXCLUSIVE MODE', { transaction });
    phoneVerification = await PhoneVerification.findAll({
      transaction,
      where: { phone, updatedAt: { [Op.gte]: lastUpdatedAt } },
    });
  } catch (err) {
    await transaction.rollback();
    return next(err);
  }


  const code = generatePIN(config.EMAIL_VERIFY_PIN_LENGTH);
  let verificationID = v4();

  if (phoneVerification) {
    if (phoneVerification.initiations >= 10) {
      await transaction.rollback();
      return next(TooManyPhoneVerificationInitiations());
    }

    phoneVerification.initiations = phoneVerification.initiations + 1;
    phoneVerification.code = code;


    try {
      await phoneVerification.save({ transaction });
    } catch (err) {
      await transaction.rollback();
      return next(err);
    }

    verificationID = phoneVerification.verificationID;
  } else {
    try {
      await PhoneVerification.create({ code, phone, verificationID }, { transaction });
    } catch (err) {
      await transaction.rollback();
      return next(err);
    }
  }

  await transaction.commit();

  // Omitted: Send code by phone

  return res.json({ verificationID });
}

export default importantRouteHandler;