prisma: upsert across HTTP requests has a race condition

Should Prisma’s upsert not being a real upsert be highlighted by the documentation? Or alternatively the method renamed to create_or_update like in ActiveRecord. 🤔 It is quite the blocker when you start getting unique constraint errors from simple upserts.

Prisma’s data guide pages even describe the real upsert so Google results can get quite confusing.

Who though that doing completely opposite what the DataGuide recommends is a good choice?

I understand rationale for supporting other mechanism that do not support native UPSERT, but then just make it clear in Docs, and underneath use whatever is best suited for each type of DB. You are using ForeignKeys in Postgres, even though MongoDB doesn’t have it right?

Prisma’s Data Guide Learn how databases work, how to choose the right one, and how to use databases with your applications to their full potential.

Ok, so let me learn, but then do NOT allow me to use the full potential of the DB, at the same time misleading me of what upsert does (dataguide says to use prisma’s upsert as example of how to use postgre’s UPSERT, which is a lie). What gives? You are teaching people to do X, and at the same time you do not do the X. You even make it hard/impossible to do the correct thing while using Prisma 😦

Thanks @henrikuper, that’s a great point about upsert across requests being racy.

Highlighting @Dremora’s SQL to show that if two requests come in at the same time trying to update the same keys, you can end up with a unique constraint violation.

prisma:query BEGIN
prisma:query BEGIN
prisma:query SELECT "public"."items"."id" FROM "public"."items" WHERE ("public"."items"."unique_index_field_1" = $1 AND "public"."items"."unique_index_field_2" = $2) OFFSET $3
prisma:query SELECT "public"."items"."id" FROM "public"."items" WHERE ("public"."items"."unique_index_field_1" = $1 AND "public"."items"."unique_index_field_2" = $2) OFFSET $3
prisma:query INSERT INTO "public"."items" ("unique_index_field_1","unique_index_field_2") VALUES ($1,$25) RETURNING "public"."items"."id"
prisma:query SELECT "public"."items"."id","public"."items"."unique_index_field_1", "public"."items"."unique_index_field_2", FROM "public"."items" WHERE "public"."items"."id" = $1 LIMIT $2 OFFSET $3
prisma:query COMMIT
prisma:query INSERT INTO "public"."items" ("unique_index_field_1","unique_index_field_2") VALUES ($1,$2) RETURNING "public"."items"."id"
prisma:query ROLLBACK

This happens in two transactions (T1 & T2) running at the same time.

• T1 looks up the email address alice@prisma.io. Not found, we’re creating.
• T2 looks up the email address alice@prisma.io. Not found, we’re creating.
• T1 inserts alice@prisma.io into the DB. All good.
• T2 inserts alice@prisma.io into the DB. Unique constraint violation. Rollback.

The solution is to use the upsert capabilities of the database itself, which turn these separate operations into one operation:

• T1 looks up the email address alice@prisma.io. Not found. Insert alice@prisma.io into the DB. All good.
• T2 looks up the email address alice@prisma.io. Found. Update alice@prisma.io into the DB. All good.

You may wonder why the second example finds Alice this time. I believe this is because operations that can write to the table need to be serialized. Since we know that this operation is an upsert, T2 will wait until T1 releases the lock on the table.

I’m also experiencing this issue when attempting to idempotently create records:

const findOrCreateSubscription = ({ stripeSubscriptionId, ...rest }) => {
  subscription = await prisma.subscription.upsert({
    where: {
      stripeSubscriptionId,
    },
    create: {
      stripeSubscriptionId,
      ...rest,
    },
    update: {
      ...rest,
    },
  });
}

As others have stated if you call this multiple times the first call will succeed and subsequent calls will fail:

await Promise.all([
  findOrCreateSubscription(event.data.object),
  findOrCreateSubscription(event.data.object),
])

Thanks @matthewmueller for the three ways workarounds, which sound very good if you have one of the three cases.

Actually I have a 4. alternative which causes this error, which can not be solved with one of these three solutions.

-> api endpoint is called twice at the exact same time with the same unique key.

This happens nearly never in reality but theoretically it can happen. Both calls trigger the upsert function with the same unique key. Now the race condition read read create create happens and an error is thrown.

I solved the issue with a dirty hack by

1. catching the error.
2. then the catch function calls the upsert function again.

