Drizzle Team•11mo ago

Transaction not respecting locks

await db.transaction(async (trx) => {
          await trx.execute(sql`LOCK TABLE myTable IN SHARE MODE;`);
await trx.select.where name === kat
if name === kat throw
await trx.insert(myTable).set({name:'kat'})


})

await db.transaction(async (trx) => {
          await trx.execute(sql`LOCK TABLE myTable IN SHARE MODE;`);
await trx.select.where name === kat
if name === kat throw
await trx.insert(myTable).set({name:'kat'})


})

using supabase and postgres spam the above function, sometimes kat gets created more than once however adding trx.execute(BEGIN TRANSACTION ) and then manually comitting it solves the problem, but drizzle logs gives an warning that a transaction is ongoing

14 Replies

eatmooseOP•11mo ago

expected the above code to lock and not allow other transactions through until the transaction is comitted seems like no locks are being respected unless i manually ask for BEGIN and COMMIT @Angelelz is this related to ur fix transaction behaving weirdly, without explictly saying BEING and COMMIT, sometimes it returns an inserted value, only for it to not show up on the db. updates sometimes just dont work but it returns a updated value but nothing updates in db but if i explicitly say BEGIN and COMMIT inside the tx callback at the top and end, it works. but drizze gives warning 'there is no transaction in progress', 'there is already a transaction in progress',

Angelelz•11mo ago

I don't think I've fix anything related to transactions Have you tried using the 3rd parameter of db.transaction to set your lock options?

eatmooseOP•11mo ago

third option? i dont think there is i hope my psudo code makes sense, its basically forcing transaction to wait if the table is being read in another transactions. but nothing is working as expected if i dont have BEGIN and COMMIT statements. without those statements, i get double inserts, inserts not showing in db but return() fine can anyone verify my work around? will this have any bad side effects?

Angelelz•11mo ago

Check out the last bullet point in this page of the docs: https://orm.drizzle.team/docs/transactions

Drizzle ORM - next gen TypeScript ORM

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

Angelelz•11mo ago

Those get applied for you if you provide them.

eatmooseOP•11mo ago

i need to apply SHARE lock

Angelelz•11mo ago

Since you are doing it manually, I don't know what kind of impact it will have.

eatmooseOP•11mo ago

but i tried serializble , and it give unexpected behavior SHARE lock so non transaction reads can come through without BEGIN and COMMIT statements it gives unexpected behaviors

Angelelz•11mo ago

So, you're trying to insert only if the name is not already in the db?

eatmooseOP•11mo ago

can i dm u the full code

Angelelz•11mo ago

Sure

eatmooseOP•11mo ago

GitHub

[BUG]: transaction not commiting · Issue #1704 · drizzle-team/drizz...

What version of drizzle-orm are you using? 0.29.1 What version of drizzle-kit are you using? 0.20.7 Describe the Bug import { env } from "@locus/env" import { drizzle } from "drizzle...

eatmooseOP•11mo ago

thanks @Angelelz , i will say this is fixed by switching to pg instead of postgres

Angelelz•11mo ago

This might need some investigation. Can you put together a reproduction repo to investigate?

Gaming

Programming

Transaction not respecting locks