Drizzle Team•15mo ago

Drizzle support SELECT FOR UPDATE of postgres to avoid logical race conditions ?

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

Systems and Tech Thoughts

Preventing Postgres SQL Race Conditions with SELECT FOR UPDATE

Learn how to prevent logical race conditions in read-modify-write SQL workloads using SELECT FOR UPDATE.

18 Replies

I am a DevOP•15mo ago

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;

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 time 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');

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');

Angelelz•15mo ago

I'm seeing the same behavior But this is not an issue with drizzle as the statements it's outputing are all correct It's the database that's not doing what's supposed to do

I am a DevOP•15mo ago

I make this work with Prisma and node-postgres, I try to migrate it to drizzle, but I can't 😦

delight•15mo ago

Prisma often times does multiple queries ... you should check the emitted sql by logging it to console - so that you can figure what prisma does.

I am a DevOP•15mo ago

In prisma I use raw sql since it not support select ... for update , but I can't figure out how to do it with drizzle methods

Angelelz•15mo ago

Can you show the code you used for prisma?

I am a DevOP•15mo ago

It's something like that

    await prisma.$transaction(
        async (tx) => {
          const counters = await tx.$queryRaw<
            Counter[]
          >`SELECT * FROM "Counter" WHERE id = ${input.id} FOR UPDATE;`; 

          if (counters.length === 0) {
            throw new Error('Counter not found');
          }

          const counter = counters[0];
          await tx.counter.update({
            data: {
              count: counter.count + 1,
            },
            where: {
              id: counter.id,
            },
          });
        }
      );

    await prisma.$transaction(
        async (tx) => {
          const counters = await tx.$queryRaw<
            Counter[]
          >`SELECT * FROM "Counter" WHERE id = ${input.id} FOR UPDATE;`; 

          if (counters.length === 0) {
            throw new Error('Counter not found');
          }

          const counter = counters[0];
          await tx.counter.update({
            data: {
              count: counter.count + 1,
            },
            where: {
              id: counter.id,
            },
          });
        }
      );

Angelelz•15mo ago

Did you call that function concurrently 50 times and it worked? With the same driver and database?

I am a DevOP•15mo ago

Yes, also you can add select pg_sleep(5) before commit, and call it from multiple clients and it do correct the row lock

Angelelz•15mo ago

I can do more tests later but can you double check that this would work as you expect with the prisma code?

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

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

I am a DevOP•15mo ago

Yes, I tested with 20k items at works fine

Angelelz•15mo ago

Even better, if you could create a reproduction repository with prisma and drizzle? I'm also interested in this, but I'm tied up right now.

I am a DevOP•15mo ago

I will try tomorrow 🙂

Fayssal•11mo ago

I had the same issue with drizzle and I ended by finding the issue. I was using Client session, when I switched to Pool it started working https://orm.drizzle.team/docs/get-started-postgresql#node-postgres

Drizzle ORM - PostgreSQL

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

stumpykilo•10mo ago

this fixed the issue for me too. i could not get Drizzle to work with Client, only Pool works for me. thank you @Fayssal!

desdenova•10mo ago

@Angelelz This is a bug in Drizzle and the way it uses node-postgress in Client mode. Since the Client mode is using only 1 connection, you cannot have more than 1 transaction running at the same time so what happens here is that all the db statements get crammed in a single transaction (concurrent transaction BEGIN statements are treated as one because there is only 1 client)

desdenova•10mo ago

here's a quick and dirty hack to fix the bug

desdenova•10mo ago

i think the same bug could potentially occur in the POOL mode as well in case the connection that's already running a transaction gets reused to run another one, but I'm not sure how it works internally

Gaming

Programming

Drizzle support SELECT FOR UPDATE of postgres to avoid logical race conditions ?