Drizzle Team•2mo ago

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

AnthonyOP•2mo ago

My original though was an onConflictDoUpdate, but in this case the phoneNumber field is not unique.

Luxaritas•2mo 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

AnthonyOP•2mo ago

@Luxaritas I think we decided to go for an advisory lock

Gaming

Programming

Race condition executing a common pattern (select or insert)