Synchronize multiple requests to database in NestJS

Question

in our NestJS application we are using TypeORM as ORM to work with db tables and typeorm-transactional-cls-hooked library.

now we have problem with synchronization of requests which are read and modifying database at same time.

Sample:

@Transactional()
async doMagicAndIncreaseCount (id) {
    const await { currentCount } = this.fooRepository.findOne(id)

    // do some stuff where I receive new count which I need add to current, for instance 10
    const newCount = currentCount + 10
    

    this.fooRepository.update(id, { currentCount: newCount })
}

When we executed this operation from the frontend multiple times at the same time, the final count is wrong. The first transaction read currentCount and then start computation, during computation started the second transaction, which read currentCount as well, and first transaction finish computation and save new currentCount, and then also second transaction finish and rewrite result of first transaction.

Our goal is to execute this operation on foo table only once at the time, and other requests should wait until.

I tried set SERIALIZABLE isolation level like this:

@Transactional({ isolationLevel: IsolationLevel.SERIALIZABLE })

which ensure that only one request is executed at time, but other requests failed with error. Can you please give me some advice how to solve that?

Maybe queues can solve this. But it's just an idea, I'm having problems with transactions too. — Henrique Ramos
– Henrique Ramos, Commented Mar 24, 2022 at 14:47
"do some stuff where..." What kind of stuff? Things that depend on currentCount? Things that depend on "id"? — jjanes
– jjanes, Commented Mar 24, 2022 at 16:12

Daniele Ricci · Accepted Answer · 2022-03-27 19:30:32Z

1

+200

I never used TypeORM and moreover you are hiding the DB engine you are using.

Anyway to achieve this target you need write locks.

The doMagicAndIncreaseCount pseudocode should be something like

BEGIN TRANSACTION
ACQUIRE WRITE LOCK ON id
READ id RECORD
do computation
SAVE RECORD
CLOSE TRANSACTION

Alternatively you have to use some operation which is natively atomic on the DB engine; ex. the INCR operation on Redis.

Edit:

Reading on TypeORM find documentation, I can suggest something like:

this.fooRepository.findOne({
    where: { id },
    lock: { mode: "pessimistic_write", version: 1 },
})

P.S. Looking at the tags of the question I would guess the used DB engine is PostgreSQL.

edited Mar 27, 2022 at 19:30

answered Mar 27, 2022 at 19:00

Daniele Ricci

15.9k1 gold badge32 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Synchronize multiple requests to database in NestJS

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related