Error with multiple upsert in $transaction

[yujonglee]

Bug description

When I execute query, these two errors alternately appeared. One is ConnectorError and the other is Unique constraint error.

Second error is kind of strange, because I chose this approach(upsert with $transaction) to avoid race condition, but it seams like it is happened here. (I can verify that all names and userIds are unique.)

Maybe this is because some creation in $transaction is not broadcasted to others in $transaction, so creation can happen multiple time. (Now I remember I read something similar in docs)

Anyway, then what is the right way to do what I want? And what should I do for first error message?

Thank you.

Error: 
Invalid `prisma.tag.upsert()` invocation:


  Error occurred during query execution:
ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Server(ServerError { code: 1213, message: "vttablet: rpc error: code = Aborted desc = (errno 1213) (sqlstate 40001) (CallerID: unsecure_grpc_client): Sql: \"insert into Tag(id, createdAt, updatedAt, `name`, userId) values (:v1, :v2, :v3, :v4, :v5)\", BindVars: {}", state: "40001" })) })
    at cb (/Users/yujonglee/dev/taggy.cloud/node_modules/@prisma/client/runtime/index.js:38692:17)
    at async Promise.all (index 0)
    at async createFile (/Users/yujonglee/dev/taggy.cloud/api/_build/index.js:1029:3)
    at async Promise.all (index 1)
    at async action3 (/Users/yujonglee/dev/taggy.cloud/api/_build/index.js:1202:3)
    at async Object.callRouteAction (/Users/yujonglee/dev/taggy.cloud/node_modules/@remix-run/server-runtime/data.js:36:14)
    at async handleDataRequest (/Users/yujonglee/dev/taggy.cloud/node_modules/@remix-run/server-runtime/server.js:110:18)
    at async requestHandler (/Users/yujonglee/dev/taggy.cloud/node_modules/@remix-run/server-runtime/server.js:45:20)
    at async /Users/yujonglee/dev/taggy.cloud/node_modules/@remix-run/express/server.js:45:22

Error: 
Invalid `prisma.tag.upsert()` invocation:


  Unique constraint failed on the (not available)
    at cb (/Users/yujonglee/dev/taggy.cloud/node_modules/@prisma/client/runtime/index.js:38688:17)
    at async Promise.all (index 0)
    at async createFile (/Users/yujonglee/dev/taggy.cloud/api/_build/index.js:1028:3)
    at async Promise.all (index 1)
    at async action3 (/Users/yujonglee/dev/taggy.cloud/api/_build/index.js:1201:3)
    at async Object.callRouteAction (/Users/yujonglee/dev/taggy.cloud/node_modules/@remix-run/server-runtime/data.js:36:14)
    at async handleDataRequest (/Users/yujonglee/dev/taggy.cloud/node_modules/@remix-run/server-runtime/server.js:110:18)
    at async requestHandler (/Users/yujonglee/dev/taggy.cloud/node_modules/@remix-run/server-runtime/server.js:45:20)
    at async /Users/yujonglee/dev/taggy.cloud/node_modules/@remix-run/express/server.js:45:22

How to reproduce

Just run query.

Expected behavior

For all strings in tagNames, create if not exist, in batch.
(tagNames is array of string)

Prisma information

  await prisma.$transaction(tagNames.map((name) => (
    prisma.tag.upsert({
      where: {
        userId_name: {
          userId,
          name,
        },
      },
      create: {
        userId,
        name,
      },
      update: {},
    })
  )));

model Tag {
  id        String   @id @default(uuid())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  name String

  User   User   @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId String

  @@unique(fields: [userId, name])
}

Environment & setup

• OS: Mac OS
• Database: PlanetScale
• Node.js version: 16.6.1

Prisma Version

