Sequelize同事务中行锁引发更新卡住问题求助

问题背景 #

使用Node.js作为机票预订项目后端，通过Sequelize操作托管在Clever Cloud的PostgreSQL数据库。项目分为两个控制器：Seats.js负责检查并锁定待预订座位以避免竞态条件，Bookings.js完成座位与机票的实际预订。所有操作包裹在单个事务中，但出现异常：锁定座位后，同事务内的UPDATE操作陷入停滞。

执行日志 #

Executing (bd3d1a7b-5e49-49f9-92db-6cf50afd2a1b): START TRANSACTION; 
Executing (bd3d1a7b-5e49-49f9-92db-6cf50afd2a1b): SET TRANSACTION ISOLATION LEVEL READ COMMITTED; 
Executing (bd3d1a7b-5e49-49f9-92db-6cf50afd2a1b): SELECT "seat_number", "flight_number", "price", "is_booked", "version" FROM "Seats" AS "Seats" WHERE "Seats"."seat_number" = 'B3' AND "Seats"."flight_number" = 'U2 4833' FOR UPDATE; 
Executing (default): SELECT "flight_number", "fk_IATA_from", "fk_IATA_to", "departure", "arrival", "price", "fk_airline" FROM "Flights" AS "Flights" WHERE "Flights"."flight_number" = 'U2 4833'; 
Executing (default): SELECT "email", "password", "name", "surname" FROM "Users" AS "Users" WHERE "Users"."email" = 'mailone@ezmail.com'; 
Executing (default): SELECT "seat_number", "flight_number", "price", "is_booked", "version" FROM "Seats" AS "Seats" WHERE "Seats"."flight_number" = 'U2 4833' AND "Seats"."seat_number" = 'B3' AND "Seats"."is_booked" = true; 
Executing (default): UPDATE "Seats" SET "is_booked"=$1,"version"=version + 1 WHERE "seat_number" = $2 AND "flight_number" = $3 AND "version" = $4  

最后一行UPDATE操作始终无法推进。

相关代码 #

Seats.js（座位锁定逻辑）

const checkSeatForBooking = async (req, transaction, res, next) => {
  const { seatNumber, flightNumber } = req;
  
  try {
    const check = await Seats.findOne({
      where: {
        seat_number: seatNumber,
        flight_number: flightNumber
      },
      lock: transaction.LOCK.UPDATE,
      transaction
    });

    if (!check) {
      return {
        success: false,
        seat_number: seatNumber,
        message: `Seat ${seatNumber} doesn't exist`,
      };
    } else if (check.isBooked) {
      return {
        success: false,
        seat_number: check.seat_number,
        message: `Seat ${check.seat_number} booked previously`,
      };
    }

    return check;
  } catch(error) {
    return {
      success: false,
      message: "Failed checking of seat",
      error: error.message,
    };
  }
}

Bookings.js（预订执行逻辑）

const insertBookings = async (req, res, next) => {
    const transaction = await instanceSequelize.transaction({
      isolationLevel: Transaction.ISOLATION_LEVELS.READ_COMMITTED
    });
    
    try {
        const flightState = req.body.flightState;
        const seatsFlightsDeparture = flightState.seatsFlightsDeparture;
        const seatsFlightsReturning = flightState.seatsFlightsReturning;
    
        //Check seats
        for (const flight of seatsFlightsDeparture) {
          for (const seat of flight) {
            const seatCheckResult = await checkSeatForBooking(seat, transaction);
        
            if (!seatCheckResult) {
              await transaction.rollback();
              return res.status(400).json(seatCheckResult);
            }
        
            seat.seatNumber = seatCheckResult.seat_number;
            seat.seatPrice = seatCheckResult.price;
            seat.version = seatCheckResult.version;
          }
        }
        
        //Other code not useful for this problem
        
        //UPDATE SEATS
        for (const flight  of seatsFlightsDeparture) {
          for (const seat of flight) {
            const existingBookedSeat = await Seats.findOne({
              where: {
                flight_number: seat.flightNumber,
                seat_number: seat.seatNumber,
                is_booked: true
              },
            });
        
            if (existingBookedSeat) {
              await transaction.rollback();
              return res.status(400).json({
                success: false,
                message: "Seat already booked",
              });
            }
        
            const version = seat.version;
        
            const seatBooking = await Seats.update(
              {
                is_booked: true,
                version: instanceSequelize.literal('version + 1')
              },
              {
                where: {
                  seat_number: seat.seatNumber,
                  flight_number: seat.flightNumber,
                  version
                }
              },
              transaction
            );
        
            if (!seatBooking) {
              await transaction.rollback();
              return res.status(400).json({
                success: false,
                message: "Cannot book departure seats",
              });
            }
          }
        }
    
        //Other code not useful for this problem
    
        await transaction.commit();
    
        res.status(200).send({
            success: true,
            message: "Departure booking inserted successfully",
            booking
        });
    
      } catch (error) {
        await transaction.rollback();
        console.error(error);
        res.status(500).json({
          success: false,
          message: "Can not insert booking, insert operation failed",
          error: error.message,
        });
      }
}

问题根源 #

1. 事务上下文丢失：UPDATE操作及后续的座位查询未传入transaction参数，导致Sequelize使用默认连接执行，而之前的SELECT FOR UPDATE已在事务连接中锁定该行，默认连接的操作被自身事务的行锁阻塞（日志中UPDATE标记为(default)，与锁定操作的事务ID不一致）。
2. 冗余查询加剧阻塞：UPDATE前再次查询座位状态的操作，同样未传入事务参数，试图读取被锁定的行，进一步延长阻塞时间。
3. 逻辑冗余：事务内已通过SELECT FOR UPDATE锁定并验证座位未被预订，UPDATE前再次检查is_booked=true完全多余，事务锁已保证其他事务无法修改该座位状态。

修复方案 #

1. 确保所有数据库操作绑定事务 #

将transaction参数传入所有事务内的数据库操作选项中，保证所有操作在同一个事务上下文执行。

2. 移除冗余的座位状态查询 #

删除UPDATE前的Seats.findOne检查，依赖事务锁保证座位状态不会被外部修改。

3. 修正UPDATE结果判断逻辑 #

Sequelize的update方法返回受影响行数的数组，需判断数组第一个元素是否大于0，而非直接判断对象是否存在。

修复后的Bookings.js核心代码片段 #

//UPDATE SEATS
for (const flight  of seatsFlightsDeparture) {
  for (const seat of flight) {
    const version = seat.version;

    const seatBooking = await Seats.update(
      {
        is_booked: true,
        version: instanceSequelize.literal('version + 1')
      },
      {
        where: {
          seat_number: seat.seatNumber,
          flight_number: seat.flightNumber,
          version
        },
        transaction // 关键：传入事务对象
      }
    );

    // 修正判断逻辑：检查受影响行数
    if (seatBooking[0] === 0) {
      await transaction.rollback();
      return res.status(400).json({
        success: false,
        message: "Cannot book departure seats",
      });
    }
  }
}

额外优化 #

• 修正checkSeatForBooking的返回值判断：原代码if (!seatCheckResult)逻辑错误，应改为if (seatCheckResult.success === false)，因为该函数无论成功失败都返回对象。
• 事务内的Flights、Users查询也需传入transaction参数，确保全链路事务一致性。

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