prisma: `updateMany()` causes lost-updates

Do we really need a repro to see why executing these two SQL statements is prone to a race condition?

prisma:query SELECT "public"."Seat"."id" FROM "public"."Seat" WHERE ("public"."Seat"."id" = $1 AND "public"."Seat"."version" = $2)
prisma:query UPDATE "public"."Seat" SET "claimedBy" = $1, "version" = ("version" + $2) WHERE "public"."Seat"."id" IN ($3)

You need to fix this by including all where clauses in the UPDATE statement:

UPDATE "public"."Seat" SET "claimedBy" = $1, "version" = ("version" + $2) WHERE ("public"."Seat"."id" IN ($3) AND "public"."Seat"."version" = $4)

updateMany() is currently not suitable for optimistic concurrency control, but that is what the docs claim.

This is fixed and will be in the next Prisma release 🎉

We do not have a confirmed reproduction yet. If you can help us get to that, please post it below. Thanks.

Any update on fixing this @matthewmueller?

I just tested it out and there is indeed a race condition here due to updateMany doing a select before update. In the example below, you’ll see “BOOKED” twice across 3 concurrent requests, where it should only appear once.

As @barryhagan , the problem is that updateMany performs 2 queries:

prisma:query BEGIN
prisma:query SELECT "public"."Seat"."id" FROM "public"."Seat" WHERE ("public"."Seat"."id" = $1 AND "public"."Seat"."version" = $2)
prisma:query UPDATE "public"."Seat" SET "userId" = $1, "version" = ("version" + $2) WHERE "public"."Seat"."id" IN ($3)
prisma:query COMMIT

Even if it’s inside a transaction, there can still be a race condition because by default selects don’t wait for each other, even inside a transaction. This post goes into further details: https://www.2ndquadrant.com/en/blog/postgresql-anti-patterns-read-modify-write-cycles/

We need to fix this. In the meantime, I’d suggest implementing one of the solutions inside this blog post using $queryRaw.

Sorry for the misguided information and inconvenience. I’ll be temporarily adjusting the guide to make note of this issue.

schema.prisma

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

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

generator client {
  provider = "prisma-client-js"
}

model User {
  id   Int   @id
  Seat Seat?
}

model Seat {
  id        Int    @id @default(autoincrement())
  movie     String @unique
  userId    Int?   @unique
  claimedBy User?  @relation(fields: [userId], references: [id])
  version   Int    @default(0)

  @@unique([id, version])
}

index.ts

import { PrismaClient } from "@prisma/client"

const client = new PrismaClient({
  log: ["query"],
})

async function book(userId: number, movieName: string) {
  const availableSeat = await client.seat.findFirst({
    where: {
      movie: movieName,
      claimedBy: null,
    },
  })

  if (!availableSeat) {
    throw new Error(`Oh no! ${movieName} is all booked.`)
  }

  console.log("BOOKING user=%s version=%s", userId, availableSeat?.version)

  const seat = await client.seat.updateMany({
    data: {
      userId: userId,
      version: {
        increment: 1,
      },
    },
    where: {
      id: availableSeat.id,
      version: availableSeat.version,
    },
  })

  if (seat.count === 0) {
    throw new Error(`That seat is already booked! Please try again.`)
  }
  console.log("BOOKED user=%s seat_count=%s", userId, seat.count)
}

async function main() {
  const movieName = "Hidden Figures"

  await client.seat.deleteMany({})
  await client.user.deleteMany({})

  const seat = await client.seat.create({
    data: {
      movie: movieName,
    },
  })

  // user 1
  const user1 = await client.user.create({
    data: {
      id: 1,
    },
  })
  // user 2
  const user2 = await client.user.create({
    data: {
      id: 2,
    },
  })
  // user 3
  const user3 = await client.user.create({
    data: {
      id: 3,
    },
  })

  await Promise.all([
    book(user1.id, movieName),
    book(user2.id, movieName),
    book(user3.id, movieName),
  ])
}

main()
  .catch((err) => console.log(err))
  .finally(() => {
    client.$disconnect()
  })

Logs

