upsert across HTTP requests has a race condition

[henrikuper]

Bug description

Situation: I have a model with a unique string.
At the beginning I have an empty database. I want to create or update an object of the model with upsert and check if the object exists by searching for the unique string.

If I call several upserts with the same unique string at the same time, I expect it to be created once and then updated if necessary. But it is created once and then the error occurs: Unique constraint failed on the fields.

Unique constraint failed on the fields: (`testId`).

How to reproduce

Expected behavior

I expect it to be created once and then updated the just created object, instead of crashing.

Prisma information

model Test {
  id Int      @default(autoincrement()) @id
  testId     String   @unique
}
const prismaClient = new PrismaClient({
  log: ["info", "warn", "error"],
});

const upsertPromises: Promise<Test>[] = [];
for (let i = 0; i < 2; i++) {
  upsertPromises.push(
    prismaClient.test.upsert({
      where: { testId: "testId" },
      create: {
        testId: "testId",
      },
      update: {},
    })
  );
}
await Promise.all(upsertPromises);

Environment & setup

• OS: Mac OS,
• Database: PostgreSQL
• Node.js version: v13.3.0
• Prisma version: 2.3.0

[pantharshit00]

Hey I am unable to reproduce this in 2.4.1.

Can you please try again with that version once?

[henrikuper]

Hi I tested it with 2.4.1 The error still occurs on my machine.

  Unique constraint failed on the fields: (`testId`)
    at PrismaClientFetcher.request (.../node_modules/@prisma/client/src/runtime/getPrismaClient.ts:1219:15)
    at runMicrotasks (<anonymous>)
    at processTicksAndRejections (internal/process/task_queues.js:93:5)
    at async Promise.all (index 1)
`

[pantharshit00]

@henrikuper Can you please try again with 2.5.1?

[uncvrd]

For what it's worth, I ran in to this error when my sequence primary key id's were out of sync with the data in the table. I ran the following to see if each primary key id was correct, if not, I updated it...not sure if this'll fix your problem but here's what I did:

-- Login to psql and run the following

-- What is the result?
SELECT MAX(id) FROM "Token";

-- Then run...
-- This should be higher than the last result.
SELECT nextval('"Token_id_seq"');

-- If it's not higher... run this set the sequence last to your highest id. 
-- (wise to run a quick pg_dump first...)

BEGIN;
-- protect against concurrent inserts while you update the counter
LOCK TABLE "Token" IN EXCLUSIVE MODE;
-- Update the sequence
SELECT setval('"Token_id_seq"', COALESCE((SELECT MAX(id)+1 FROM "Token"), 1), false);
COMMIT;

[pantharshit00]

Hmm, that is quite interesting @uncvrd

Do you have any idea on what caused this for your case in the first place?

[uncvrd]

@pantharshit00 hey - so if I remember correctly, I deleted all items in my database (all the tables but didn't delete the database itself) and ran a migration to add all the same tables back, so I guess when running an upsert, the sequence ids from the old table did not reset. Hope that helps!

[guillaumeLamanda]

Hi there!

I also hit this issue. I can't create a model instance with upsert when where have no property.
I made a small reproduction issue on CodeSandbox.
https://codesandbox.io/s/prisma-upsert-reproduction-2tq0t?file=/src/index.js

This reproduction uses the latest version of prisma (2.13.0) and sqlite.

Hope that helps!

[pantharshit00]

@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

[Dremora]

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.

[pantharshit00]

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

[Dremora]

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.

[matthewmueller]

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');

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

[henrikuper]

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.

[matthewmueller]

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.

[janpio]

@Noor0 That case is probably better represented in #9678
@elie222 @Dremora Are you aware of any issues that spell out this problem with examples of connectOrCreate usage?

[janpio]

Ping @elie222 @Dremora Any connectOrCreate examples would be super helpful for us. Thanks.

[Gerrit-K]

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.

[dtinth]

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:

• Add a remark about upsert’s race conditions docs#3910

[dickfickling]

🙌

[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.

[t0ggah]

@garrensmith: Great that it's documented better. We have the case(which I see the example also have), that we have unique constraint with the combination of two fields.
Would a where clause combining these two use native or prisma upsert? E.g.

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

[garrensmith]

@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: {
    ...
  },
})

[rothfels]

@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

[rothfels]

@janpio are database upserts for MySQL on the product roadmap? Referring to those using ON DUPLICATE KEY (not the current prisma upsert functionality). Thanks.

[janpio]

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 👍

[calvinl]

@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?

[felipap]

Hi @garrensmith, I believe the following query satisfies the requirements to use a native upsert, but it's not.

const schema = await db.destinationSchema.upsert({
  where: {
    destinationId: dest.id,
  },
  create: {
    destinationId: dest.id,
    envId: dest.envId,
    engine: dest.engine,
    projectId: dest.projectId,
  },
  update: {},
})

Logs show a multi-stage upsert taking place:

prisma:query BEGIN
prisma:query SELECT "public"."DestinationSchema"."id" FROM "public"."DestinationSchema" WHERE ("public"."DestinationSchema"."id" = $1 AND 1=1) OFFSET $2
prisma:query INSERT INTO "public"."DestinationSchema" ("id","createdAt","updatedAt","destinationId","engine","envId","projectId") VALUES ($1,$2,$3,$4,$5,$6,$7) RETURNING "public"."DestinationSchema"."id"
prisma:query SELECT "public"."DestinationSchema"."id", "public"."DestinationSchema"."createdAt", "public"."DestinationSchema"."updatedAt", "public"."DestinationSchema"."destinationId", "public"."DestinationSchema"."value", "public"."DestinationSchema"."initializedAt", "public"."DestinationSchema"."engine", "public"."DestinationSchema"."envId", "public"."DestinationSchema"."projectId" FROM "public"."DestinationSchema" WHERE "public"."DestinationSchema"."id" = $1 LIMIT $2 OFFSET $3
prisma:query COMMIT

Am I missing something?

• You use Prisma version 4.6.0 or later => Yes, 4.16.
• Your application uses a CockroachDB, PostgreSQL, or SQLite data source => Yes, postgres.
• The query does not include a selection that uses a nested read => true
• The query modifies only one model => true
• There is only one unique field in the upsert's where option => true, destinationId
• The unique field in the where option and the unique field in the create option have the same value => true

[janpio]

Can you please open a new issue @felipap? We'll be happy to reproduce and look into that. Thanks!

[rothfels]

@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.

[bartoszhernas]

Was this issue closed because it was fixed, if so which version? As @rothfels mentioned, in case the update is empty the native upsert is not used which is not what the docs require for native upsert to be used.

[janpio]

Then please open a new issue and describe the full situation. Thanks.