prisma                  : 3.7.0
@prisma/client          : 3.7.0
Current platform        : darwin
Query Engine (Node-API) : libquery-engine 8746e055198f517658c08a0c426c7eec87f5a85f (at node_modules/@prisma/engines/libquery_engine-darwin.dylib.node)
Migration Engine        : migration-engine-cli 8746e055198f517658c08a0c426c7eec87f5a85f (at node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine    : introspection-core 8746e055198f517658c08a0c426c7eec87f5a85f (at node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary           : prisma-fmt 8746e055198f517658c08a0c426c7eec87f5a85f (at node_modules/@prisma/engines/prisma-fmt-darwin)
Default Engines Hash    : 8746e055198f517658c08a0c426c7eec87f5a85f
Studio                  : 0.445.0
Preview Features        : referentialIntegrity

[hyochan]

Did you try to create a row like below instead of direct insertion?

create: {
  user: {
    connect: {
      id: userId
    }
  }
},

I found this earlier that Prisma won't naturally join the foreign key in such cases without connect. Would be great to organize this in an issue or document.

@yujong-lee I hope you try this out first and come back again.

[yujonglee]

@hyochan Thanks for your help. But I didn't work for me.

  await prisma.$transaction(tagNames.map((name) => (
    prisma.tag.upsert({
      where: {
        userId_name: {
          userId,
          name,
        },
      },
      create: {
        User: {
          connect: {
            id: userId,
          },
        },
        name,
      },
      update: {},
    })
  )));

I changed query like above, but I got this error.

Error: 
Invalid `prisma.tag.upsert()` invocation:


  An operation failed because it depends on one or more records that were required but not found. No 'User' record(s) (needed to inline the relation on 'Tag' record(s)) was found for a nested connect on one-to-many relation 'TagToUser'.
    at cb (/Users/yujonglee/dev/taggy.cloud/node_modules/@prisma/client/runtime/index.js:38688:17)
    at async Promise.all (index 0)
    at async createFile (/Users/yujonglee/dev/taggy.cloud/api/_build/index.js:1028:3)
    at async Promise.all (index 0)
    at async action3 (/Users/yujonglee/dev/taggy.cloud/api/_build/index.js:1225:3)
    at async Object.callRouteAction (/Users/yujonglee/dev/taggy.cloud/node_modules/@remix-run/server-runtime/data.js:36:14)
    at async handleDataRequest (/Users/yujonglee/dev/taggy.cloud/node_modules/@remix-run/server-runtime/server.js:110:18)
    at async requestHandler (/Users/yujonglee/dev/taggy.cloud/node_modules/@remix-run/server-runtime/server.js:45:20)
    at async /Users/yujonglee/dev/taggy.cloud/node_modules/@remix-run/express/server.js:45:22

[hyochan]

@hyochan Thanks for your help. But I didn't work for me.

  await prisma.$transaction(tagNames.map((name) => (
    prisma.tag.upsert({
      where: {
        userId_name: {
          userId,
          name,
        },
      },
      create: {
        User: {
          connect: {
            id: userId,
          },
        },
        name,
      },
      update: {},
    })
  )));

I changed query like above, but I got this error.

Error: 
Invalid `prisma.tag.upsert()` invocation:


  An operation failed because it depends on one or more records that were required but not found. No 'User' record(s) (needed to inline the relation on 'Tag' record(s)) was found for a nested connect on one-to-many relation 'TagToUser'.
    at cb (/Users/yujonglee/dev/taggy.cloud/node_modules/@prisma/client/runtime/index.js:38688:17)
    at async Promise.all (index 0)
    at async createFile (/Users/yujonglee/dev/taggy.cloud/api/_build/index.js:1028:3)
    at async Promise.all (index 0)
    at async action3 (/Users/yujonglee/dev/taggy.cloud/api/_build/index.js:1225:3)
    at async Object.callRouteAction (/Users/yujonglee/dev/taggy.cloud/node_modules/@remix-run/server-runtime/data.js:36:14)
    at async handleDataRequest (/Users/yujonglee/dev/taggy.cloud/node_modules/@remix-run/server-runtime/server.js:110:18)
    at async requestHandler (/Users/yujonglee/dev/taggy.cloud/node_modules/@remix-run/server-runtime/server.js:45:20)
    at async /Users/yujonglee/dev/taggy.cloud/node_modules/@remix-run/express/server.js:45:22

The error is telling that there is no corresponding User record.

Try connectOrCreate.

[yujonglee]

@hyochan I understand that's what the error message is saying, but obviously, user exists. Because that code can be only executed when user is logged in.

Maybe I should do some debugging.

Anyway, thank you for responding.

[pantharshit00]

@yujong-lee If you can still reproduce this, please share a small script with enough data for reproduction. That helps a ton.

[yujonglee]

@pantharshit00 I will do my best to reproduce this in minimal example and share it.

[pantharshit00]

I am going to close this as its been 3 months now and there is no activity here.

Please reply if you want us to take another look.

We also did many fixes since so this might be fixed in the latest version of Prisma.