prisma:query BEGIN
prisma:query SELECT "public"."Seat"."id" FROM "public"."Seat" WHERE 1=1 OFFSET $1
prisma:query SELECT "public"."Seat"."id" FROM "public"."Seat" WHERE 1=1
prisma:query DELETE FROM "public"."Seat" WHERE "public"."Seat"."id" IN ($1)
prisma:query COMMIT
prisma:query BEGIN
prisma:query SELECT "public"."User"."id" FROM "public"."User" WHERE 1=1 OFFSET $1
prisma:query SELECT "public"."User"."id" FROM "public"."User" WHERE 1=1
prisma:query DELETE FROM "public"."User" WHERE "public"."User"."id" IN ($1,$2,$3)
prisma:query COMMIT
prisma:query BEGIN
prisma:query INSERT INTO "public"."Seat" ("movie","version") VALUES ($1,$2) RETURNING "public"."Seat"."id"
prisma:query SELECT "public"."Seat"."id", "public"."Seat"."movie", "public"."Seat"."userId", "public"."Seat"."version" FROM "public"."Seat" WHERE "public"."Seat"."id" = $1 LIMIT $2 OFFSET $3
prisma:query COMMIT
prisma:query BEGIN
prisma:query INSERT INTO "public"."User" ("id") VALUES ($1) RETURNING "public"."User"."id"
prisma:query SELECT "public"."User"."id" FROM "public"."User" WHERE "public"."User"."id" = $1 LIMIT $2 OFFSET $3
prisma:query COMMIT
prisma:query BEGIN
prisma:query INSERT INTO "public"."User" ("id") VALUES ($1) RETURNING "public"."User"."id"
prisma:query SELECT "public"."User"."id" FROM "public"."User" WHERE "public"."User"."id" = $1 LIMIT $2 OFFSET $3
prisma:query COMMIT
prisma:query BEGIN
prisma:query INSERT INTO "public"."User" ("id") VALUES ($1) RETURNING "public"."User"."id"
prisma:query SELECT "public"."User"."id" FROM "public"."User" WHERE "public"."User"."id" = $1 LIMIT $2 OFFSET $3
prisma:query COMMIT
prisma:query SELECT "public"."Seat"."id", "public"."Seat"."movie", "public"."Seat"."userId", "public"."Seat"."version" FROM "public"."Seat" WHERE ("public"."Seat"."movie" = $1 AND "public"."Seat"."userId" IS NULL) LIMIT $2 OFFSET $3
BOOKING user=2 version=0
prisma:query SELECT "public"."Seat"."id", "public"."Seat"."movie", "public"."Seat"."userId", "public"."Seat"."version" FROM "public"."Seat" WHERE ("public"."Seat"."movie" = $1 AND "public"."Seat"."userId" IS NULL) LIMIT $2 OFFSET $3
BOOKING user=3 version=0
prisma:query BEGIN
prisma:query BEGIN
prisma:query SELECT "public"."Seat"."id" FROM "public"."Seat" WHERE ("public"."Seat"."id" = $1 AND "public"."Seat"."version" = $2)
prisma:query SELECT "public"."Seat"."id" FROM "public"."Seat" WHERE ("public"."Seat"."id" = $1 AND "public"."Seat"."version" = $2)
prisma:query UPDATE "public"."Seat" SET "userId" = $1, "version" = ("version" + $2) WHERE "public"."Seat"."id" IN ($3)
prisma:query COMMIT
BOOKED user=3 seat_count=1
prisma:query SELECT "public"."Seat"."id", "public"."Seat"."movie", "public"."Seat"."userId", "public"."Seat"."version" FROM "public"."Seat" WHERE ("public"."Seat"."movie" = $1 AND "public"."Seat"."userId" IS NULL) LIMIT $2 OFFSET $3
Error: Oh no! Hidden Figures is all booked.
    at book (/Users/m/dev/src/github.com/prisma/transaction-guide/index.ts:16:11)
    at async Promise.all (index 0)
    at main (/Users/m/dev/src/github.com/prisma/transaction-guide/index.ts:71:3)
BOOKED user=2 seat_count=1
prisma:query UPDATE "public"."Seat" SET "userId" = $1, "version" = ("version" + $2) WHERE "public"."Seat"."id" IN ($3)
prisma:query COMMIT

@renecouto thanks for the interest in working on this but the Prisma team will be working on this quite soon.

