Acquire table-level lock?

[e-e]

I'm working on a React application, using Adonis v6 as the backend, with Postgres 16. In dev/strict mode, the components are mounted twice. I have a component that attempts to fetch the "active" record of a table, and if it doesn't exist, makes another request to create a new one. The table should only have one active record at a time. (the row is considered active if the completed_at timestamp column is NULL). The problem I'm running into is that every time that component loads, it hits that endpoint twice, and I get two new records created. I've tried the following:

async store({ auth }: HttpContext) {
  const trx = await db.transaction();
  let record: MyModel | null = null;
  try {
    const existingRecord = await MyModel.query({ client: trx })
      .where("user_id", auth.user!.id)
      .whereNull("completed_at")
      .first();

    if (existingRecord) {
      return existingRecord;
    }

    record = await MyModel.create({ userId: auth.user!.id }, { client: trx });
    await trx.commit();

    return record;
  } catch (err) {
    await trx.rollback();
    throw err;
  }
}

and

async store({ auth }: HttpContext) {
  const trx = await db.transaction();
  cosnt record = await MyModel.firstOrCreate({
    userId: auth.user!.id,
    completedAt: null,
  }, { userId: auth.user!.id }, {client: trx });

  await trx.commit();

  return record;
}

with no luck. I'm assuming the db transaction doesn't actually lock the table. Is there a way to do this with Lucid query builder? Open to any solutions - thanks in advance.

[RomainLanz]

Hey @e-e! 👋🏻

You can lock the index by using forUpdate on your select.

const existingRecord = await MyModel.query({ client: trx })
  .forUpdate()
  .where("user_id", auth.user!.id)
  .whereNull("completed_at")
  .first();