S
Supabase•5d ago
nsadeh

Race condition in insert

Hello, I have the following snippet: 
const { data: prompt, error } = await supabase
    .from("prompts")
    .insert({ name, description })
    .select("*")
    .single();
  if (error) {
    throw new Error(error.message);
  }
  const { data: version, error: versionError } = await supabase
    .from("prompt_versions")
    .insert({
      prompt_id: prompt.id,
      content,
    });
  if (versionError) {
    throw new Error(versionError.message);
  }
  return prompt;
}
const { data: prompt, error } = await supabase
    .from("prompts")
    .insert({ name, description })
    .select("*")
    .single();
  if (error) {
    throw new Error(error.message);
  }
  const { data: version, error: versionError } = await supabase
    .from("prompt_versions")
    .insert({
      prompt_id: prompt.id,
      content,
    });
  if (versionError) {
    throw new Error(versionError.message);
  }
  return prompt;
}
The important piece is to note that prompt_id on the prompt_versions table is a foreign key to the id column of prompts. About 1 times out of 10 I get an error inserting the prompt version that it violates the foreign key constraint because the prompt doesn't exist. The prompt is however inserted successfully, Given the flakiness of the error, I'm positive it's a race condition. I'm thinking this shouldn't happen since the routine to insert the prompt completes successfully and is awaited. Please let me know if I'm missing something here.
5 Replies
nsadeh
nsadehOP•5d ago
My current workaround is to implement retry with backoff for the version insert, but thought it needed attention because it shouldn't happen
vick
vick•5d ago
It shouldn't happen. The await will not return until it is committed to the DB. I would also try logging the prompt.id you are attempting to use.
nsadeh
nsadehOP•5d ago
If Supabase uses read replicas and the second read comes from a read replica it's possible if the backend is eventually consistent In other words the prompt is committed to a write db but may have not propagated to the replica the version is attempting to insert into
vick
vick•5d ago
Are you paying Supabase for replicas? That's not their default. Aside, a read-only replica wouldn't be involved in a write operation.
nsadeh
nsadehOP•5d ago
That's true. An no, this is free, I wasn't aware of how their architecture is setup but I thought some sort of distributed system/eventual consistency is the most likely explanation, as I can confirm the prompt ID is returned and the prompt exists prior to the attempt to insert the prompt ID.

Did you find this page helpful?