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
let record = await db.query.records.findFirst({
where: eq(records.phoneNumber, '+15555555555'),
orderBy: desc(records.createdAt)
});
let record = await db.query.records.findFirst({
where: eq(records.phoneNumber, '+15555555555'),
orderBy: desc(records.createdAt)
});
But if that record doesn't exist I want to create a new record and use that one later on in the file.
let record = await db.query.records.findFirst({
where: eq(records.phoneNumber, '+15555555555'),
orderBy: asc(records.createdAt)
});

if (!record) {
const [newRecord] = await db.insert(records)
.values({
phoneNumber: '+15555555555'
})
.returning();

record = newRecord;
}
let record = await db.query.records.findFirst({
where: eq(records.phoneNumber, '+15555555555'),
orderBy: asc(records.createdAt)
});

if (!record) {
const [newRecord] = await db.insert(records)
.values({
phoneNumber: '+15555555555'
})
.returning();

record = newRecord;
}
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.
const record = await db.transaction(async (tx) => {
// First try to find the existing record
const existingRecord = await tx.query.records.findFirst({
where: eq(records.phoneNumber, '+15555555555'),
orderBy: asc(records.createdAt)
});

if (existingRecord) {
return existingRecord;
}

// If no record exists, create a new one
const [newRecord] = await tx.insert(records)
.values({
phoneNumber: '+15555555555'
})
.returning();

return newRecord;
});
const record = await db.transaction(async (tx) => {
// First try to find the existing record
const existingRecord = await tx.query.records.findFirst({
where: eq(records.phoneNumber, '+15555555555'),
orderBy: asc(records.createdAt)
});

if (existingRecord) {
return existingRecord;
}

// If no record exists, create a new one
const [newRecord] = await tx.insert(records)
.values({
phoneNumber: '+15555555555'
})
.returning();

return newRecord;
});
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?
3 Replies
Anthony
AnthonyOP2w ago
My original though was an onConflictDoUpdate, but in this case the phoneNumber field is not unique.
Luxaritas
Luxaritas2w ago
Postgres does not have a mechanism to upsert workout some form of unique index. If you really need to let it not be unique, you probably need to use manual locking, a semaphore table, etc
Anthony
AnthonyOP2w ago
@Luxaritas I think we decided to go for an advisory lock
Want results from more Discord servers?
Add your server