This doesn’t completely solve the issue, but reduces the probability of occurrence enough that there are no problems in the real application.

I mentioned data guide because directly underneath the information about how to use/code ON CONFLICT in Postgres clause there is a mention

Note: If you are connecting to your database with Prisma client, you can perform upsert operations using the dedicated upsert operation.

which links to prisms docs. It is highly misleading but I still think prisma in Postgres should just use ON CONFLICT clause. Just as data guide suggest.

Hey folks, this is expected behavior.

If you issue two upserts concurrently in the Prisma Client, under the hood we do a read, then a write. This is to support data sources that don’t have native upsert support.

Unfortunately, this means that your concurrent queries have a race condition and may look like this: read-read-create-create, and in this case, the second create fails the constraint.

A couple workarounds:

1. It looks like you’re just trying to create many records at once anyway, so you can use our createMany API with skipDuplicates: true. Under the hood this does an ON CONFLICT DO NOTHING in Postgres.

2. You can also use upsert with await:

await addItem('foo', 'bar');
await addItem('foo', 'bar');

1. Finally you can upvote the upsertMany issue. Community interest is one of the primary drives of features 🙂

@Dremora I can reproduce the above issue. We should fix that.

I got bitten by this too. I submitted a documentation change to add the following remark to upsert’s API docs:

Prisma performs the upsert first executing a SELECT query to check if the record exists, followed by an INSERT or UPDATE query depending on the result of the SELECT query. Since it does not use the ON CONFLICT or the ON DUPLICATE KEY clause, two simultaneous upserts with the same UNIQUE key can result in a “Unique constraint failed” error. It is your application’s responsibility to handle the P2002 error and retry the upsert.

PR:

• https://github.com/prisma/docs/pull/3910

@garrensmith thanks for adding real upserts – super helpful. Any reason you’re not supporting MySQL ON DUPLICATE KEY, or plans to in the future? We (MySQL users) want true upserts too, it shouldn’t be much different than ON CONFLICT

We’re running into this as well, with more or less exactly the case described here, where a user causes two nearly simultaneous http requests, which both attempt to “upsert” a user.

What’s especially bugging me is that we already have a retry middleware in place, but that simply keeps retrying the underlying “create”, hence always failing.

Our current workaround is to await these calls, catch any conflicts and (if there occurs one) fetch and return the persisted user instead. However, this doesn’t sound like something a framework user should need to implement for an upsert.

👋 Any update here? I also ended up hacking together a try-catch solution like @henrikuper but it’s very gross.

I’m not an expert in SQL, but below are potential solutions. One would be to use INSERT INTO ... ON CONFLICT statement, that is basically an upsert. It only exists in Postgres. UPSERT can be used in MySQL instead.

An example from https://www.postgresqltutorial.com/postgresql-upsert/:

INSERT INTO customers (name, email)
VALUES('Microsoft','hotline@microsoft.com') 
ON CONFLICT (name) 
DO 
   UPDATE SET email = EXCLUDED.email || ';' || customers.email;

Any unique index key can be used in the ON CONFLICT clause.

Alternatively, database table would have to be locked using ACCESS EXCLUSIVE for reads before doing the first SELECT. This would ensure that the SELECT/INSERT pair is atomic. However, locking the whole table is not great for performance.

Experiencing the same problem with a compound key… I too believe something should be done about it. Having to use the dirty try/catch hack as the only solution is very lame 😦

Pretty annoying issue. I run into it with connectOrCreate. The issue is that the parent record isn’t created either. I could just ignore the error for the related record as it’s already been added. But it causes the entire operation to fail which I can’t have 😦

@felipap I discovered when the update is empty the native upsert is not used, even if the rest of the rules are followed. This is an odd choice (or just a bug) but you can work around it by adding destinationId: dest.id, to the update.

@t0ggah yes if your create has the two fields defined defined in the where clause and they are the same

prisma.User.upsert({
  where: {
   userName_profileViews: {
      userName: 'Alice',
      profileViews: 1,
    }
  },
  create: {
    userName: 'Alice',
     profileViews: 1,
  },
  update: {
    ...
  },
})

