Трансакции

Вовед

Во рамките на оваа фаза, целта е да се прикаже како еден систем за авионски резервации управува со паралелни барања од повеќе корисници во исто време, без да се наруши конзистентноста на податоците.

Фокусот е системот да гарантира конзистентна продажба и резервација на седишта:

• две различни резервации да не можат да завршат со исто седиште на ист лет;
• да нема overbooking, односно повеќе резервации од капацитетот на авионот;
• промена или откажување на резервација да биде атомска операција;
• промена на авионот да не доведе до состојба во која постоечките резервации го надминуваат новиот капацитет.

Овие операции во пракса можат да се случуваат истовремено од различни корисници. Без трансакции и locking, две операции можат паралелно да ја прочитаат истата состојба и да донесат одлука врз основа на веќе застарени податоци.

Core transactional domain во airportdb

booking е централната табела за резервации. Тука најчесто се појавуваат concurrency проблемите, бидејќи повеќе корисници можат истовремено да резервираат или менуваат седишта за ист лет.

flight го дефинира конкретниот лет и авионот кој го извршува. Оваа табела е важна затоа што сите резервации се врзуваат за flight, а flight редот може да се користи и како заедничка точка за locking на операции што работат над истиот лет.

airplane го содржи capacity, кој се користи при проверка дали може да се направи нова резервација или дали летот смее да се префрли на друг авион.

Дополнително, passenger и passengerdetails ги содржат податоците за патниците и се поврзани со резервациите.

Дефинирани правила за конзистентност

1. Единственост на седиште по лет.
   
2. Почитување на капацитетот на авионот.
   
3. Атомичност на seat change и cancel.
   
4. Промена на авион само ако постоечките резервации се вклопуваат во новиот капацитет.
   

Во booking постои UNIQUE ограничување над:

(flight_id, seat)

Ова ограничување претставува дополнителна заштита на ниво на база: две резервации не можат да имаат ист flight_id и seat.

Сепак, UNIQUE ограничувањето само по себе не е доволно за проверка на вкупниот капацитет. Две паралелни резервации можат да изберат различни седишта, а сепак заедно да го надминат капацитетот. Затоа е потребна и трансакциска логика.

Реални сценарија

Во системот можат да се појават неколку типични конкурентни ситуации.

1. Два корисници во ист момент се обидуваат да го резервираат истото седиште на ист лет.
   

Ако двете операции ја прочитаат состојбата пред било која да запише промена, двете можат да сметаат дека седиштето е слободно.

2. Два корисници резервираат различни седишта на лет кој има само едно преостанато место.
   

UNIQUE ограничувањето нема да помогне бидејќи седиштата се различни, па мора посебно да се контролира capacity.

3. Корисник сака да го промени своето седиште, а новото седиште во меѓувреме го резервира друг корисник.
   

Промената мора да биде атомска: или целосно успева, или старата резервација останува непроменета.

4. Една операција менува седиште, а друга истовремено ја откажува резервацијата.
   

Операциите мора да се серијализираат за да не работат врз неконзистентна состојба.

5. Оператор сака да го промени авионот на летот со авион со помал капацитет.
   

Промената мора да се одбие ако веќе постојат повеќе резервации од капацитетот на новиот авион.

Затоа процедурите користат:

• START TRANSACTION
• SELECT ... FOR UPDATE
• COMMIT
• ROLLBACK
• RESIGNAL

Редот од flight се користи како заедничка точка за locking на операциите што работат над истиот лет. Со тоа проверките и промените не се извршуваат истовремено врз различна верзија на состојбата.

Procedure Reserve Seat - резервација и контрола на капацитет

Кај резервирањето имаме типична "check-then-write" логика: прво се проверува дали седиштето е слободно и дали летот има капацитет, а потоа се прави INSERT.

