Drizzle Team•2y ago

noob help : transaction not acid

 return await this.drizzle.db.transaction(async (trx) => {
      const u = await trx
        .select({
          usd: users.usd,
        })
        .from(users)
        .where(eq(users.id, 1));
      if (!u || u.length == 0) {
        throw new HttpException(
          {
            message: 'User not found',
            success: false,
          },
          404,
        );
      }

      const user = u[0];
      await this.timeout(5000);
      return (
        await trx
          .update(users)
          .set({
            usd: user.usd + val,
          })
          .where(eq(users.id, 1))
          .returning()
      )[0].usd;

 return await this.drizzle.db.transaction(async (trx) => {
      const u = await trx
        .select({
          usd: users.usd,
        })
        .from(users)
        .where(eq(users.id, 1));
      if (!u || u.length == 0) {
        throw new HttpException(
          {
            message: 'User not found',
            success: false,
          },
          404,
        );
      }

      const user = u[0];
      await this.timeout(5000);
      return (
        await trx
          .update(users)
          .set({
            usd: user.usd + val,
          })
          .where(eq(users.id, 1))
          .returning()
      )[0].usd;

sending two req at the same time, one with +10 the other with +100, the expected behavior for the trasnaction to wait for previous req to complete so its consistent

19 Replies

AlcaponeYou•2y ago

You are capturing the user object in this transaction, then waiting 5 seconds to add that value to the database. The 2nd query comes in and does the same thing. After 10 requests, the last query will have the final update: user.usd would be $0 + 100 = $100 That's not how you increment db values is that postgres? U'd want to do something like: "sqlSET usd = usd + ${value}"

eatmooseOP•2y ago

I’m using Postgres yah, What if I need to do logic on user balance ? The read request should also be acid too I thought

AlcaponeYou•2y ago

I think you might be conflating two ideas here. they are acid, it sounds like an application layer problem

eatmooseOP•2y ago

how do i make sure the read also the latest, and wait if a transactions in progress ?

AlcaponeYou•2y ago

that's something that the db handles, eg if there are 2 concurrent request writing to the same table/col... pg will lock one and wait for it to be finished before the 2nd request can write to it. the db is doing exactly what it needs to do, your business logic is faulty

eatmooseOP•2y ago

... what would u recommend?

AlcaponeYou•2y ago

set({usd: sql`usd + ${value}`})

set({usd: sql`usd + ${value}`})

To fix your current logic

eatmooseOP•2y ago

thx but i really need read modify write

AlcaponeYou•2y ago

isn't that wat your transaction is doing

eatmooseOP•2y ago

nope i could have some insane logic before write, that requires me to have the latest value for usd

 return await this.drizzle.db.transaction(
      async (trx) => {
        const u = await trx
          .select({
            usd: users.usd,
          })
          .from(users)
          .where(eq(users.id, 1))
          .for('update');
        if (!u || u.length == 0) {
          throw new HttpException(
            {
              message: 'User not found',
              success: false,
            },
            404,
          );
        }

        const user = u[0];
        await this.timeout(5000);
        return (
          await trx
            .update(users)
            .set({
              usd: user.usd + val,
            })
            .where(eq(users.id, 1))
            .returning()
        )[0].usd;
      },
      {
        isolationLevel: 'read committed',
        accessMode: 'read write',
        deferrable: true,
      },
    );

 return await this.drizzle.db.transaction(
      async (trx) => {
        const u = await trx
          .select({
            usd: users.usd,
          })
          .from(users)
          .where(eq(users.id, 1))
          .for('update');
        if (!u || u.length == 0) {
          throw new HttpException(
            {
              message: 'User not found',
              success: false,
            },
            404,
          );
        }

        const user = u[0];
        await this.timeout(5000);
        return (
          await trx
            .update(users)
            .set({
              usd: user.usd + val,
            })
            .where(eq(users.id, 1))
            .returning()
        )[0].usd;
      },
      {
        isolationLevel: 'read committed',
        accessMode: 'read write',
        deferrable: true,
      },
    );

this looks like its working, without using isolation lv of serailize which wants me to implement retry handling

AlcaponeYou•2y ago

again, doing user.usd + val will set an old value what happens if the usd value got updated some time between 1 and 5 seconds? your user.usd would be outdated

eatmooseOP•2y ago

i passed for('update'), i think its locking that row/column and actually waits for it to finish

AlcaponeYou•2y ago

change the timeout to 1 minute run your script then go into your db console and change the value will it allow you to update the value via console or cli due to that flag?

eatmooseOP•2y ago

ok let me see yep it literally waited an entire minute well i learned something new 2day

rphlmr ⚡•2y ago

This can help to find the best way for your needs: https://www.2ndquadrant.com/en/blog/postgresql-anti-patterns-read-modify-write-cycles/

craig.ringer

2ndQuadrant | PostgreSQL

PostgreSQL anti-patterns: read-modify-write cycles

Shaun Thomas’s recent post about client-side loops as an SQL anti-pattern is well worth a read if you’re relatively new to SQL-based application development. It’s reminded me of another SQL coding anti-pattern that I see quite a lot: the naïve read-modify-write cycle. Here I’ll explain what this common development mistake is, how to identify it,...

eatmooseOP•2y ago

“Row level locking with SELECT ... FOR UPDATE” My code is doing that right? Just to make sure I’m not doing it wrong

rphlmr ⚡•2y ago

It seems yes you can log the executed query to be sure, but I guess it does

eatmooseOP•2y ago

i have a question, the docs do this

  await tx.update(accounts).set({ balance: sql`${accounts.balance} - 100.00` }).where(eq(users.name, 'Dan'));

  await tx.update(accounts).set({ balance: sql`${accounts.balance} - 100.00` }).where(eq(users.name, 'Dan'));

does this do row locking if multiple request for that code gets run at the same time? because its not locking accounts with for update

rphlmr ⚡•2y ago

Yes 👍

Gaming

Programming

noob help : transaction not acid