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?

Answer 1

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.