Drizzle Team•4d ago

transations

Greetings! I am trying to perform 2 inserts to my SQLite db, but I want that if one of the 2 fails, it will revert any changes made to it. I then decided to use a transaction. My goal is to achieve that when inserting an account, a user is then created and associated with it. But I get an error when executing this function.

16 Replies

DoggeSlapper•4d ago

It's looks like the db cant obtain a lock. Anything else runing at same time? try changing transaction mode to see if u cant see what's happening there

edarcode•4d ago

nothing, it's the only transaction

DoggeSlapper•4d ago

await db.transaction(
  async (tx) => {
    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, "Andrew"));
  }, {
    behavior: "deferred",
  }
);
interface SQLiteTransactionConfig {
    behavior?: 'deferred' | 'immediate' | 'exclusive';
}

await db.transaction(
  async (tx) => {
    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, "Andrew"));
  }, {
    behavior: "deferred",
  }
);
interface SQLiteTransactionConfig {
    behavior?: 'deferred' | 'immediate' | 'exclusive';
}

try diferent behavior then to see if that helps debug the issue

edarcode•4d ago

ok. let me commit and try.

edarcode•4d ago

@DoggeSlapper equal err

edarcode•4d ago

DoggeSlapper•4d ago

what if u had some console log? get some info there? I'm not using sqlite myself so cant really help much sorry that were my 2 cents 🙂

edarcode•4d ago

after line 5

edarcode•4d ago

the info arrive good, but explot when try transation

DoggeSlapper•4d ago

I suspect it's lock on first insert

edarcode•4d ago

I really don't understand how it works, but the await is supposed to wait before doing the second insert, right? I even think that it shouldn't matter if I block it because I actually want to wait for the first insert to finish so that it does the second one, because I depend on knowing the id.

DoggeSlapper•4d ago

wait I saw the problem u using db not tx so that's the reason why u blocking it

edarcode•4d ago

DoggeSlapper•4d ago

funny enough I paste doc but didnt notice the error hehehe

edarcode•4d ago

Obviously I'm an idiot xD. 2h trying to solve it and it was 2 letters, good GPT chat doesn't even work to warn us what it was xd and they'll even take away our jobs, dammit thanks bro @DoggeSlapper

DoggeSlapper•4d ago

hahahah

Gaming

Programming

transations