Prisma Error: Transaction failed due to a write conflict or a deadlock. Please retry your transaction

Question

I am working on Prisma in my backend application. And I want to manage navigation items for each role and users. This is my db model and statement to delete old navigations to update.

model Navigation {
  id        String       @id @default(cuid())
  createdAt DateTime?    @default(now())
  updatedAt DateTime?    @default(now()) @updatedAt
  name      String?
  title     String?
  subtitle  String?
  type      String?
  icon      String?
  link      String?
  userId    String?
  order     Int?
  roleId    String?
  parentId  String?
  children  Navigation[] @relation(name: "Navigation_children")
  featureId String?
  user      User?        @relation(name: "User_navigations", fields: [userId], references: [id])
  parent    Navigation?  @relation(name: "Navigation_children", fields: [parentId], references: [id], onDelete: NoAction, onUpdate: NoAction)
}

    await  this.navigation.deleteMany({
        where: {
          userId: userId,
          parentId: {
            not: null,
          },
        },
      })
    await  this.navigation.deleteMany({
        where: {
          userId: userId,
          parentId: null
        },
      })

My navigation items is only 2 layer, so I think I can delete all navigation items using above commands. But I got error:

Invalid prisma.navigation.deleteMany() invocation:
Transaction failed due to a write conflict or a deadlock. Please retry your transaction

How to solve this error?

And how to delete all unnecessary navigation items using only 1 command?

Thank you.

Answer 1

I solved my problem, so I will share that.

First, I detached children to avoid foreign key constraint issues. And then deleted the records.

This is my code.

    // First, detach children to avoid foreign key constraint issues.
    await this.navigation.updateMany({
      where: {
        userId: userId,
        parent: {
          isNot: null,
        },
      },
      data: {
        parentId: null,
      },
    });

    // Then, delete the records.
    await this.navigation.deleteMany({
      where: {
        userId: userId,
      },
    });