NodeJS 为什么在Sequelize中,锁定行会导致更新在同一事务中卡住?

uemypmqf  于 9个月前  发布在  Node.js
关注(0)|答案(1)|浏览(101)

我使用node.js作为预订机票项目的后端,我还使用Sequelize在DB上操作数据库是在线的,托管在Clever Cloud上。我有两个控制器,名为Seats.js和Bookings.js,第一个用于检查我必须预订的座位并锁定它们,以防止更新期间出现比赛情况,第二个用于有效预订座位和门票。所有这些都被 Package 到一个事务中,但是由于未知的原因,当座位被锁定时,我无法更新它们(当它们都在同一个事务中时),更新就会卡住。
提前感谢任何答案。
我希望运行时不会卡住,但事实并非如此,这是node.js给我的日志:

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" = '[email protected]'; 
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)被卡住,无法前进到其他查询。
启动SELECT查询的代码是:
(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,
    };
  }
}


启动第一个checkSeatForBooking然后更新查询的代码是:
(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 departureTicketsToBook = flightState.seatsFlightsDeparture;
        const seatsFlightsReturning = flightState.seatsFlightsReturning;
        const returningTicketsToBook = 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,
        });
      }
}

iibxawm4

iibxawm41#

对于其他有这个问题的用户,事实上我没有在Seats.update()中包含transition。这个函数应该是:

const seatBooking = await Seats.update(
    {
       is_booked: true,
       version: instanceSequelize.literal('version + 1')
    },
    {
       where: {
          seat_number: seat.seatNumber,
          flight_number: seat.flightNumber,
          version
       },
         transaction //inside the function
       }
    );

字符串

相关问题