"Unique constraint failed on the fields" when using upsert

[mcarcaso]

Bug description

I was messing around with attempting to create a mutex lock using the db and thought I could do it via an upsert but it looks like the upsert can fail due to a race condition. The schema and code used to generate this error is in the "How to reproduce" section.

Here's the error with DEBUG="*" on:

 prisma:client  Prisma Client call: +156ms
  prisma:client  prisma.mutexLock.upsert({
  create: {
    name: 'TEST1660761643498'
  },
  update: {
    next: {
      increment: 1
    }
  },
  where: {
    name: 'TEST1660761643498'
  }
}) +1ms
  prisma:client  Generated request: +0ms
  prisma:client  mutation {
  upsertOneMutexLock(
    create: {
      name: "TEST1660761643498"
    }
    update: {
      next: {
        increment: 1
      }
    }
    where: {
      name: "TEST1660761643498"
    }
  ) {
    name
    current
    next
    createdAt
    updatedAt
  }
}
 +0ms
  prisma:client  Prisma Client call: +24ms
  prisma:client  prisma.mutexLock.upsert({
  create: {
    name: 'TEST1660761643498'
  },
  update: {
    next: {
      increment: 1
    }
  },
  where: {
    name: 'TEST1660761643498'
  }
}) +4ms
  prisma:client  Generated request: +0ms
  prisma:client  mutation {
  upsertOneMutexLock(
    create: {
      name: "TEST1660761643498"
    }
    update: {
      next: {
        increment: 1
      }
    }
    where: {
      name: "TEST1660761643498"
    }
  ) {
    name
    current
    next
    createdAt
    updatedAt
  }
}
 +0ms
  prisma:client:libraryEngine  sending request, this.libraryStarted: false +185ms
  prisma:client:libraryEngine  sending request, this.libraryStarted: false +0ms
  prisma:client:libraryEngine  library starting +0ms
  prisma:client:libraryEngine  library already starting, this.libraryStarted: false +22ms
  prisma:client:libraryEngine  library started +41ms
  prisma:client:request_handler  PrismaClientKnownRequestError: Unique constraint failed on the fields: (`name`)

How to reproduce

With the following model in the schema:

model MutexLock {
  name    String @unique
  current Int    @default(0)
  next    Int    @default(0)
}

Run this code:

  const name = 'TEST' + Date.now();
  await Promise.all([
    prisma.mutexLock.upsert({
      create: { name },
      update: { next: { increment: 1 } },
      where: { name },
    }),
    prisma.mutexLock.upsert({
      create: { name },
      update: { next: { increment: 1 } },
      where: { name },
    }),
  ]);

Expected behavior

I'd expect one transaction to create the row and another to update the row.

Prisma information

Here's the model:

model MutexLock {
  name    String @unique
  current Int    @default(0)
  next    Int    @default(0)
}

Environment & setup

• OS: Mac OS
• Database: PostgreSQL
• Node.js version: 16.16.0

Prisma Version

prisma                  : 4.0.0
@prisma/client          : 4.0.0
Current platform        : darwin-arm64
Query Engine (Node-API) : libquery-engine da41d2bb3406da22087b849f0e911199ba4fbf11 (at node_modules/@prisma/engines/libquery_engine-darwin-arm64.dylib.node)
Migration Engine        : migration-engine-cli da41d2bb3406da22087b849f0e911199ba4fbf11 (at node_modules/@prisma/engines/migration-engine-darwin-arm64)
Introspection Engine    : introspection-core da41d2bb3406da22087b849f0e911199ba4fbf11 (at node_modules/@prisma/engines/introspection-engine-darwin-arm64)
Format Binary           : prisma-fmt da41d2bb3406da22087b849f0e911199ba4fbf11 (at node_modules/@prisma/engines/prisma-fmt-darwin-arm64)
Default Engines Hash    : da41d2bb3406da22087b849f0e911199ba4fbf11
Studio                  : 0.465.0
Preview Features        : interactiveTransactions
Done in 1.47s.

[doz13189]

I have the same issue.
And I checked the query log, is there any way to resolve this with prisma clinet?

Query: BEGIN
Params: []
Duration: 1ms
Query: BEGIN
Params: []
Duration: 0ms
Query: SELECT "public"."MutexLock"."name" FROM "public"."MutexLock" WHERE "public"."MutexLock"."name" = $1 OFFSET $2 /* traceparent=00-00-00-00 */
Params: ["TEST1661596171755",0]
Duration: 10ms
Query: (★The second upsert select query ★) SELECT "public"."MutexLock"."name" FROM "public"."MutexLock" WHERE "public"."MutexLock"."name" = $1 OFFSET $2 /* traceparent=00-00-00-00 */
Params: ["TEST1661596171755",0]
Duration: 7ms
Query: INSERT INTO "public"."MutexLock" ("name","current","next") VALUES ($1,$2,$3) RETURNING "public"."MutexLock"."name" /* traceparent=00-00-00-00 */
Params: ["TEST1661596171755",0,0]
Duration: 5ms
Query: SELECT "public"."MutexLock"."name", "public"."MutexLock"."current", "public"."MutexLock"."next" FROM "public"."MutexLock" WHERE "public"."MutexLock"."name" = $1 LIMIT $2 OFFSET $3 /* traceparent=00-00-00-00 */
Params: ["TEST1661596171755",1,0]
Duration: 2ms
Query: (★ the first commit of the insert query ★) COMMIT
Params: []
Duration: 1ms
Query: INSERT INTO "public"."MutexLock" ("name","current","next") VALUES ($1,$2,$3) RETURNING "public"."MutexLock"."name" /* traceparent=00-00-00-00 */
Params: ["TEST1661596171755",0,0]
Duration: 15ms
Query: ROLLBACK
Params: []
Duration: 1ms

The second upsert select query is executed before the first commit of the insert query. I would like to know if this is a problem that can be resolved with prisma client.

As shown below, I have taken the workaround and made it run synchronously.

  // errors
  await Promise.all([
    prisma.mutexLock.upsert({
      create: { name },
      update: { next: { increment: 1 } },
      where: { name },
    }),
    prisma.mutexLock.upsert({
      create: { name },
      update: { next: { increment: 1 } },
      where: { name },
    }),
  ])

  // no errors
  await prisma.mutexLock.upsert({
    create: { name },
    update: { next: { increment: 1 } },
    where: { name },
  })
  await prisma.mutexLock.upsert({
    create: { name },
    update: { next: { increment: 1 } },
    where: { name },
  })

[mcarcaso]

For my use case, I ended up just using a raw query that does an INSERT ON CONFLICT: https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-upsert/
I assumed that prisma's upsert did this but I guess not. Is this something that it can do so we don't need to use a raw query?

[doz13189]

For my use case, I ended up just using a raw query that does an INSERT ON CONFLICT:

That's good.

I assumed that prisma's upsert did this but I guess not.

Looking at the log queries, I think not too.

[garrensmith]

Prisma Upserts are very powerful and support very nested upserts. This can lead to a Unique constraint error being thrown. We have updated our docs with why this happens and what to do in that situation.
We have also added support for INSERT .. ON CONFLICT .. UPDATE see the docs on when it will be used.