1

I have a problem. I don't know if I use it wrong or there is a problem Under concurrency, optimistic locks implemented with updateMany will have overwrite writes Thank u for your help~
address

Simple Test

Optimistic Concurrency Control pattern case code(little change) from documentation
code demo

schema

model Seat {
    id        Int     @id @default(autoincrement())
    claimedBy String?
    movieId   Int
    movie     String   
    version   Int
    @@map("seat")
}

code

async function testBuy(userName:string){
  const movieName = 'fly'
  
  // Find the first available seat
  // availableSeat.version might be 0
  const availableSeat = await prisma.seat.findFirst({
    where: {
      movie: movieName,
      claimedBy: null,
    },
  })
  
  if (!availableSeat) {
    console.log('seat is zero')
    return
  }
  
  //test1 and test3 use this
   const seats = await prisma.seat.updateMany({
     data: {
       claimedBy: userName,
       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
     },
   })

   if (seats.count === 0) {
     console.log('xxxxx count = 0')
   }
  
  //test2 and test4 use this
  //const seats = await prisma.$executeRaw`update seat set claimedBy = ${userName},version =version+1 where id = ${availableSeat.id} and version = ${availableSeat.version};`
  //console.log('seats=',seats)
  //if (!seats) {
  //  console.log('xxxxx count = 0')
  //}
}
function teatBuyMore(){
  testBuy('userA')
  testBuy('userB')
}
teatBuyMore()

test result

The result looks like, during concurrent, tests 1 and 3 are overwritten, and only tests 2 and 4 are effective

transaction isolation level rr

test1: before db data: id =4 movieId=1 version = 1 movie=fly claimedBy = null

prisma:query SELECT `prisma_test`.`seat`.`id`, `prisma_test`.`seat`.`claimedBy`, `prisma_test`.`seat`.`movieId`, `prisma_test`.`seat`.`movie`, `prisma_test`.`seat`.`version` FROM `prisma_test`.`seat` WHERE (`prisma_test`.`seat`.`movie` = ? AND `prisma_test`.`seat`.`claimedBy` IS NULL) LIMIT ? OFFSET ?
prisma:query SELECT `prisma_test`.`seat`.`id`, `prisma_test`.`seat`.`claimedBy`, `prisma_test`.`seat`.`movieId`, `prisma_test`.`seat`.`movie`, `prisma_test`.`seat`.`version` FROM `prisma_test`.`seat` WHERE (`prisma_test`.`seat`.`movie` = ? AND `prisma_test`.`seat`.`claimedBy` IS NULL) LIMIT ? OFFSET ?
prisma:query BEGIN
prisma:query BEGIN
prisma:query SELECT `prisma_test`.`seat`.`id` FROM `prisma_test`.`seat` WHERE (`prisma_test`.`seat`.`id` = ? AND `prisma_test`.`seat`.`version` = ?)
prisma:query SELECT `prisma_test`.`seat`.`id` FROM `prisma_test`.`seat` WHERE (`prisma_test`.`seat`.`id` = ? AND `prisma_test`.`seat`.`version` = ?)
prisma:query UPDATE `prisma_test`.`seat` SET `claimedBy` = ?, `version` = (`version` + ?) WHERE `prisma_test`.`seat`.`id` IN (?)
prisma:query COMMIT
prisma:query UPDATE `prisma_test`.`seat` SET `claimedBy` = ?, `version` = (`version` + ?) WHERE `prisma_test`.`seat`.`id` IN (?)
prisma:query COMMIT

after db data: id =4 movieId=1 version =3 movie=fly claimedBy = userB

test2: before db data: id =5 movieId=1 version = 1 movie=fly claimedBy = null

prisma:info Starting a mysql pool with 9 connections.
prisma:query SELECT `prisma_test`.`seat`.`id`, `prisma_test`.`seat`.`claimedBy`, `prisma_test`.`seat`.`movieId`, `prisma_test`.`seat`.`movie`, `prisma_test`.`seat`.`version` FROM `prisma_test`.`seat` WHERE (`prisma_test`.`seat`.`movie` = ? AND `prisma_test`.`seat`.`claimedBy` IS NULL) LIMIT ? OFFSET ?
prisma:query SELECT `prisma_test`.`seat`.`id`, `prisma_test`.`seat`.`claimedBy`, `prisma_test`.`seat`.`movieId`, `prisma_test`.`seat`.`movie`, `prisma_test`.`seat`.`version` FROM `prisma_test`.`seat` WHERE (`prisma_test`.`seat`.`movie` = ? AND `prisma_test`.`seat`.`claimedBy` IS NULL) LIMIT ? OFFSET ?
prisma:query update seat set claimedBy = ?,version = ? where id = ? and version = ?;
seats= 0
xxxxx count = 0
seats= 1
prisma:query update seat set claimedBy = ?,version = ? where id = ? and version = ?;

after db data: id =5 movieId=1 version = 2 movie=fly claimedBy = userB

transaction isolation level rc test3: before db data: id =6 movieId=1 version = 1 movie=fly claimedBy = null

