How to lock table in sequelize, wait until another request to be complete

Question

Description:

I have one table in db say Table1. having only one column AppNo(numeric) and only single row, and current value is 0

I have created API in node.js using Sequelize ORM named UpdateAppNo.

Whenever UpdateAppNo api called value of AppNo should increment by 1.

What i want:

If 2 or more simultaneous request comes at a time, current request should wait until previous request to complete.

What happening now:

If previous request is in process, then current request throws an error.

Answer 1

This is old, but also the first google hit, so I'm posting this in case others have stumbled here.

As Chase said this is not at all well documented. Here's some code that will do what you want...

const User = db.User

const t = await sequelize.transaction(async(t) => {
  const user = await User.findByPk(userId, {lock: true, transaction: t})
  user.fieldToIncrement++
  await user.save({transaction:t})
})

Key points:

1. This code example is using the "Managed" transaction provided by Sequelize, so you don't need to manually manage commit/rollback, however the same will work with unmanaged transactions

2. You need to provide the lock option as well as the transaction, otherwise the transaction occurs without the lock you are looking for. Requests to update the table row while it's locked will balk until the lock is released.

3. Don't forget to include the transaction object in the options for any database actions inside the transaction context (see the call to user.save() ), otherwise your transaction will balk on itself...

I hope that's helpful to someone!

Answer 2

This is not very well documented or easy to find, but what you want is a Transaction that wraps your find query which uses a lock option to turn it into a SELECT FOR UPDATE statement.

The find query with the lock option will lock the row (you don't need to lock the entire table) and because you are using a transaction, the action will either be committed or rolled back depending on whether or not your instance holds the lock.

You will want to adapt the code found in these examples for your use.