What are the possible ways to prevent race conditions in this postgres insert operation?

Here are typescript types for instructions and sections database tables:

type Instruction = {
  body: string;
  id: string;
  // Foreign key reference to `id` column of Section
  sectionId: string | null;
};

type Section = {
  id: string;
  name: string;
  // Foreign key reference to `id` column of Section 
  parentSectionId: string | null;
};

type Instruction = {
  body: string;
  id: string;
  // Foreign key reference to `id` column of Section
  sectionId: string | null;
};

type Section = {
  id: string;
  name: string;
  // Foreign key reference to `id` column of Section 
  parentSectionId: string | null;
};

I want it so that any time a section can only contain many instructions or many sections but not both. How do I enforce this while preventing race conditions that could create inconsistencies? For example take a look at the following code:

// START
const parentSection = await drizzle.sections.findFirst({ 
  with: {
    instructions: {
      limit: 1,
    }
  }
  where: (fields, operators) => operators.eq(fields.id, sectionId)
 });


if (parentSection.instructions[0] !== undefined) {
  throw new Error("Section already contains instructions.");
};

const childSection = await drizzle.insert(sections).values({ name, parentSectionId: sectionId }).returning();
// END

// START
const parentSection = await drizzle.sections.findFirst({ 
  with: {
    instructions: {
      limit: 1,
    }
  }
  where: (fields, operators) => operators.eq(fields.id, sectionId)
 });


if (parentSection.instructions[0] !== undefined) {
  throw new Error("Section already contains instructions.");
};

const childSection = await drizzle.insert(sections).values({ name, parentSectionId: sectionId }).returning();
// END

There is a race condition here during the time between START and END where the parent section could be made to contain instructions before this operation completes. What can be done to prevent any row in sections table to be referenced with sectionId as the value for parentSectionId column while this operation is being carried out?

0 Replies

No replies yetBe the first to reply to this messageJoin

Gaming

Programming

What are the possible ways to prevent race conditions in this postgres insert operation?