Ако две сесии ја направат проверката паралелно без locking, и двете можат да видат стара состојба и да продолжат со внесување.

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_reserve_seat`(
    IN p_flight_id INT,
    IN p_seat VARCHAR(10),
    IN p_passenger_id INT,
    IN p_price DECIMAL(10,2)
)
BEGIN
    DECLARE v_airplane_id INT DEFAULT NULL;
    DECLARE v_capacity INT DEFAULT NULL;
    DECLARE v_booked INT DEFAULT 0;
    DECLARE v_occupied INT DEFAULT 0;
    DECLARE v_booking_id INT DEFAULT NULL;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    IF p_flight_id IS NULL OR p_flight_id <= 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MYSQL_ERRNO = 30001, MESSAGE_TEXT = 'invalid flight_id';
    END IF;

    IF p_passenger_id IS NULL OR p_passenger_id <= 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MYSQL_ERRNO = 30002, MESSAGE_TEXT = 'invalid passenger_id';
    END IF;

    IF p_price IS NULL OR p_price < 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MYSQL_ERRNO = 30009, MESSAGE_TEXT = 'invalid price';
    END IF;

    IF p_seat IS NULL
       OR CHAR_LENGTH(TRIM(p_seat)) = 0
       OR CHAR_LENGTH(TRIM(p_seat)) > 10 THEN
        SIGNAL SQLSTATE '45000'
            SET MYSQL_ERRNO = 30003, MESSAGE_TEXT = 'invalid seat';
    END IF;

    SET p_seat = TRIM(p_seat);

    SELECT airplane_id
      INTO v_airplane_id
      FROM flight
     WHERE flight_id = p_flight_id
     FOR UPDATE;

    IF v_airplane_id IS NULL THEN
        SIGNAL SQLSTATE '45000'
            SET MYSQL_ERRNO = 30004, MESSAGE_TEXT = 'flight not found';
    END IF;

    IF COALESCE(@txn_lab_pause, 0) > 0 THEN
        DO SLEEP(@txn_lab_pause);
    END IF;

    SELECT capacity
      INTO v_capacity
      FROM airplane
     WHERE airplane_id = v_airplane_id;

    IF v_capacity IS NULL THEN
        SIGNAL SQLSTATE '45000'
            SET MYSQL_ERRNO = 30005, MESSAGE_TEXT = 'airplane not found';
    END IF;

    SELECT COUNT(*)
      INTO v_occupied
      FROM booking
     WHERE flight_id = p_flight_id
       AND seat = p_seat;

    IF v_occupied > 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MYSQL_ERRNO = 30006, MESSAGE_TEXT = 'seat already occupied';
    END IF;

    SELECT COUNT(*)
      INTO v_booked
      FROM booking
     WHERE flight_id = p_flight_id;

    IF v_booked >= v_capacity THEN
        SIGNAL SQLSTATE '45000'
            SET MYSQL_ERRNO = 30007, MESSAGE_TEXT = 'flight is full';
    END IF;

    INSERT INTO booking (flight_id, passenger_id, seat, price)
    VALUES (p_flight_id, p_passenger_id, p_seat, p_price);

    SET v_booking_id = LAST_INSERT_ID();

    COMMIT;

    SELECT 'RESERVED' AS status,
           v_booking_id AS booking_id,
           p_flight_id AS flight_id,
           p_passenger_id AS passenger_id,
           p_seat AS seat,
           p_price AS price;
END$$

DELIMITER ;

Што прави процедурата?

sp_reserve_seat прво ги валидира влезните параметри, а потоа го заклучува соодветниот flight ред со FOR UPDATE.

Додека тој lock е активен, друга процедура што работи над истиот flight не може паралелно да ја помине критичната проверка.

Потоа:

1. се чита капацитетот на авионот;
2. се проверува дали избраното седиште е веќе зафатено;
3. се брои бројот на постоечки резервации;
4. ако има капацитет, се прави INSERT;
5. при успех се прави COMMIT.

Ако седиштето е зафатено се враќа 30006, а ако летот е полн се враќа 30007.

EXIT HANDLER прави ROLLBACK и RESIGNAL, така што при грешка не останува делумно запишана трансакција.

@txn_lab_pause не е дел од бизнис логиката. Се користи само за тестирање, за намерно да се задржи lock-от неколку секунди и полесно да се репродуцира конкурентно извршување.

Procedure Change Seat - атомична промена на седиште

При промена на седиште е важно старата позиција да не се изгуби ако новото седиште е веќе зафатено.

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_change_seat`(
    IN p_booking_id INT,
    IN p_new_seat VARCHAR(10)
)
BEGIN
    DECLARE v_flight_id INT DEFAULT NULL;
    DECLARE v_airplane_id INT DEFAULT NULL;
    DECLARE v_capacity INT DEFAULT NULL;
    DECLARE v_old_seat VARCHAR(10) DEFAULT NULL;
    DECLARE v_occupied INT DEFAULT 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    IF p_booking_id IS NULL OR p_booking_id <= 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MYSQL_ERRNO = 30011, MESSAGE_TEXT = 'invalid booking_id';
    END IF;

    IF p_new_seat IS NULL
       OR CHAR_LENGTH(TRIM(p_new_seat)) = 0
       OR CHAR_LENGTH(TRIM(p_new_seat)) > 10 THEN
        SIGNAL SQLSTATE '45000'
            SET MYSQL_ERRNO = 30012, MESSAGE_TEXT = 'invalid seat';
    END IF;

    SET p_new_seat = TRIM(p_new_seat);

    SELECT flight_id
      INTO v_flight_id
      FROM booking
     WHERE booking_id = p_booking_id;

    IF v_flight_id IS NULL THEN
        SIGNAL SQLSTATE '45000'
            SET MYSQL_ERRNO = 30013, MESSAGE_TEXT = 'booking not found';
    END IF;

    SELECT airplane_id
      INTO v_airplane_id
      FROM flight
     WHERE flight_id = v_flight_id
     FOR UPDATE;

    IF v_airplane_id IS NULL THEN
        SIGNAL SQLSTATE '45000'
            SET MYSQL_ERRNO = 30014, MESSAGE_TEXT = 'flight not found';
    END IF;

    IF COALESCE(@txn_lab_pause, 0) > 0 THEN
        DO SLEEP(@txn_lab_pause);
    END IF;

    SET v_old_seat = NULL;

    SELECT seat
      INTO v_old_seat
      FROM booking
     WHERE booking_id = p_booking_id
       AND flight_id = v_flight_id
     FOR UPDATE;

    IF v_old_seat IS NULL THEN
        SIGNAL SQLSTATE '45000'
            SET MYSQL_ERRNO = 30013, MESSAGE_TEXT = 'booking not found';
    END IF;

    SELECT capacity
      INTO v_capacity
      FROM airplane
     WHERE airplane_id = v_airplane_id;

    IF v_capacity IS NULL THEN
        SIGNAL SQLSTATE '45000'
            SET MYSQL_ERRNO = 30015, MESSAGE_TEXT = 'airplane not found';
    END IF;

    IF p_new_seat = v_old_seat THEN
        COMMIT;

        SELECT 'UNCHANGED' AS status,
               p_booking_id AS booking_id,
               v_flight_id AS flight_id,
               v_old_seat AS old_seat,
               v_old_seat AS new_seat;
    ELSE
        SELECT COUNT(*)
          INTO v_occupied
          FROM booking
         WHERE flight_id = v_flight_id
           AND seat = p_new_seat
           AND booking_id <> p_booking_id;

        IF v_occupied > 0 THEN
            SIGNAL SQLSTATE '45000'
                SET MYSQL_ERRNO = 30016,
                    MESSAGE_TEXT = 'target seat already occupied';
        END IF;

        UPDATE booking
           SET seat = p_new_seat
         WHERE booking_id = p_booking_id;

        COMMIT;

        SELECT 'CHANGED' AS status,
               p_booking_id AS booking_id,
               v_flight_id AS flight_id,
               v_old_seat AS old_seat,
               p_new_seat AS new_seat;
    END IF;
