Concurrent relation upserts conflict #9751

dariusj18 · 2021-10-13T13:10:07Z

Bug description

Running multiple upserts concurrently and receiving the error:

PrismaClientKnownRequestError3 [PrismaClientKnownRequestError]: 
Invalid `prisma.post.upsert()` invocation:


  Unique constraint failed on the fields: (`name`,`type`,`userId`)
    at cb (C:\PROJECTFOLDER\node_modules\@prisma\client\runtime\index.js:36063:17)
    at processTicksAndRejections (internal/process/task_queues.js:93:5)
    at async Promise.all (index 5) {
  code: 'P2002',
  clientVersion: '2.30.2',
  meta: { target: [ 'name', 'type', 'userId' ] }
}

How to reproduce

https://github.com/dariusj18/prisma_playground/tree/prisma_9751

Initialize database
Run npm start

Expected behavior

No errors, upserts happen as expected

Prisma information

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

generator client {
  provider = "prisma-client-js"
  // previewFeatures = ["interactiveTransactions", "selectRelationCount", "referentialActions"]
}

model User {
  id       String @id @default(uuid()) @db.Uuid
  username String @unique

  type UserType

  profiles Profile[]
  tags     Tag[]

  publishedPosts Post[]

  @@unique([username, type])
}

enum UserType {
  PUBLISHER
  AUTHOR
  EDITOR
}

model Profile {
  id  String @id @default(uuid()) @db.Uuid
  bio String

  user   User   @relation(fields: [userId], references: [id])
  userId String @db.Uuid
}

model Tag {
  id   String @id @default(uuid()) @db.Uuid
  name String

  type TagType

  user   User   @relation(fields: [userId], references: [id])
  userId String @db.Uuid

  posts PostTag[]

  @@unique([name, type, userId])
}

enum TagType {
  CATEGORY
  POSTTYPE
  LANGUAGE
}

model Post {
  id    String @id @default(uuid()) @db.Uuid
  title String

  user   User   @relation(fields: [userId], references: [id])
  userId String @db.Uuid

  tags PostTag[]
}

model PostTag {
  post   Post   @relation(fields: [postId], references: [id])
  postId String @db.Uuid

  tag   Tag    @relation(fields: [tagId], references: [id])
  tagId String @db.Uuid

  @@id([postId, tagId])
}

Environment & setup

OS: Windows
Database: PostgreSQL
Node.js version: 14.16.0

Prisma Version

prisma                : 2.30.2
@prisma/client        : 2.30.2
Current platform      : windows
Query Engine (Binary) : query-engine b8c35d44de987a9691890b3ddf3e2e7effb9bf20 (at node_modules\@prisma\engines\query-engine-windows.exe)
Migration Engine      : migration-engine-cli b8c35d44de987a9691890b3ddf3e2e7effb9bf20 (at node_modules\@prisma\engines\migration-engine-windows.exe)
Introspection Engine  : introspection-core b8c35d44de987a9691890b3ddf3e2e7effb9bf20 (at node_modules\@prisma\engines\introspection-engine-windows.exe)
Format Binary         : prisma-fmt b8c35d44de987a9691890b3ddf3e2e7effb9bf20 (at node_modules\@prisma\engines\prisma-fmt-windows.exe)
Default Engines Hash  : b8c35d44de987a9691890b3ddf3e2e7effb9bf20
Studio                : 0.422.0

The text was updated successfully, but these errors were encountered:

dariusj18 · 2021-10-13T13:11:55Z

Debug output

