mysql - prisma：乐观并发控制使用UpdateMany有问题

Question

我有个问题。不知道是自己用错了还是有问题 并发下，用updateMany实现的乐观锁会有overwrite writes 谢谢大家帮忙~
地址

简单测试

文档代码演示中的乐观并发控制模式案例代码（小改动）

图式

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

代码

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()

测试结果

结果看起来，在并发期间，测试 1 和 3 被覆盖，只有测试 2 和 4 有效

事务隔离级别 rr

test1：db 数据之前：id =4 movieId=1 version = 1 movie=fly claimBy = 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

db 数据后：id =4 movieId=1 version =3 movie=fly claimBy = userB

test2：在db数据之前：id =5 movieId=1 version = 1 movie=fly claimBy = 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 = ?;

db 数据后： id =5 movieId=1 version = 2 movie=fly claimBy = userB

事务隔离级别rc test3：db数据前：id =6 movieId=1 version = 1 movie=fly claimBy = 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

db 数据后： id =6 movieId=1 version =3 movie=fly claimBy = userA

test4：db 数据之前：id =7 movieId=1 version = 1 movie=fly claimBy = 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

db 数据后： id =7 movieId=1 version = 2 movie=fly claimBy = userB

标签： mysqlnode.jsconcurrencyprismaoptimistic