Prisma - Postgres transaction - problem read incremental number in concurrent transaction

Question

I am currently trying to use the new "interactiveTransactions" preview feature of Prisma. However, it does not seem to actually make my database changes as a transaction.

I am trying to update an incremental number on my school table whenever I create a new report.

This is how my tables look like:

model Report {
  id   String @id @default(cuid())
  name String
  publicId Int 
  schoolId String

  school @relation(fields: [schoolId], references: [id])
  @@unique([publicId, schoolId])
}

model School {
  id   String @id @default(cuid())
  name String
  incrementalReportNumber Int @default(1001)

  reports         Report[]
}

my code to increment the number whenever I am creating a new report looks like this:

 report = await prisma.$transaction(async () => {
      // get current report number
      const school = await prisma.school.findUnique({
        where: {
          id: school.id
        }
      });
      const newIncrementalReportNumber = school.incrementalReportNumber + 1;

      // Increase incrementalReportNumber
      await prisma.school.update({
        where: { id: school.id },
        data: {
          incrementalReportNumber: newIncrementalReportNumber
        }
      });

      // Create report
      const newReport = await prisma.report.create({
        data: {
          publicId: newIncrementalReportNumber,
          name: 'new report'
        },
      });

      // Return
      return newReport;
    });

When I create multiple reports at the same time I get an "Unique constraint failed" error,

the Error PrismaClientKnownRequestError: Unique constraint failed on the fields: (`publicId`,`schoolId`)

because when I get incrementalReportNumber, some process have same value, despite other process already increment it.

I registered the "interactiveTransactions" preview feature with prisma like this:

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["interactiveTransactions"]
}

I am using Prisma version "3.2.1"

Answer 1

I realized, that for your specific usecase you could also increment your incrementalReportNumber with an atomic number operation

This avoids the need for a stricter transaction isolation level, because you only run update queries and avoid all read queries:

 const report = await prisma.$transaction(async (prisma) => {
      const school = await prisma.school.update({
        where: { id: schoolId },
        data: {
          incrementalReportNumber: {
            // increment the report number here
            increment: 1,
          },
        },
      });

      const newReport = await prisma.report.create({
        data: {
          publicId: school.incrementalReportNumber,
          schoolId: school.id,
          name: reportName,
        },
      });

      return newReport;
    });