@garrensmith – this does not seem to be the case when using a multicolumn unique index. I have the same fields in both where and create, like so:

      const post = await prisma.post.upsert({
        create: {
          name: 'Alice',
          amount: 100
        },
        update: {},
        where: {
          name_amount: {
            name: 'Alice',
            amount: 100
          }
        }
      });

Outputting the logs to console, it’s doing a prisma client read, then insert rather than using ON CONFLICT. The docs say there must be only one unique field, but it doesn’t clarify whether a multicolumn unique index works as to use the native upsert. Could you clarify this?

I fear that dropped between the other work. Can you open a feature request for this? Generally I see no reason why we should not also do similar changes/optimizations (if I am allowed to call them that) for MySQL 👍

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.

@Noor0 That case is probably better represented in https://github.com/prisma/prisma/issues/9678 @elie222 @Dremora Are you aware of any issues that spell out this problem with examples of connectOrCreate usage?

I’m having a similar issue, both with upsert and connectOrCreate. Prisma 2.15.0. In both cases, the key I’m using in the where clause is a unique index composed of two columns. I’m manually populating both values in the create clause (so they are not autoincremented values generated by the database). Of course in the real world the values are dynamic, but we had cases where the same set of values gets sent into our system nearly simultaneously.

Here’s an example schema, code and logs (table and field names have been redacted). Even though this is upsert only (it’s simpler), connectOrCreate generates similar transactions and queries, and results in the same issue.

model Item {
  id                      Int                   @id @default(autoincrement())
  uniqueIndexField1       String                @map("unique_index_field_1")
  uniqueIndexField2       String                @map("unique_index_field_2")

  @@unique([uniqueIndexField1, uniqueIndexField2], name: "unique_index_field_1_unique_index_field_2_unique")
  @@map("items")
}

async function addItem(field1, field2) {
  await prisma.item.upsert({
    where: {
      unique_index_field_1_unique_index_field_2_unique: {
        uniqueIndexField1: field1,
        uniqueIndexField2: field2
      }
    },
    create: {
      uniqueIndexField1: field1,
      uniqueIndexField2: field2
    },
    update: {}
  });
}

addItem('foo', 'bar');
addItem('foo', 'bar');

prisma:query BEGIN
prisma:query BEGIN
prisma:query SELECT "public"."items"."id" FROM "public"."items" WHERE ("public"."items"."unique_index_field_1" = $1 AND "public"."items"."unique_index_field_2" = $2) OFFSET $3
prisma:query SELECT "public"."items"."id" FROM "public"."items" WHERE ("public"."items"."unique_index_field_1" = $1 AND "public"."items"."unique_index_field_2" = $2) OFFSET $3
prisma:query INSERT INTO "public"."items" ("unique_index_field_1","unique_index_field_2") VALUES ($1,$25) RETURNING "public"."items"."id"
prisma:query SELECT "public"."items"."id","public"."items"."unique_index_field_1", "public"."items"."unique_index_field_2", FROM "public"."items" WHERE "public"."items"."id" = $1 LIMIT $2 OFFSET $3
prisma:query COMMIT
prisma:query INSERT INTO "public"."items" ("unique_index_field_1","unique_index_field_2") VALUES ($1,$2) RETURNING "public"."items"."id"
prisma:query ROLLBACK

(node:57819) UnhandledPromiseRejectionWarning: Error: 
Invalid `prisma.item.upsert()` invocation:


  Unique constraint failed on the fields: (`unique_index_field_1`,`unique_index_field_2`)
    at PrismaClientFetcher.request (node_modules/@prisma/client/runtime/index.js:78455:15)
    at processTicksAndRejections (internal/process/task_queues.js:97:5)

If you look at the logs, it becomes obvious what’s happening. Two transactions start nearly simultaneously, both query items for a particular set of values, both determine that a new item needs to be created. So far database operations have been able to run in parallel. Then transaction 1 tries to create a new item in the database, transaction 2 has to wait. Once transaction 1 successfully commits, transaction 2 tries to create the same item - and fails.

Interestingly enough, we had similar code before with Knex, using hand-written transactions, and this rare-to-reproduce bug was present there as well.

@guillaumeLamanda

I checked your reproduction and the output is expected. Since id property is not defined in the object that you are passing, it is undefined in javascript terms. If you pass undefined as a value to the client, it will omit that field: https://www.prisma.io/docs/concepts/components/prisma-client/null-and-undefined