The proper way to achieve database locks (Pessimistic Read and Write) with TypeORM and NodeJS

Question

I have an application that needs to deal with concurrency/race conditions. I noticed user account balances were being updated wrongly. Multiple requests can reach in at the same time and then the the balance data table is messed up.

For example, Request A and B comes in, Request B executes first and then Request A follows. However Request A has already read the the row and might be working with an old value that Request B has already updated. So balance is 5, request B gets the balance ( 5 ) and adds ( 3 ) to make 8 and updates the table. Since both requests came at the same time, request A has already read the balance 5 and will work from 5 instead of waiting and letting Request B finish first and then working with the new balance of 8.

I've tried several examples both blogs and stack overflow for almost a week now and I am totally drained. If anyone has worked or can provide assistance, kindly help me out here please.

I have been trying to replicate a situation where multiple code calls to the database want to modify the same value but they are all executing concurrently although I have specified a "pessimistic_write" lock on the row and other calls are supposed to wait. It seems nothing is working.

Code sample

require("dotenv").config();
const environment = process.env;

module.exports = async function UserAccount(connection, Entities) {
    console.log("==== STARTING =====");

    const queryRunner = connection.createQueryRunner();
    await queryRunner.connect();

    await queryRunner.startTransaction();

    /* const data = await queryRunner.query("SELECT * FROM balances");

    await queryRunner.release(); */

    queryRunner.manager
        .getRepository(Entities.Balances)
        .createQueryBuilder("balances")
        .useTransaction(true)
        .select()
        .setLock("pessimistic_write")
        .where("balances.id = :status", { status: 1 })
        .getOne()
        .then((result) => {
            console.log("= 2 ===================================");
            console.log(result);
            console.log("====================================");

            queryRunner.manager
                .getRepository(Entities.Balances)
                .createQueryBuilder("balanceX")
                .update()
                .set({ balance: `${Number(Math.random()) + Number(result.balance)}` })
                .where("balances.id = :status", { status: 1 })
                .execute()
                .then((result) => {
                    console.log("= 2A ===================================");
                    console.log(result);
                    console.log(Number(result.balance));
                    console.log("====================================");
                });
        });

    queryRunner.manager
        .getRepository(Entities.Balances)
        .createQueryBuilder("balances")
        .select()
        .useTransaction(true)
        .setLock("pessimistic_read")
        .where("balances.id = :status", { status: 1 })
        .getOne()
        .then((result) => {
            console.log("= 1 ==================================");
            console.log(result);
            console.log(Number(result.balance));
            console.log("====================================");
        });

    // queryRunner.commitTransaction();

    // queryRunner.startTransaction();

    // queryRunner.commitTransaction();

    // queryRunner.startTransaction();

    queryRunner.manager
        .getRepository(Entities.Balances)
        .createQueryBuilder("balances")
        .useTransaction(true)
        .select()
        .setLock("pessimistic_write")
        .where("balances.id = :status", { status: 1 })
        .getOne()
        .then((result) => {
            console.log("= 3 ===================================");
            console.log(result);
            console.log("====================================");

            queryRunner.manager
                .getRepository(Entities.Balances)
                .createQueryBuilder("balanceX")
                .update()
                .set({ balance: `${Number(Math.random()) + Number(result.balance)}` })
                .where("balances.id = :status", { status: 1 })
                .execute()
                .then((result) => {
                    console.log("= 3A ===================================");
                    console.log(result);
                    console.log(Number(result.balance));
                    console.log("====================================");
                });
        });

    queryRunner.commitTransaction();

    // await queryRunner.release();

    // await queryRunner.commitTransaction();
    // await queryRunner.release();

    // await queryRunner.startTransaction();

    /* try {
            // await queryRunner.manager.save({});
            await queryRunner.commitTransaction();
        } catch (err) {
            await queryRunner.rollbackTransaction();
        } finally {
            await queryRunner.release();
        } */
};

At this point, I am willing to accept any help. Reading and updating works fine. I am trying to implement lock and updating. Thanks in advance.