prisma:query BEGIN
prisma:query SELECT "public"."Post"."id" FROM "public"."Post" WHERE "public"."Post"."id" = $1 OFFSET $2
prisma:query INSERT INTO "public"."Post" ("id","title","userId") VALUES ($1,$2,$3) RETURNING "public"."Post"."id"
prisma:query BEGIN
prisma:query SELECT "public"."Post"."id" FROM "public"."Post" WHERE "public"."Post"."id" = $1 OFFSET $2
prisma:query INSERT INTO "public"."Post" ("id","title","userId") VALUES ($1,$2,$3) RETURNING "public"."Post"."id"
prisma:query BEGIN
prisma:query SELECT "public"."Tag"."id" FROM "public"."Tag" WHERE ("public"."Tag"."name" = $1 AND "public"."Tag"."type" = $2 AND "public"."Tag"."userId" = $3) OFFSET $4
prisma:query INSERT INTO "public"."Tag" ("id","name","type","userId") VALUES ($1,$2,$3,$4) RETURNING "public"."Tag"."id"
prisma:query SELECT "public"."Post"."id" FROM "public"."Post" WHERE "public"."Post"."id" = $1 OFFSET $2
prisma:query INSERT INTO "public"."Post" ("id","title","userId") VALUES ($1,$2,$3) RETURNING "public"."Post"."id"
prisma:query INSERT INTO "public"."PostTag" ("postId","tagId") VALUES ($1,$2) RETURNING "public"."PostTag"."postId", "public"."PostTag"."tagId"
prisma:query SELECT "public"."Post"."id", "public"."Post"."title", "public"."Post"."userId" FROM "public"."Post" WHERE "public"."Post"."id" = $1 LIMIT $2 OFFSET $3
prisma:query BEGIN
prisma:query SELECT "public"."Tag"."id" FROM "public"."Tag" WHERE ("public"."Tag"."name" = $1 AND "public"."Tag"."type" = $2 AND "public"."Tag"."userId" = $3) OFFSET $4
prisma:query SELECT "public"."Post"."id" FROM "public"."Post" WHERE "public"."Post"."id" = $1 OFFSET $2
prisma:query SELECT "public"."Tag"."id" FROM "public"."Tag" WHERE ("public"."Tag"."name" = $1 AND "public"."Tag"."type" = $2 AND "public"."Tag"."userId" = $3) OFFSET $4
prisma:query INSERT INTO "public"."Post" ("id","title","userId") VALUES ($1,$2,$3) RETURNING "public"."Post"."id"
prisma:query BEGIN
prisma:query SELECT "public"."Post"."id" FROM "public"."Post" WHERE "public"."Post"."id" = $1 OFFSET $2
prisma:query SELECT "public"."Tag"."id" FROM "public"."Tag" WHERE ("public"."Tag"."name" = $1 AND "public"."Tag"."type" = $2 AND "public"."Tag"."userId" = $3) OFFSET $4
prisma:query INSERT INTO "public"."Post" ("id","title","userId") VALUES ($1,$2,$3) RETURNING "public"."Post"."id"
prisma:query BEGIN
prisma:query INSERT INTO "public"."Tag" ("id","name","type","userId") VALUES ($1,$2,$3,$4) RETURNING "public"."Tag"."id"
prisma:query INSERT INTO "public"."PostTag" ("postId","tagId") VALUES ($1,$2) RETURNING "public"."PostTag"."postId", "public"."PostTag"."tagId"
prisma:query SELECT "public"."Post"."id" FROM "public"."Post" WHERE "public"."Post"."id" = $1 OFFSET $2
prisma:query SELECT "public"."Post"."id", "public"."Post"."title", "public"."Post"."userId" FROM "public"."Post" WHERE "public"."Post"."id" = $1 LIMIT $2 OFFSET $3
prisma:query INSERT INTO "public"."Post" ("id","title","userId") VALUES ($1,$2,$3) RETURNING "public"."Post"."id"
prisma:query BEGIN
prisma:query SELECT "public"."Post"."id" FROM "public"."Post" WHERE "public"."Post"."id" = $1 OFFSET $2
prisma:query SELECT "public"."Tag"."id" FROM "public"."Tag" WHERE ("public"."Tag"."name" = $1 AND "public"."Tag"."type" = $2 AND "public"."Tag"."userId" = $3) OFFSET $4
prisma:query INSERT INTO "public"."Post" ("id","title","userId") VALUES ($1,$2,$3) RETURNING "public"."Post"."id"
prisma:query SELECT "public"."Tag"."id" FROM "public"."Tag" WHERE ("public"."Tag"."name" = $1 AND "public"."Tag"."type" = $2 AND "public"."Tag"."userId" = $3) OFFSET $4
prisma:query BEGIN
prisma:query SELECT "public"."Post"."id" FROM "public"."Post" WHERE "public"."Post"."id" = $1 OFFSET $2
prisma:query SELECT "public"."Tag"."id" FROM "public"."Tag" WHERE ("public"."Tag"."name" = $1 AND "public"."Tag"."type" = $2 AND "public"."Tag"."userId" = $3) OFFSET $4
prisma:query INSERT INTO "public"."Post" ("id","title","userId") VALUES ($1,$2,$3) RETURNING "public"."Post"."id"
prisma:query BEGIN
prisma:query SELECT "public"."Post"."id" FROM "public"."Post" WHERE "public"."Post"."id" = $1 OFFSET $2
prisma:query INSERT INTO "public"."Post" ("id","title","userId") VALUES ($1,$2,$3) RETURNING "public"."Post"."id"
prisma:query SELECT "public"."Tag"."id" FROM "public"."Tag" WHERE ("public"."Tag"."name" = $1 AND "public"."Tag"."type" = $2 AND "public"."Tag"."userId" = $3) OFFSET $4
prisma:query SELECT "public"."Tag"."id" FROM "public"."Tag" WHERE ("public"."Tag"."name" = $1 AND "public"."Tag"."type" = $2 AND "public"."Tag"."userId" = $3) OFFSET $4
prisma:query COMMIT
prisma:query INSERT INTO "public"."Tag" ("id","name","type","userId") VALUES ($1,$2,$3,$4) RETURNING "public"."Tag"."id"
prisma:query INSERT INTO "public"."Tag" ("id","name","type","userId") VALUES ($1,$2,$3,$4) RETURNING "public"."Tag"."id"
prisma:query INSERT INTO "public"."Tag" ("id","name","type","userId") VALUES ($1,$2,$3,$4) RETURNING "public"."Tag"."id"
prisma:query INSERT INTO "public"."Tag" ("id","name","type","userId") VALUES ($1,$2,$3,$4) RETURNING "public"."Tag"."id"
prisma:query INSERT INTO "public"."Tag" ("id","name","type","userId") VALUES ($1,$2,$3,$4) RETURNING "public"."Tag"."id"
prisma:query ROLLBACK
prisma:query BEGIN
prisma:query ROLLBACK
PrismaClientKnownRequestError3 [PrismaClientKnownRequestError]: 
Invalid `prisma.post.upsert()` invocation:


  Unique constraint failed on the fields: (`name`,`type`,`userId`)
    at cb (C:\PROJECTFOLDER\node_modules\@prisma\client\runtime\index.js:36063:17)
    at processTicksAndRejections (internal/process/task_queues.js:93:5)
    at async Promise.all (index 5) {
  code: 'P2002',
  clientVersion: '2.30.2',
  meta: { target: [ 'name', 'type', 'userId' ] }
}
prisma:query ROLLBACK
prisma:query ROLLBACK
prisma:query BEGIN
prisma:query BEGIN
prisma:query SELECT "public"."Post"."id" FROM "public"."Post" WHERE "public"."Post"."id" = $1 OFFSET $2
prisma:query ROLLBACK
prisma:query INSERT INTO "public"."Tag" ("id","name","type","userId") VALUES ($1,$2,$3,$4) RETURNING "public"."Tag"."id"
prisma:query BEGIN
prisma:query BEGIN
prisma:query SELECT "public"."Post"."id" FROM "public"."Post" WHERE "public"."Post"."id" = $1 OFFSET $2
prisma:query SELECT "public"."Post"."id" FROM "public"."Post" WHERE "public"."Post"."id" = $1 OFFSET $2
prisma:query BEGIN
prisma:query INSERT INTO "public"."Tag" ("id","name","type","userId") VALUES ($1,$2,$3,$4) RETURNING "public"."Tag"."id"
prisma:query SELECT "public"."Post"."id" FROM "public"."Post" WHERE "public"."Post"."id" = $1 OFFSET $2
prisma:query INSERT INTO "public"."Post" ("id","title","userId") VALUES ($1,$2,$3) RETURNING "public"."Post"."id"
prisma:query ROLLBACK

