TypeORM Database Lock: Please explain how to use database lock in TypeORM using setLock() function

user_14855882 0
Viewed 2435

I want to implement database row wise lock in PostgreSQL with Nestjs and TypeORM. Could you please any one let me know how to implement it in correct way. I tried with the following code but not sure whether it is locking the particular row.

async testFun(): Promise<any> {
        const user = await this.userRepo
                    .createQueryBuilder("user")
                    .useTransaction(true)
                    .setLock("pessimistic_write")
                    .where("id = :id", { id: 2 })
                    .getOne()

        const updateResult: any = await this.userRepo.createQueryBuilder('test')
                                .update()
                                .set({ fullname: "frm3000" })
                                .where("id = :id", { id: 2 })
                                .execute();
        
        return updateResult;
    }

Query generated by TypeORM is,

query: START TRANSACTION
query: SELECT * FROM "users" "user" WHERE id = $1 FOR UPDATE -- PARAMETERS: [2]      
query: COMMIT
query: UPDATE "users" SET "fullname" = $1 WHERE "id" = $2 -- PARAMETERS: ["from3000",2]

Here commit done before update query, so doubted whether the row locked or not.

Also I want to test it in my local host, with two different Ports localhost:3000 and localhost:3001. Hence I added same controller and service code in another app with port 3001 with delay,

async testFun(): Promise<any> {
        const user = await this.userRepo
                    .createQueryBuilder("user")
                    .useTransaction(true)
                    .setLock("pessimistic_write")
                    .where("id = :id", { id: 2 })
                    .getOne()
        

        function sleep(ms) {
            return new Promise(resolve => setTimeout(resolve, ms));
        }
        await sleep(20000);

        const updateResult: any = await this.userRepo.createQueryBuilder('test')
                                .update()
                                .set({ fullname: "frm3001" })
                                .where("id = :id", { id: 2 })
                                .execute();
        
        return updateResult;
    }

Please let me know is this approach correct or suggest better way if any. Also the testing approach too not working.

Thanks!

typeorm nestjs typescript node.js postgresql
1 Answers

Since you are running query without starting a transaction on TypeORM. All the individual queries are considered as a separate transaction on database.

To run a set of queries in a single transaction you have to Demarcate transaction at application level. One of the ways to set transaction demarcation with TypeORM is by using QueryRunner.

Do the following steps:

const queryRunner = getConnection().createQueryRunner();
  
try {
    await queryRunner.connect();
    await queryRunner.startTransaction();
    const user = await queryRunner.manager.getRepository(User)
                    .createQueryBuilder("user")
                    .useTransaction(true)
                    .setLock("pessimistic_write")
                    .where("id = :id", { id: 2 })
                    .getOne()
    function sleep(ms) {
         return new Promise(resolve => setTimeout(resolve, ms));
    }
    await sleep(20000);

    const updateResult: any = await queryRunner.manager.getRepository(User)
                                .createQueryBuilder('test')
                                .update()
                                .set({ fullname: "frm3001" })
                                .where("id = :id", { id: 2 })
                                .execute();
    await queryRunner.commitTransaction();    
    return updateResult;
} catch(e) {
    await queryRunner.rollbackTransaction();
} finally {
    await queryRunner.release();
}

NOTE: Never forget to release the queryRunner, otherwise your db connections will be used up.

Hope this helps.

user_41671250
Related