Changes between Version 52 and Version 53 of Transactions

Timestamp:

09/11/26 09:12:08 (33 hours ago)

Author:

222004

Comment:

--

Transactions

@@ -3 +3 @@
 === Вовед
 
-Кај систем за авионски резервации повеќе корисници можат истовремено да резервираат, менуваат или откажуваат резервации за истиот лет. Без трансакции и locking, две операции можат паралелно да ја прочитаат истата состојба и да доведат до неконзистентни податоци.
-
-Во оваа фаза се проверуваат четири правила:
-
-1. Едно седиште не смее да биде резервирано двапати на ист лет. \\
-2. Бројот на резервации не смее да го надмине капацитетот на авионот. \\
-3. Промена или откажување на резервација мора да биде атомска операција. \\
-4. Промена на авион не смее да создаде состојба во која бројот на резервации е поголем од новиот капацитет.
-
-Имплементацијата и автоматизираната валидација се изведени во изолираната `airportdb_transactions_lab`, со три InnoDB табели: `airplane`, `flight` и `booking`.
+Во рамките на оваа фаза, целта е да се прикаже како еден систем за авионски резервации управува со паралелни барања од повеќе корисници во исто време, без да се наруши конзистентноста на податоците.
+
+Фокусот е системот да гарантира конзистентна продажба и резервација на седишта:
+* две различни резервации да не можат да завршат со исто седиште на ист лет;
+* да нема 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 ограничување над:
@@ -20 +36 @@
 }}}
 
-со што на ниво на база се спречува две резервации да завршат со исто седиште на ист лет.
-
-=== Реализација
-
-За операциите се користат четири stored procedures:
-
-|| '''Процедура''' || '''Намена''' ||
-|| `sp_reserve_seat(flight_id, seat, passenger_id, price)` || Резервира седиште и проверува дали седиштето е зафатено и дали летот има слободен капацитет. ||
-|| `sp_change_seat(booking_id, new_seat)` || Атомски го менува седиштето и ја одбива промената ако новото седиште е веќе зафатено. ||
-|| `sp_cancel_booking(booking_id)` || Безбедно ја откажува резервацијата. ||
-|| `sp_change_flight_airplane(flight_id, new_airplane_id)` || Го менува авионот само ако постоечките резервации се вклопуваат во новиот капацитет. ||
-
-Процедурите користат `START TRANSACTION`, `FOR UPDATE`, `COMMIT` и `ROLLBACK`. Редот од `flight` се користи како заедничка точка за locking на операциите што работат над истиот лет.
-
-При SQL грешка се извршува:
-
-{{{
-ROLLBACK;
-RESIGNAL;
-}}}
-
-со што промените од неуспешната трансакција не остануваат запишани.
-
-`@txn_lab_pause` се користеше само при тестирање за намерно да се задржи една трансакција неколку секунди и полесно да се репродуцира паралелно извршување. Не претставува дел од бизнис логиката.
+Ова ограничување претставува дополнителна заштита на ниво на база: две резервации не можат да имаат ист `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 кој веќе има повеќе резервации од капацитетот на новиот авион.
 
 === Тестирање