barryhagan · 2021-10-14T14:09:35Z

Related to #8612.

Concurrency control in prisma is a lie.

matthewmueller · 2021-11-03T16:24:08Z

Since it's all in one block, you should be able to stick this in a transaction prisma.$transaction([...])

We do have a problem when you're unable to stick the code in a block and the block is concurrent, see #3242 .

dariusj18 · 2022-04-30T02:22:15Z

So, this must be a fundamental flaw with Prisma and how it works, because I get this error when I run this across multiple requests and the actual upsert is inside a transaction. And the idea that "Hey folks, this is expected behavior" is the response in #3242 is pretty ridiculous.

This was also added as a milestone for 2.19 and seems to have been left behind?

SevInf · 2022-08-31T13:49:42Z

Reproduction: https://github.com/prisma/reproductions/tree/main/gh-9751

pimeys · 2022-09-05T09:30:58Z

Can still reproduce this with 4.3.1. Logs:

https://gist.github.com/pimeys/f3831809ac388a6c9b6ef8a720d8dce9

garrensmith · 2022-11-09T06:40:32Z

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.

chientrm · 2023-03-02T10:35:42Z

Just wrap the upsert with a $transaction:

[settings] = await prisma.$transaction([
  prisma.settings.upsert({
    where: { sub },
    update: {},
    create: { sub },
  }),
]);

