Race condition executing a common pattern (select or insert)
I'm having trouble understanding how to execute a common pattern in Postgres and I'm using Drizzle as an ORM adapted. The common pattern is this: I want to fetch a single row by some query, for example
But if that record doesn't exist I want to create a new record and use that one later on in the file.
Now, the thing is I am doing this in an API route that might be called multiple times at the same time and I want to avoid race conditions so I wrapped this in a transaction.
Now the problem is that even though this is wrapped in a transaction, race conditions can still occur where 2 users call my API endpoint at the exact same time, and 2 new records are created. How do I update my API endpoint to avoid this scenario using Drizzle syntax or PostgreSQL?
But if that record doesn't exist I want to create a new record and use that one later on in the file.
Now, the thing is I am doing this in an API route that might be called multiple times at the same time and I want to avoid race conditions so I wrapped this in a transaction.
Now the problem is that even though this is wrapped in a transaction, race conditions can still occur where 2 users call my API endpoint at the exact same time, and 2 new records are created. How do I update my API endpoint to avoid this scenario using Drizzle syntax or PostgreSQL?
