DT
Drizzle Teameatmoose

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
A
AlcaponeYou•351d 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}"
E
eatmoose•351d 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
A
AlcaponeYou•351d ago
I think you might be conflating two ideas here. they are acid, it sounds like an application layer problem
E
eatmoose•351d ago
how do i make sure the read also the latest, and wait if a transactions in progress ?
A
AlcaponeYou•351d 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
E
eatmoose•351d ago
... what would u recommend?
A
AlcaponeYou•351d ago
set({usd: sql`usd + ${value}`})
set({usd: sql`usd + ${value}`})
To fix your current logic
E
eatmoose•351d ago
thx but i really need read modify write
A
AlcaponeYou•351d ago
isn't that wat your transaction is doing
E
eatmoose•351d 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
A
AlcaponeYou•351d 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
E
eatmoose•351d ago
i passed for('update'), i think its locking that row/column and actually waits for it to finish
A
AlcaponeYou•351d 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?
E
eatmoose•351d ago
ok let me see yep it literally waited an entire minute well i learned something new 2day
R
rphlmr ⚡•350d 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,...
E
eatmoose•350d ago
“Row level locking with SELECT ... FOR UPDATE” My code is doing that right? Just to make sure I’m not doing it wrong
R
rphlmr ⚡•350d ago
It seems yes you can log the executed query to be sure, but I guess it does
E
eatmoose•348d 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
R
rphlmr ⚡•347d ago
Yes 👍
Want results from more Discord servers?
Add your server
More Posts
Explicit inferred types```ts async loginWithGoogle(user: NonNullable<Request['user']>) { const existingUser = await thiMany-to-Many where in far tableI'm trying to `findMany()` Pokemon(s) by a type name ``` pokemons pokemonsToType Cannot call onConflictDoNothing() or on onConflctDoUpdate() on selectHello guys, this is probably right in front of me, but when I insert at the end of values I cannot fTop-level await is not available in the configured target environment ("chrome87", "edge88", "es2020Hey I just upgraded to the v^0.26.2 and I get the following error: ``` Top-level await is not avaican you Infer relations?im really loving how drizzle plays well with relations, but im wondering is there a way to get typesubRows.map is not a functionI am getting a subRows.map is not a function error when adding the "with" parameter to my relationalPostgres's Serial column type doesn't automatically have a defaultHello, when using postgres's `serial` types, and setting them as primary keys, there is an issue curHow to insert into a table without providing valuesI've got the following schema for users in sqlite, it only contains an id, which is an auto incremenDoent infer type in Callback syntax for queryIm querying the users table like this `db.query.user.findFirst({ where: (user, { eq }) => { Bug When Trying To Increment A Fieldthis set the field to 0 instead of increasing by one it used to work in other routes ``` await Maximum call stack exceeded with relation to selfI have the following table ``` export const category = pgTable('categories', { id: serial('id').importing into schema.ts fileHi, I am using turborepo and I have defined my schema in `packages/schema/user.ts` and in my main AWhat versions of MySQL are supported? I have JSON_ARRAYAGG issues with 5.7 and AWS Aurora ServerlessCan you confirm what versions of MySQL you intend on supporting? Relational queries with joins use J`$with` example in docs error "unsupported: with expression in select statement"I have a user with id of 42, just like the docs https://orm.drizzle.team/docs/crud#with-clause but Infer type for relational queryIs there anyway to infer the type of a relational queries result? For example if I have a user who in drizzle.config.ts: "Cannot use import statement outside a module"happens when I try to `push` ```ts import type { Config } from "drizzle-kit"; import { env } from "Error when using Drizzle (Non-abstract class 'PgSelect<TTableName, TSelection, TSelectMode, [...])I'm getting the following error when I try to use Drizzle: ```> graphql-server@1.0.0 start > npm runGenerate classes/interfaces from existing schemas?Messing around with drizzle, and I was wondering if there was any way to generate a class or interfaRelation Query - Get likes in postPlaying around with relational queries and I'm not quite getting how I'd retrieve the count of likesRelation query `extras` needs access to `with`I have a mysql table with two relations to another table. (one is for source values, one is for opti