prisma:query SELECT `prisma_test`.`seat`.`id`, `prisma_test`.`seat`.`claimedBy`, `prisma_test`.`seat`.`movieId`, `prisma_test`.`seat`.`movie`, `prisma_test`.`seat`.`version` FROM `prisma_test`.`seat` WHERE (`prisma_test`.`seat`.`movie` = ? AND `prisma_test`.`seat`.`claimedBy` IS NULL) LIMIT ? OFFSET ?
prisma:query SELECT `prisma_test`.`seat`.`id`, `prisma_test`.`seat`.`claimedBy`, `prisma_test`.`seat`.`movieId`, `prisma_test`.`seat`.`movie`, `prisma_test`.`seat`.`version` FROM `prisma_test`.`seat` WHERE (`prisma_test`.`seat`.`movie` = ? AND `prisma_test`.`seat`.`claimedBy` IS NULL) LIMIT ? OFFSET ?
prisma:query BEGIN
prisma:query BEGIN
prisma:query SELECT `prisma_test`.`seat`.`id` FROM `prisma_test`.`seat` WHERE (`prisma_test`.`seat`.`id` = ? AND `prisma_test`.`seat`.`version` = ?)
prisma:query SELECT `prisma_test`.`seat`.`id` FROM `prisma_test`.`seat` WHERE (`prisma_test`.`seat`.`id` = ? AND `prisma_test`.`seat`.`version` = ?)
prisma:query UPDATE `prisma_test`.`seat` SET `claimedBy` = ?, `version` = (`version` + ?) WHERE `prisma_test`.`seat`.`id` IN (?)
prisma:query COMMIT
prisma:query UPDATE `prisma_test`.`seat` SET `claimedBy` = ?, `version` = (`version` + ?) WHERE `prisma_test`.`seat`.`id` IN (?)
prisma:query COMMIT

after db data: id =6 movieId=1 version =3 movie=fly claimedBy = userA

test4: before db data: id =7 movieId=1 version = 1 movie=fly claimedBy = null

prisma:query SELECT `prisma_test`.`seat`.`id`, `prisma_test`.`seat`.`claimedBy`, `prisma_test`.`seat`.`movieId`, `prisma_test`.`seat`.`movie`, `prisma_test`.`seat`.`version` FROM `prisma_test`.`seat` WHERE (`prisma_test`.`seat`.`movie` = ? AND `prisma_test`.`seat`.`claimedBy` IS NULL) LIMIT ? OFFSET ?
prisma:query SELECT `prisma_test`.`seat`.`id`, `prisma_test`.`seat`.`claimedBy`, `prisma_test`.`seat`.`movieId`, `prisma_test`.`seat`.`movie`, `prisma_test`.`seat`.`version` FROM `prisma_test`.`seat` WHERE (`prisma_test`.`seat`.`movie` = ? AND `prisma_test`.`seat`.`claimedBy` IS NULL) LIMIT ? OFFSET ?
prisma:query update seat set claimedBy = ?,version =version+1 where id = ? and version = ?;
seats= 0
xxxxx count = 0
prisma:query update seat set claimedBy = ?,version =version+1 where id = ? and version = ?;
seats= 1

after db data: id =7 movieId=1 version = 2 movie=fly claimedBy = userB

Jack
  • 11
  • 2
  • I'm having difficulty reproducing this, would it be possible to provide a small reproduction (perhaps in the form of a github repo)? I see that you have created a github issue, it would be good to provide a reproduction there as well. – Tasin Ishmam Nov 16 '21 at 14:13
  • [code demo](https://github.com/AoiIsMine/test) Thank u for your answer – Jack Nov 17 '21 at 03:19
  • Thanks for the reproduction. This seems to be a bug. I can confirm being able to reproduce the problem for `mysql` (works fine in `postgres`). I think it would be better to move the conversation to the [github issue](https://github.com/prisma/prisma/issues/10207) you created. I work at Prisma and I'll ask someone to look into this. – Tasin Ishmam Nov 17 '21 at 16:02
  • We look forward to your reply and thank you for your help – Jack Nov 18 '21 at 01:47
  • Dear Tasin Ishmam Since my project needs optimistic locks to prevent concurrency problems, I'd like to know when you plan to deal with this problem Should I write raw sql first or wait until you deal with this problem I hope you can give me a suggestion Thanks for your help – Jack Nov 22 '21 at 11:50
  • Hi Jack, I understand your situation and I apologize for the inconvenience :(. If it's possible, I would suggest writing raw queries for the moment until this is resolved. I can't give an exact deadline when we will fix this, but we will update the github issue when it happens. – Tasin Ishmam Nov 22 '21 at 11:55
  • Dear Tasin Ishmam : Thank u for your reply. I'll start writing raw sql (but it won't start until two weeks later). I look forward to your early solution to this problem. I also thank all members of prisma for their hard contributions. – Jack Nov 23 '21 at 12:24
  • Glad you're enjoying using Prisma :D. Unfortunately, I can't comment if this will be fixed within 2 weeks (would likely take longer), so a raw SQL solution might be the way to go for the moment. – Tasin Ishmam Nov 23 '21 at 12:26
  • OK, I'll use raw sql first – Jack Nov 24 '21 at 10:33
  • 1
    Dear Tasin Ishmam :i need your help [issues](https://github.com/prisma/prisma/issues/10622) – Jack Dec 08 '21 at 12:33
  • Hey @Jack, I'll relay it to the appropriate team, but unfortunately it might take some time for a response. – Tasin Ishmam Dec 08 '21 at 19:40
  • Thank u and look forward to team reply – Jack Dec 09 '21 at 07:40

0 Answers0