Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

node js. Sequelize transactions

I have 'Banks' table with 'money' field in my database,

Users can withdraw money periodically, but they can withdraw only if there is money > 0 in the bank.

Firstly I should get the entity of bank, then check if(bank.money > amountToWithdraw) and then withdraw this amount.

Imagine the situation, when concurrent user try to withdraw some money. In that moment when I check if(bank.money > amountToWithdraw) other user can perform withdraw operation and the real bank.money amount in the DB will be less.

How to apply transaction to finding bank operation(how to lock bank entity)?

models.sequelize.transaction(function (t) {

return models.Banks.findOne({where: {
    money: {
      $gt: 0
    }
  }).then(function(bank){

    //in this moment other user finished the same operation
// how to lock access for editing bank object by other users after //findOne method?

    bank.money -= amountToWithdraw;
    return bank.save({transaction: t});
  })
})
like image 382
Igor Kasuan Avatar asked Nov 20 '16 11:11

Igor Kasuan


People also ask

What are transactions in Sequelize?

The transaction object is used to identify a running transaction. It is created by calling Sequelize. transaction() . To run a query under a transaction, you should pass the transaction in the options object.

Why do we use transactions in Sequelize?

Transaction is a way to execute or commit a group of operations as a unit. In other words, it is a technique to call multiple SQL statements as a single unit. In case of the transaction if any error occurred all the operations rollback. Sequelize provides a very easy way to implement transactions.

What is transaction in node JS?

A transaction is a unit of work, composed of a series of operations that you want either to succeed together, or fail together when one or more of the operations fail. This behavior is called atomicity.


1 Answers

You can use a lock on the row of the bank.

It can look something like this, depending on your database.

models.sequelize.transaction(function (t) {

return models.Banks.findOne({where: {
       money: {
         $gt: 0
       }
    }, lock: t.LOCK.UPDATE, transaction: t }).then(function(bank){

    bank.money -= amountToWithdraw;
    return bank.save({transaction: t});
  })
})
like image 129
drinchev Avatar answered Oct 17 '22 19:10

drinchev