3

We have a function which has a Sequelize transaction. Within the transaction, it checks a field on a database table row, and based on that field, it either returns, or continues onto update various database tables.

For example

let buyingStatus = await models.buyingFood.findOne({ where: { id: 21 }, transaction: t});
if (buyingStatus.status == 'bought') return 'already bought'

If it continues, it updates multiple places, which should only update if the status isn't bought, then finally, changes the status to bought.

The problem is when the function is called twice, at the same time. The bottleneck is the updates that occur after the select statement. So two people can pass the buying status being not bought, then both update the bought status, along with all the other updates.

Not being an expert in SQL, we researched and found that locking the table, or changing the isolation level could fix this. However we implemented both and didnt help. See below

1) Changing Isolation level

let t = await models.sequelize.transaction(isolationLevel: Sequelize.Transaction.ISOLATION_LEVELS.SERIALIZABLE);

2) Setting lock on the find

let buyingStatus = await models.buyingFood.findOne({ where: { id: 21 }, transaction: t, lock: t.LOCK.UPDATE });

What we want, is to read from uncommitted transactions, so person B is unable commit if person A has changed the bought status before him

KittenKiller
  • 117
  • 9

1 Answers1

0

The reads are fine to run concurrently, it is the different updates that needs to use the same transaction that need to result in a non serializable conflict for an error to occur. So to be able to answer one would need more information, but if both calls result in the same end result, it will not result in a conflict.

Smolkis
  • 11
  • 2