SQL - Race Condition

What is Race Condition?

Race condition is a problem that can occur when multiple processes or threads access and modify a shared resource concurrently in a concurrent environment. Race conditions can lead to unexpected and inconsistent results and also can cause undesired behavior or data corruption.

Examples

This is the code I originally wrote.

// 예약 생성
export const createReservation = async (
    req: Request,
    res: Response,
    next: NextFunction
) => {
    try {
        const {
            member_generation,
            member_name,
            member_email,
            reservation_date,
            start_time,
            end_time,
            visitors,
            seat_number,
            seat_type
        } = req.body;

        // 좌석 유효성 검사
        const getSeatQuery = `
            SELECT *
            FROM seats
            WHERE seat_number = ?
        `;

        const [seatRows] = await con.promise().query(getSeatQuery, [seat_number]);
        const seat: RowDataPacket | undefined = (seatRows as RowDataPacket[])[0];

        if (!seat) {
            return res.status(400).json({ error: '잘못된 좌석 번호입니다.' });
        }

        // 예약 가능 여부 확인
        const originalCurrentDate = new Date();
        const year = originalCurrentDate.getFullYear();
        const month = String(originalCurrentDate.getMonth() + 1).padStart(2, '0');
        const day = String(originalCurrentDate.getDate()).padStart(2, '0');
        const hours = String(originalCurrentDate.getHours()).padStart(2, '0');
        const minutes = String(originalCurrentDate.getMinutes()).padStart(2, '0');

        const currentDate = `${year}-${month}-${day} ${hours}:${minutes}`;
        const selectedDate = reservation_date + ' ' + end_time;

        if (selectedDate < currentDate) {
            return res.status(400).json({ error: '지난 날짜로 예약을 생성할 수 없습니다.' });
        }

        // 해당 좌석의 중복 예약 확인
        const checkDuplicateQuery = `
            SELECT *
            FROM reservations
            WHERE seat_number = ? 
                AND reservation_date = ?
                AND ((start_time >= ? AND start_time <= ?) OR (end_time >= ? AND end_time <= ?))
        `;

        const [duplicateRows] = await con
            .promise()
            .query(checkDuplicateQuery, [seat_number, reservation_date, start_time, end_time, start_time, end_time]);

        if (Array.isArray(duplicateRows) && duplicateRows.length > 0) {
            return res.status(400).json({ error: '해당 시간에 이미 예약된 좌석입니다.' });
        }

        // 예약 생성
        const reservation_id = uuidv4();

        const createReservationQuery = `
            INSERT INTO reservations (
                reservation_id,
                generation,
                name,
                member_email,
                reservation_date,
                start_time,
                end_time,
                visitors,
                seat_number,
                seat_type,
                status
            )
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        `;

        const createReservationParams = [
            reservation_id,
            member_generation,
            member_name,
            member_email,
            reservation_date,
            start_time,
            end_time,
            visitors,
            seat_number,
            seat_type,
            '예약완료' //
        ];

        await con.promise().query(createReservationQuery, createReservationParams);

        // 예약 정보 조회
        const getReservationQuery = `
            SELECT *
            FROM reservations
            WHERE reservation_id = ?
        `;
        const [reservationRows] = await con.promise().query<RowDataPacket[]>(getReservationQuery, [reservation_id]);
        const reservation: RowDataPacket | undefined = (reservationRows as RowDataPacket[])[0];

        // 이메일 보내기
        const emailText = `성수동 엘리스를 이용해 주셔서 감사합니다. \n \n${member_name}님의 엘리스랩 예약이 아래와 같이 완료되었습니다.\n예약 ID: ${reservation_id} \n예약일자: ${reservation_date} ${start_time}~${end_time} \n예약좌석: ${seat_type} ${seat_number}번 \n\n예약시간을 꼭 지켜주세요.`;
        const emailSubject = '성수동 엘리스 예약이 완료되었습니다.';
        const receiver = member_email;
        sendEmail(receiver, emailSubject, emailText);

        return res.status(201).json({ message: '예약이 완료되었습니다.', reservation

In the code, there are several potential issues related to race conditions:

Seat Validation

The code retrieves seat information from the database to validate the seat number. If multiple requests are processed concurrently and check the same seat number simultaneously, there is a possibility of race conditions. Two or more requests might concurrently find the seat as valid and proceed with the reservation, even if there is only one seat available.

Duplicate Reservation Check

The code queries the reservations table to check for any existing reservations for the given seat, date, and time range. However, if multiple requests check for duplicate reservations concurrently, they may all pass the check and proceed with creating a reservation, leading to multiple reservations for the same seat and time slot.

Solution

To address the race condition in my code, I had several choices for handling concurrent access to shared resources. Three common approaches are using transactions, implementing locks, or adding a query to recheck the seat availability after making a reservation. In this case, I chose to use locks.

By using locks, I was able to ensure exclusive access to shared resources, such as the "seats" and "reservations" tables. This helped prevent multiple threads or processes from simultaneously modifying the same data, eliminating the race condition.

code

// 예약 생성
export const createReservation = async (
    req: Request,
    res: Response,
    next: NextFunction
) => {
    try {
        const {
            member_generation,
            member_name,
            member_email,
            reservation_date,
            start_time,
            end_time,
            visitors,
            seat_number,
            seat_type
        } = req.body;

        // 좌석 유효성 검사
        const getSeatQuery = `
            SELECT *
            FROM seats
            WHERE seat_number = ?
        `;

        // 좌석 및 예약 테이블 락 설정
        await con.promise().query('LOCK TABLES seats WRITE, reservations WRITE');

        const [seatRows] = await con.promise().query(getSeatQuery, [seat_number]);
        const seat: RowDataPacket | undefined = (seatRows as RowDataPacket[])[0];

        if (!seat) {
            // 락 해제
            await con.promise().query('UNLOCK TABLES');
            return res.status(400).json({ error: '잘못된 좌석 번호입니다.' });
        }

        // 예약 가능 여부 확인
        const originalCurrentDate = new Date();
        const year = originalCurrentDate.getFullYear();
        const month = String(originalCurrentDate.getMonth() + 1).padStart(2, '0');
        const day = String(originalCurrentDate.getDate()).padStart(2, '0');
        const hours = String(originalCurrentDate.getHours()).padStart(2, '0');
        const minutes = String(originalCurrentDate.getMinutes()).padStart(2, '0');

        const currentDate = `${year}-${month}-${day} ${hours}:${minutes}`;
        const selectedDate = reservation_date + ' ' + end_time;

        if (selectedDate < currentDate) {
            // 락 해제
            await con.promise().query('UNLOCK TABLES');
            return res.status(400).json({ error: '지난 날짜로 예약을 생성할 수 없습니다.' });
        }

        // 예약 생성
        const reservation_id = uuidv4();

        const createReservationQuery = `
            INSERT INTO reservations (
                reservation_id,
                generation,
                name,
                member_email,
                reservation_date,
                start_time,
                end_time,
                visitors,
                seat_number,
                seat_type,
                status
            )
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        `;

        const createReservationParams = [
            reservation_id,
            member_generation,
            member_name,
            member_email,
            reservation_date,
            start_time,
            end_time,
            visitors,
            seat_number,
            seat_type,
            '예약완료' //
        ];

        await con.promise().query(createReservationQuery, createReservationParams);

        // 예약 정보 조회
        const getReservationQuery = `
            SELECT *
            FROM reservations
            WHERE reservation_id = ?
        `;
        const [reservationRows] = await con.promise().query<RowDataPacket[]>(getReservationQuery, [reservation_id]);
        const reservation: RowDataPacket | undefined = (reservationRows as RowDataPacket[])[0];

        // 이메일 보내기
        const emailText = `성수동 엘리스를 이용해 주셔서 감사합니다. \n \n${member_name}님의 엘리스랩 예약이 아래와 같이 완료되었습니다.\n예약 ID: ${reservation_id} \n예약일자: ${reservation_date} ${start_time}~${end_time} \n예약좌석: ${seat_type} ${seat_number}번 \n\n예약시간을 꼭 지켜주세요.`;
        const emailSubject = '성수동 엘리스 예약이 완료되었습니다.';
        const receiver = member_email;
        sendEmail(receiver, emailSubject, emailText);

        // 락 해제
        await con.promise().query('UNLOCK TABLES');

        return res.status(201).json({ message: '예약이 완료되었습니다.', reservation });
    } catch (err) {
        return Promise.reject(err);
    }
};

In the updated code, I added table-level locks to protect the relevant tables during the reservation process. The LOCK TABLES is used to acquire the necessary locks, and the UNLOCK TABLES releases them once the reservation is complete. This guarantees that only one reservation operation can access the shared resources at any given time, preventing conflicts and maintaining data integrity.

Conclusion

I thought my code prevented a duplicated reservation just because I added a query that checks validation of seats in the API. I got this idea(more likely issue) when I had an interview with a company. It made me consider Concurrency issue and Race condition.

Considering race conditions when creating API features, such as the reservation creation API in my case, is crucial for ensuring data consistency, preventing conflicts, and maintaining the integrity of your application. By addressing race conditions through techniques like locks, transactions, or careful synchronization, you can create more reliable and robust APIs.