END$$

DELIMITER ;

Што прави процедурата?

Најпрво се пронаоѓа flight-от на резервацијата, а потоа неговиот ред се заклучува со FOR UPDATE.

Потоа повторно се чита и се заклучува конкретниот booking ред. Ова е важно затоа што booking-от може да биде променет или избришан додека се чека flight lock-от.

Ако новото седиште е исто со старото, нема потреба од UPDATE.

Во спротивно се проверува дали друго booking веќе го користи новото седиште. Ако е зафатено, се враќа:

30016: target seat already occupied

и целиот transaction се враќа назад.

Со ова старата позиција на патникот останува непроменета ако промената не може успешно да се заврши.

Procedure Cancel Booking - безбедно откажување

Cancel може да се судри со seat change или друга операција врз истата резервација. Затоа и оваа процедура го користи истиот locking редослед.

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_cancel_booking`(
    IN p_booking_id INT
)
BEGIN
    DECLARE v_flight_id INT DEFAULT NULL;
    DECLARE v_airplane_id INT DEFAULT NULL;
    DECLARE v_old_seat VARCHAR(10) DEFAULT NULL;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    IF p_booking_id IS NULL OR p_booking_id <= 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MYSQL_ERRNO = 30021, MESSAGE_TEXT = 'invalid booking_id';
    END IF;

    SELECT flight_id
      INTO v_flight_id
      FROM booking
     WHERE booking_id = p_booking_id;

    IF v_flight_id IS NULL THEN
        SIGNAL SQLSTATE '45000'
            SET MYSQL_ERRNO = 30022, MESSAGE_TEXT = 'booking not found';
    END IF;

    SELECT airplane_id
      INTO v_airplane_id
      FROM flight
     WHERE flight_id = v_flight_id
     FOR UPDATE;

    IF v_airplane_id IS NULL THEN
        SIGNAL SQLSTATE '45000'
            SET MYSQL_ERRNO = 30023, MESSAGE_TEXT = 'flight not found';
    END IF;

    IF COALESCE(@txn_lab_pause, 0) > 0 THEN
        DO SLEEP(@txn_lab_pause);
    END IF;

    SET v_old_seat = NULL;

    SELECT seat
      INTO v_old_seat
      FROM booking
     WHERE booking_id = p_booking_id
       AND flight_id = v_flight_id
     FOR UPDATE;

    IF v_old_seat IS NULL THEN
        SIGNAL SQLSTATE '45000'
            SET MYSQL_ERRNO = 30022, MESSAGE_TEXT = 'booking not found';
    END IF;

    DELETE FROM booking
     WHERE booking_id = p_booking_id;

    COMMIT;

    SELECT 'CANCELLED' AS status,
           p_booking_id AS booking_id,
           v_flight_id AS flight_id,
           v_old_seat AS seat;
END$$

DELIMITER ;

Што прави процедурата?

sp_cancel_booking прво го наоѓа flight-от на booking-от, потоа го заклучува истиот flight ред кој го користат и другите процедури.

После тоа booking-от повторно се чита со FOR UPDATE. Ова спречува да се избрише запис кој во меѓувреме бил променет или веќе откажан.

Ако booking-от постои, се извршува DELETE и COMMIT.

Ако друга трансакција истовремено врши seat change или друга промена на истиот лет, едната операција ќе почека додека другата го ослободи lock-от.

Procedure Change Flight Airplane - промена на авион

Ова е административна операција каде flight се префрла на друг airplane.

Најважната проверка е дали новиот авион има доволен капацитет за сите веќе постоечки резервации.

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_change_flight_airplane`(
    IN p_flight_id INT,
    IN p_new_airplane_id INT
)
BEGIN
    DECLARE v_old_airplane_id INT DEFAULT NULL;
    DECLARE v_new_capacity INT DEFAULT NULL;
    DECLARE v_booked INT DEFAULT 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    IF p_flight_id IS NULL OR p_flight_id <= 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MYSQL_ERRNO = 30031, MESSAGE_TEXT = 'invalid flight_id';
    END IF;

    IF p_new_airplane_id IS NULL OR p_new_airplane_id <= 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MYSQL_ERRNO = 30032,
                MESSAGE_TEXT = 'invalid new_airplane_id';
    END IF;

    SELECT airplane_id
      INTO v_old_airplane_id
      FROM flight
     WHERE flight_id = p_flight_id
     FOR UPDATE;

    IF v_old_airplane_id IS NULL THEN
        SIGNAL SQLSTATE '45000'
            SET MYSQL_ERRNO = 30033, MESSAGE_TEXT = 'flight not found';
    END IF;

    IF COALESCE(@txn_lab_pause, 0) > 0 THEN
        DO SLEEP(@txn_lab_pause);
    END IF;

    SELECT capacity
      INTO v_new_capacity
      FROM airplane
     WHERE airplane_id = p_new_airplane_id;

    IF v_new_capacity IS NULL THEN
        SIGNAL SQLSTATE '45000'
            SET MYSQL_ERRNO = 30034, MESSAGE_TEXT = 'airplane not found';
    END IF;

    SELECT COUNT(*)
      INTO v_booked
      FROM booking
     WHERE flight_id = p_flight_id;

    IF v_booked > v_new_capacity THEN
        SIGNAL SQLSTATE '45000'
            SET MYSQL_ERRNO = 30035,
                MESSAGE_TEXT = 'new airplane capacity is too small';
    END IF;

    UPDATE flight
       SET airplane_id = p_new_airplane_id
     WHERE flight_id = p_flight_id;

    COMMIT;

    SELECT 'AIRPLANE_CHANGED' AS status,
           p_flight_id AS flight_id,
           v_old_airplane_id AS old_airplane_id,
           p_new_airplane_id AS new_airplane_id,
           v_booked AS booking_count,
           v_new_capacity AS new_capacity;
