How to use SELECT FOR UPDATE of postgres to avoid logical race conditions?

[MiniSuperDev]

I have a similar use case like this: https://on-systems.tech/blog/128-preventing-read-committed-sql-concurrency-errors/

Currently I use a mutex in my node main process, but I think is better to move this to the db.

If is possible can you show me a minimal example

Thank you so much

For example how to do this statement?

BEGIN;
SELECT * FROM "counters" WHERE id = 1 FOR UPDATE;
UPDATE "counters" SET count = count + 1 WHERE id = 1;
COMMIT;

I tried with this, but this is called 50 times but only increment the counter 1 time, not like the expected behavior

const incrementCounter = async (id: number): Promise<void> => {
      console.log(`Incrementing counter ${id}`);
      await db.transaction(async (tx) => {
        const counter = await tx
          .select()
          .from(counters)
          .where(eq(counters.id, id))
          .for('update');
        if (counter.length > 0) {
          const newCount = counter[0].count + 1;

          await tx
            .update(counters)
            .set({
              count: newCount,
            })
            .where(eq(counters.id, id));
        }
      });
    };

    await Promise.allSettled(
      Array.from({ length: 50 }, (_, i) => i).map(() => incrementCounter(1)),
    );
    console.log('Incremented counter 50 times');