Why, in Sequelize, locking row causes update to get stuck while in the same transaction?

Question

I'm using node.js as a backend for a booking flight ticket project and i'm also using Sequelize to operate on the DB (PostgreSQL). The DB is online, hosted on Clever Cloud. I've two controllers, named Seats.js and Bookings.js, the first is used for checking the seats that i have to book and locks them to prevent race conditions during update and the second is used to effectively book seats and tickets. It's all wrapped into a single transaction, but for an unknown reason, when the seats are locked, i can't update them (while it's all in the same transaction) and the update gets stuck.

Thanks in advance for any answer.

I've expected to run without getting stuck, but it isn't, and this is what node.js gives me as log:

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  

The last row (UPDATE) is stuck, and can't go forward to other queries.

The code that launches the SELECT query is:

(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,
    };
  }
}

And the code that launches first checkSeatForBooking then the UPDATE query is:

(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,
        });
      }
}

Answer 1

For other users with this problem, the fact is i didn't include transition inside Seats.update(). This function should be:

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
       }
    );