END$$

DELIMITER ;

Што прави процедурата?

Процедурата прво го заклучува flight редот со FOR UPDATE.

Додека flight-от е заклучен:

1. се чита капацитетот на новиот авион;
2. се брои бројот на постоечки booking записи;
3. се проверува дали сите можат да се сместат во новиот авион.

Ако:

bookings > new_capacity

процедурата враќа:

30035: new airplane capacity is too small

и transaction-от се враќа назад.

Ако капацитетот е доволен, flight.airplane_id се ажурира и се прави COMMIT.

Со тоа не може преку оваа процедура да се создаде flight кој веќе има повеќе резервации од капацитетот на новиот авион.

Тестирање

1. Паралелна резервација на исто седиште

Во тестот две различни сесии се обидуваат да го резервираат истото седиште 26G на flight 100.

Првата сесија повикува:

SET @txn_lab_pause = 10;

CALL sp_reserve_seat(
    100,
    '26G',
    4,
    100.00
);

и резервацијата успешно се креира.

Слика 1. Успешна резервација на 26G за passenger 4.

Во втората сесија истовремено се прави обид да се резервира истото седиште за passenger 5:

CALL sp_reserve_seat(
    100,
    '26G',
    5,
    100.00
);

Слика 2. Втората резервација е одбиена со 30006: seat already occupied.

