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.

score 23 · Accepted Answer · edited Apr 05 '23 at 11:28

23

What you want is a Transaction that wraps your .findXX 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.

return User.findAll({
  limit: 1,
  lock: true, // <-- this does the trick
  transaction: t1
});

edited Apr 05 '23 at 11:28

Rafael Leite

147
1
1
13

answered Jan 17 '18 at 09:36

Chase

3,009
3
17
23

1

Thanks for this answer, can we have live example for this...it will be help full. – Piyush Dhamecha Jan 17 '18 at 14:37
@PiyushDhamecha have you find a working solution in the end? – Domen Grabec Mar 31 '21 at 15:52
none of the links work now and without them this answer is meaningless – morpheus Sep 17 '22 at 00:04
Here's the link to locks: https://sequelize.org/docs/v6/other-topics/transactions/#locks – Kuda Sep 29 '22 at 16:09

Andy Davison · Answer 2 · 2022-01-28T06:14:57.323

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:

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
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.
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!

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

2 Answers2

Linked