I am a Dev
I am a Dev
Explore posts from servers
RReactifluxTtRPCPD🧩 Plasmo DevelopersTTCTheo's Typesafe CultDTDrizzle Team
PostsComments
DTDrizzle Team
Created by I am a Dev on 10/3/2023 in #help
Drizzle support SELECT FOR UPDATE of postgres to avoid logical race conditions ?
I will try tomorrow 🙂
23 replies
DTDrizzle Team
Created by I am a Dev on 10/3/2023 in #help
Drizzle support SELECT FOR UPDATE of postgres to avoid logical race conditions ?
Yes, I tested with 20k items at works fine
23 replies
DTDrizzle Team
Created by I am a Dev on 10/3/2023 in #help
Drizzle support SELECT FOR UPDATE of postgres to avoid logical race conditions ?
Yes, also you can add select pg_sleep(5) before commit, and call it from multiple clients and it do correct the row lock
23 replies
DTDrizzle Team
Created by I am a Dev on 10/3/2023 in #help
Drizzle support SELECT FOR UPDATE of postgres to avoid logical race conditions ?
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,
            },
          });
        }
      );
23 replies
DTDrizzle Team
Created by I am a Dev on 10/3/2023 in #help
Drizzle support SELECT FOR UPDATE of postgres to avoid logical race conditions ?
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
23 replies
DTDrizzle Team
Created by I am a Dev on 10/3/2023 in #help
Drizzle support SELECT FOR UPDATE of postgres to avoid logical race conditions ?
I make this work with Prisma and node-postgres, I try to migrate it to drizzle, but I can't 😦
23 replies
DTDrizzle Team
Created by I am a Dev on 10/3/2023 in #help
Drizzle support SELECT FOR UPDATE of postgres to avoid logical race conditions ?
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');
23 replies
DTDrizzle Team
Created by I am a Dev on 10/3/2023 in #help
Drizzle support SELECT FOR UPDATE of postgres to avoid logical race conditions ?
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;
23 replies