garhul · 2023-03-27T15:53:25Z

I'm seeing this issue on transactions as well.

My case scenario is different but not that much, I've got a collection with an unique constraint on a value, but that constraint needs to be case insensitive (this is mongodb btw).
So when I do an upsert I cannot use an insensitive where clause, so I perform a find first and then an upsert, all within a transaction.

The result is duplicates everywhere because prisma doesn't guarantee that other transaction will start execution as my first arriving transaction has started execution, hence data corruption.

I don't believe my case use is so uncommon, what's the recommended course of action to ensure that two commands get executed in an "atomic" way?

janpio · 2023-03-27T20:03:19Z

Open a new issue or discussion please instead of commenting on a closed bug report. Thanks.

chris-activewrite · 2023-05-18T11:25:02Z

For those who are reporting concurrency issues, it would help if you noted what isolation level you're running in. Obviously that has a big effect on concurrency in general, and that needs to be used when reproducing your problems.

dariusj18 added the kind/bug A reported bug. label Oct 13, 2021

janpio added bug/0-unknown Bug is new, does not have information for reproduction or reproduction could not be confirmed. team/client Issue for team Client. topic: upsert nested upsert labels Nov 19, 2021

Jolg42 added tech/engines Issue for tech Engines. topic: query race condition labels Aug 31, 2022

pimeys self-assigned this Sep 5, 2022

pimeys added bug/2-confirmed Bug has been reproduced and confirmed. and removed bug/0-unknown Bug is new, does not have information for reproduction or reproduction could not be confirmed. labels Sep 5, 2022

pimeys removed their assignment Sep 5, 2022

pimeys mentioned this issue Sep 5, 2022

upsert(): Unique constraint on DateTime sqlite #14584

Closed

janpio mentioned this issue Sep 8, 2022

Upsert errors with compound unique key with Date on MySQL: Query ... is required to return data, but found no record(s) #12783

Closed

janpio added this to the 4.5.0 milestone Sep 27, 2022

janpio assigned garrensmith Sep 27, 2022

janpio modified the milestones: 4.5.0, 4.6.0 Oct 18, 2022

janpio closed this as completed Nov 8, 2022

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Concurrent relation upserts conflict #9751

Concurrent relation upserts conflict #9751

dariusj18 commented Oct 13, 2021 •

edited

dariusj18 commented Oct 13, 2021

barryhagan commented Oct 14, 2021

matthewmueller commented Nov 3, 2021

dariusj18 commented Apr 30, 2022

SevInf commented Aug 31, 2022

pimeys commented Sep 5, 2022

garrensmith commented Nov 9, 2022

chientrm commented Mar 2, 2023

garhul commented Mar 27, 2023

janpio commented Mar 27, 2023

chris-activewrite commented May 18, 2023

Concurrent relation upserts conflict #9751

Concurrent relation upserts conflict #9751

Comments

dariusj18 commented Oct 13, 2021 • edited

Bug description

How to reproduce

Expected behavior

Prisma information

Environment & setup

Prisma Version

dariusj18 commented Oct 13, 2021

barryhagan commented Oct 14, 2021

matthewmueller commented Nov 3, 2021

dariusj18 commented Apr 30, 2022

SevInf commented Aug 31, 2022

pimeys commented Sep 5, 2022

garrensmith commented Nov 9, 2022

chientrm commented Mar 2, 2023

garhul commented Mar 27, 2023

janpio commented Mar 27, 2023

chris-activewrite commented May 18, 2023

dariusj18 commented Oct 13, 2021 •

edited