Prisma 使用 updateMany 实现乐观并发控制时并发场景出现覆盖写问题

我遇到了一个问题，不确定是使用方式错误还是框架本身存在缺陷：在并发场景下，使用updateMany实现的乐观锁会出现覆盖写问题。

简单测试 #

本次使用的乐观并发控制模式示例代码基于官方文档做了少量修改。

schema #

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'
  
  // 查询第一个可预订座位
  // availableSeat.version 初始可能为0
  const availableSeat = await prisma.seat.findFirst({
    where: {
      movie: movieName,
      claimedBy: null,
    },
  })
  
  if (!availableSeat) {
    console.log('seat is zero')
    return
  }
  
  // test1 和 test3 使用该写法
   const seats = await prisma.seat.updateMany({
     data: {
       claimedBy: userName,
       version: {
         increment: 1,
       },
     },
     where: {
       id: availableSeat.id,
       version: availableSeat.version, // version字段是乐观锁核心：仅当内存中读取的版本号与数据库当前版本号一致，说明记录未被修改时才会抢占座位成功
     },
   })

   if (seats.count === 0) {
     console.log('xxxxx count = 0')
   }
  
  // test2 和 test4 使用该写法
  //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()

测试结果 #

测试结果显示，并发场景下test1、test3出现了覆盖写问题，仅test2、test4的乐观锁逻辑生效。

事务隔离级别 RR（可重复读）

test1测试前数据库数据：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

test1测试后数据库数据：id =4 movieId=1 version =3 movie=fly claimedBy = userB

test2测试前数据库数据：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 = ?;

test2测试后数据库数据：id =5 movieId=1 version = 2 movie=fly claimedBy = userB

事务隔离级别 RC（读已提交）

test3测试前数据库数据：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

test3测试后数据库数据：id =6 movieId=1 version =3 movie=fly claimedBy = userA

test4测试前数据库数据：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

test4测试后数据库数据：id =7 movieId=1 version = 2 movie=fly claimedBy = userB

内容的提问来源于stack exchange，提问作者Jack