Овој тест покажува дека две паралелни резервации не можат да завршат со исто (flight_id, seat). Додека првата трансакција го обработува flight 100, втората операција не може да ја наруши истата состојба. По завршувањето на првата резервација, вториот повик се одбива со 30006: seat already occupied.

2. Промена кон веќе зафатено седиште

Во автоматизираниот тест на flight 100 постојат две резервации:

booking 1 -> seat 1A
booking 2 -> seat 1B

Кога првата резервација се обидува да се премести на 1B, процедурата враќа:

30016: target seat already occupied

По ROLLBACK, првата резервација останува на 1A, а втората на 1B.

Со ова се проверува атомичноста на sp_change_seat: ако промената не може целосно да се изврши, старата состојба се задржува.

3. Промена и откажување на резервации

При паралелниот тест една сесија го менува седиштето на една резервација од 1A во 1C, додека друга сесија откажува друга резервација на истиот лет.

Поради locking на flight редот, операциите над резервациите за истиот лет се серијализираат и се извршуваат една по друга.

4. Проверка на капацитет

За flight 200 беше поставен авион со capacity 1.

Две сесии се обидуваат да резервираат различни седишта. Првата резервација успева, а втората добива:

30007: flight is full

На крај останува точно една резервација.

Овој тест е различен од UNIQUE проверката: седиштата можат да бидат различни, но вкупниот број на booking записи сепак не смее да го надмине капацитетот.

5. Промена кон авион со помал капацитет

Flight 100 користи airplane 1 со capacity 2 и има две постоечки резервации.

Се прави обид летот да се префрли на airplane 2, кој има capacity 1:

CALL sp_change_flight_airplane(100, 2);

Слика 3. Промената е одбиена со 30035: new airplane capacity is too small.

Потоа се проверува состојбата на летот:

Слика 4. Flight 100 и понатаму користи airplane 1 со capacity 2.

Бројот на резервации исто така останува непроменет:

Слика 5. По неуспешната промена остануваат двете резервации.

Со тоа се покажува дека процедурата ја одбива промената пред да се создаде состојба bookings > capacity, а по грешката се задржува претходната состојба.

Резултати од валидацијата

Покрај рачните Workbench проверки, сценаријата беа извршени и како одделни автоматизирани тестови.

Сценарио	Очекувано	Резултат
Исто седиште	Само една резервација	PASS / 30006
Capacity = 1	Нема overbooking	PASS / 30007, count = 1
Зафатено target седиште	Старата позиција останува	PASS / 30016
Change vs cancel	Операциите се серијализираат	PASS
Aircraft downgrade	Промената се одбива	PASS / 30035

По автоматизираните проверки:

duplicate_groups = 0
capacity_violations = 0

со што не беше пронајдена состојба со дуплирано седиште или надминат капацитет.

Заклучок

Тестовите покажуваат дека UNIQUE ограничувањето е доволно за директно спречување на две исти (flight_id, seat) вредности, но не е доволно за контрола на вкупниот капацитет.

За контролата на капацитетот и паралелните промени е потребна трансакциска логика која ја заклучува состојбата на летот додека се вршат проверките и промените. Со FOR UPDATE, COMMIT и ROLLBACK, процедурите при запишување преку нив спречуваат overbooking и обезбедуваат неуспешната операција да не остави делумно изменета состојба.