Search code examples
mysqlnode.jstransactionsmariadbtypeorm

Locking and Skipping Locked Rows in MariaDB


In the database, there are 2 rows that match the query condition. If we run two parallel queries, the first query locks both rows and returns one, while the second query returns null.

It would be desirable to have the ability to lock only one (returned) row to avoid such a situation.

const queryRunner : QueryRunner = this.connect.createQueryRunner();
await queryRunner.connect();
await queryRunner.startTransaction();
const identificationProcess = await queryRunner.manager
            .getRepository(IdentificationProcessEntity)
            .createQueryBuilder("identification_process")
            .orderBy("identification_process.updated_at", "ASC")
            .where("identification_process.status = :status", { status: IdentificationStatusEnum.Scanning })
            .useTransaction(true)
            .setLock("pessimistic_write")
            .setOnLocked("skip_locked")
            .take(1)
            .getOne()

I have tried changing the query parameters.

MariaDB - v10.8.4

SQL:

query: SELECT `identification_process`.`uid` AS `identification_process_uid`, `identification_process`.`user_uid` AS `identification
_process_user_uid`, `identification_process`.`status` AS `identification_process_status`, `identification_process`.`attempts` AS `id
entification_process_attempts`, `identification_process`.`expire_at` AS `identification_process_expire_at`, `identification_process`
.`created_at` AS `identification_process_created_at`, `identification_process`.`updated_at` AS `identification_process_updated_at`, 
`identification_process`.`procedure_uid` AS `identification_process_procedure_uid` FROM `identification_process` `identification_pro
cess` WHERE `identification_process`.`status` = ? ORDER BY `identification_process`.`updated_at` ASC LIMIT 1 FOR UPDATE SKIP LOCKED 
-- PARAMETERS: ["Scanning"]

Solution

  • Locks are always on indexes. So to ensure the minimal amount of locks are obtained for the query, the query needs to use an index in the optimal way for the least locks to be created.

    While indexing is a big and important topic, that is highly recommended in support of optimal queries (and energy conservation), lets look at the query in the question.

    There is a simple one table lookup, no joins, so look at the where criteria. Searching on status requires an index on status. After this is found, the results are sorted by updated_at. This forms the second part of the compound index.

    As such the index in SQL is created with:

    CREATE INDEX idx_status_updated_at ON identification_process (status, updated_at)
    

    Or going by the TypeORM docs:

    @Entity()
    ...
    @Index(["status", "updated_at"])
    ...
    export class identification_process {