OCC is not only broken in updateMany/deleteMany but also update/delete in unique key situation.

It seams update/delete are like updateMany/deleteMany to select for primary key first then update/delete by only the primary key.

Setting the transaction isolation level is now supported in Prisma 4.2.0. While it’s not an actual fix to this problem, I was able to get this script to pass using serializable transaction isolation levels in a PostgreSQL database.

This issue still needs to be fixed, but wanted to mention this for anyone who has been wanting a fix for this for awhile and uses a database that supports serializable transactions.

import { PrismaClient } from '@prisma/client';

const client = new PrismaClient();

async function bookSeat(userId: string) {
  return await client.$transaction(
    async (transaction) => {
      const availableSeat = await transaction.seat.findFirst({
        where: {
          claimedBy: null,
        },
        orderBy: {
          id: 'asc',
        }
      });

      if (!availableSeat) {
        throw new Error(`Oh no! All seats are already booked.`);
      }

      const { count } = await transaction.seat.updateMany({
        data: {
          claimedBy: userId,
          version: {
            increment: 1,
          },
        },
        where: {
          id: availableSeat.id,
          version: availableSeat.version, // This version field is the key; only claim seat if in-memory version matches database version, indicating that the field has not been updated
        },
      });

      return count;
    },
    {
      isolationLevel: 'Serializable',
    },
  );
}

async function demonstrateLostUpdate() {
  if (process.argv[2] === 'createData') {
    await client.seat.deleteMany();
    for (let i = 0; i < 1000; i++) {
      await client.seat.create({
        data: {
          id: i,
          version: 0,
          movieId: 1,
          claimedBy: null,
        },
      });
    }
    process.exit();
  }

  const userId = process.argv[2];

  let updatedCount = 0;
  for (let i = 0; i < 1000; i++) {
    try {
      updatedCount += await bookSeat(userId);
    } catch {
      // ignore lock failure
    }
  }

  // Detect lost-updates
  const actualCount = await client.seat.count({
    where: {
      claimedBy: userId,
    },
  });
  console.log({
    userId,
    updatedCountByUpdateMany: updatedCount,
    actualUpdatedCount: actualCount,
  });
  process.exit();
}

demonstrateLostUpdate();

I’m still seeing this in prisma 4.5.0; it lists a bunch of primary keys in the query log whenever I do updateMany, even inside an interactive transaction. It’s resulting in the rows being overwritten multiple times (only the last one ‘wins’ and all of them think they succeeded and don’t cause the other transaction to roll back) if I fire off multiple of those transactions quickly in succession (by hitting my Next.js endpoint that does it with 10 simultaneous requests).

prisma:query UPDATE `<db>`.`<table>` SET `<update field>` = ? WHERE (<model id fields>) IN ((?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?)) AND 1=1)```

I think a workaround might be a unique field rowUpdateId using cuid() (better for indexing? ) or uuid() besides the normal id of the row. You then can use update with where: { rowUpdateId: myPreviousId } and update it via client generated cuid() which should be collision resistant. Or would this still generate a SELECT id ... -> UPDATE ... WHERE id instead of UPDATE ... WHERE rowUpdateId?

The advantage is, it will also throw an error so it can be used with transactions to support rollback.

Disadvantages might be: increased storage needs. It might be more complex or confusing to use (especially for new developers). You need to use an additional index to improve the lookups.

I think the use of cuid() is robust enough to not generate duplicates, even in serverless where a timestamp and internal counter could overlap. Because it also uses a client fingerprint (by pid and hostname) it would still be different in diffrent containers/cloud functions. Where uuid() might have the chance of overlaps. Also cuid() is optimized for binary searches and therefore better suited to use as an index.

If I am missing something or this won’t work please let me know.

Is there any guidance on when this may be fixed?

In particular update with unique compound key is more desired over updateMany due to the limitation of nested writes on latter.

In the meantime dropping to raw queries isn’t really a great option for us as we have a generic [DDD] Aggregate OCC versioning solution and this effectively means we can’t use the Prisma client anywhere (safely). We believe temporarily dropping into interactive transactions is the best bet for now but have not fully explored how locking might work here and the effect on performance (not a great concern for now).