Changes between Version 51 and Version 52 of Transactions


Ignore:
Timestamp:
09/10/26 23:13:32 (5 hours ago)
Author:
222004
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • Transactions

    v51 v52  
    33=== Вовед
    44
    5 Во рамките на оваа фаза, целта е да се прикаже како еден ваков систем за авионски резервации управува со паралелни барања од повеќе корисници во исто време, без да се наруши конзистентноста на податоците и практики за истото во еден MySQL сервер.
     5Кај систем за авионски резервации повеќе корисници можат истовремено да резервираат, менуваат или откажуваат резервации за истиот лет. Без трансакции и locking, две операции можат паралелно да ја прочитаат истата состојба и да доведат до неконзистентни податоци.
    66
    7 Фокусот е на тоа системот да гарантира '''конзистентна продажба и резервација на седишта''' односно да не може да се случи две различни резервации да завршат со исто седиште на ист лет, и да нема ситуација на '''overbooking''' (повеќе резервирани места од капацитетот на авионот).
     7Во оваа фаза се проверуваат четири правила:
    88
    9 Покрај тоа, анализираме како системот треба правилно да се однесува при '''паралелни промени врз исти податоци''', како на пример:
    10 * промена на седиште
    11 * откажување резервација
    12 * промени во распоредот на летот.
     91. Едно седиште не смее да биде резервирано двапати на ист лет. \\
     102. Бројот на резервации не смее да го надмине капацитетот на авионот. \\
     113. Промена или откажување на резервација мора да биде атомска операција. \\
     124. Промена на авион не смее да создаде состојба во која бројот на резервации е поголем од новиот капацитет.
    1313
    14 Овие операции во пракса често се случуваат истовремено од различни корисници и без трансакции и locking  можат да доведат до неконзистентни резултати, „изгубени” промени или некоректен број на резервации.
     14Имплементацијата и автоматизираната валидација се изведени во изолираната `airportdb_transactions_lab`, со три InnoDB табели: `airplane`, `flight` и `booking`.
    1515
    16 === Core  transactional domain во airportdb
    17 
    18 '''booking''' – централната табела за резервации. Токму тука најчесто се појавуваат concurrency проблеми , бидејќи повеќе корисници можат истовремено да направат booking (или менуваат) на седишта за ист лет.
    19 
    20 [[Image(F2 IMG 1.png)]]
    21 
    22 '''flight''' - табела што го дефинира летот (од каде до каде, време на поаѓање и пристигнување, авиокомпанија и кој авион го извршува летот). Оваа табела е важна затоа што секоја резервација се врзува за конкретен лет, а промени во летот (на пример reschedule или промена на авион) имаат директно влијание врз валидноста на постоечките резервации.
    23 
    24 [[Image(F2 IMG 2.png)]]
    25 
    26 '''airplane''' - табела која го носи најважниот капацитетен параметар: capacity. Ова е основата за контролирање на overbooking. Дури и кога различни седишта се резервираат паралелно, системот мора да осигури дека вкупниот број резервации за летот не го надминува капацитетот на авионот кој го извршува летот.
    27 
    28 [[Image(F2 IMG 3.png)]]
    29 
    30 Дополнително, постојат и '''поддржувачки табли''' кои се важни за реалистичност и traceability:
    31 
    32 * '''passenger''' и '''passengerdetails''' – иако не се директниот извор на concurrency проблемите, тие често учествуваат во транзакции (на пример при креирање нов патник + резервација во една атомска операција).
    33 * '''flight_log''' – корисна за audit (логирање на историја кој и кога направил промена). Во реални системи audit логиката е критична, особено при debugging на проблеми со конкурентност и при следење на промени во распоредите или атрибутите на летовите.
    34 
    35 
    36 === Дефинирани правила за конзистентност во airportdb
    37 
    38 Овие правила ја опишуваат „точната“ состојба на системот и служат како основа за дизајн на транзакции, заклучувања и ограничувања во базата.
    39 
    40 1) Единственост на седиште по лет \\
    41 2) Почитување на капацитетот (без overbooking) \\
    42 3) Атомичност на промени (seat change / cancel) \\
    43 4) Reschedule без нарушување на капацитет \\
     16Во `booking` постои UNIQUE ограничување над:
    4417
    4518{{{
    46 ALTER TABLE booking
    47 ADD CONSTRAINT uq_booking_flight_seat UNIQUE (flight_id, seat);
    48 
    49 ALTER TABLE booking
    50 ADD INDEX ix_booking_flight (flight_id);
    51 
    52 ALTER TABLE flight
    53 ADD INDEX ix_flight_airplane (airplane_id);
    54 
    55 ALTER TABLE airplane
    56 ADD INDEX ix_airplane_id_capacity (airplane_id, capacity);
     19(flight_id, seat)
    5720}}}
    5821
    59 UNIQUE (flight_id, seat) e најчист race-condition stopper за кога двајца купуваат исто седиште, ама во реален систем имаме и '''перформанси''' и '''lock behavior'''. Во concurrency сценарија (многу паралелни резервации), сакаме:
    60 * критичните проверки да бидат '''брзи''' (да не држат locks предолго),
    61 * да избегнеме '''full table scans''' што прават повеќе блокирања и оптоварување
     22со што на ниво на база се спречува две резервации да завршат со исто седиште на ист лет.
    6223
    63 Затоа додадовме и индекси на колоните што најчесто се користат во транзакциските операции.
     24=== Реализација
    6425
    65 === Реални сценарија
     26За операциите се користат четири stored procedures:
    6627
    67 ==== Сценарио 1
     28|| '''Процедура''' || '''Намена''' ||
     29|| `sp_reserve_seat(flight_id, seat, passenger_id, price)` || Резервира седиште и проверува дали седиштето е зафатено и дали летот има слободен капацитет. ||
     30|| `sp_change_seat(booking_id, new_seat)` || Атомски го менува седиштето и ја одбива промената ако новото седиште е веќе зафатено. ||
     31|| `sp_cancel_booking(booking_id)` || Безбедно ја откажува резервацијата. ||
     32|| `sp_change_flight_airplane(flight_id, new_airplane_id)` || Го менува авионот само ако постоечките резервации се вклопуваат во новиот капацитет. ||
    6833
    69 Нашата база е дел од апликација за онлајн резервации. Во peak часови (петок вечер), стотици корисници паралелно купуваат седишта за ист лет.
    70 1. '''Корисник1''' и '''Корисник2''' во ист момент резервираат седиште 12A” на истиот лет. \\
    71 Без трансакции/ограничувања, може и двата корисници да добијат потвра => overbooking. \\
    72 2. '''Корисник1''' потоа сака да се премести од '''12A''' на '''14C'''. Во истиот момент, друг корисник се обидува да го земе 14C.\\
    73 Ако промената не е атомична: старото седиште ослободено, новото неуспешно, или дупликат. \\
    74 3. Паралелно, оператор од авиокомпанијата прави reschedule: го менува авионот за летот со помал капацитет (поради технички проблем). \\
    75 Ако системот дозволи промена без да провери колку резервации веќе постојат, може да создаде лет каде bookings > capacity. Ова мора да се блокира или одбие. \\
     34Процедурите користат `START TRANSACTION`, `FOR UPDATE`, `COMMIT` и `ROLLBACK`. Редот од `flight` се користи како заедничка точка за locking на операциите што работат над истиот лет.
    7635
    77 Затоа овие проблеми во процедурите ги решаваме со трансакции така што:
    78 * ги заклучуваме релевантните редови додека трае проверката,
    79 * се осигуруваме дека операцијата е атомична (COMMIT или ROLLBACK),
    80 
    81 ===== Procedure Reserve Seat (спречува overbooking + double seat)
    82 Затоа што имаме “check-then-write” логика: прво проверуваме капацитет/број резервации, па insert. Без транзакција, две сесии можат да поминат проверки паралелно и да го надминат капацитетот.
     36При SQL грешка се извршува:
    8337
    8438{{{
    85 DELIMITER $$
    86 
    87 CREATE PROCEDURE sp_reserve_seat(
    88   IN p_flight_id INT,
    89   IN p_seat CHAR(4),
    90   IN p_passenger_id INT,
    91   IN p_price DECIMAL(10,2)
    92 )
    93 BEGIN
    94   DECLARE v_capacity INT;
    95   DECLARE v_booked INT;
    96   DECLARE exit handler FOR SQLEXCEPTION
    97   BEGIN
    98     ROLLBACK;
    99     SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Reservation failed (transaction rolled back).';
    100   END;
    101 
    102   START TRANSACTION;
    103 
    104   SELECT a.capacity INTO v_capacity
    105   FROM flight f
    106   JOIN airplane a ON a.airplane_id = f.airplane_id
    107   WHERE f.flight_id = p_flight_id
    108   FOR UPDATE;
    109 
    110   SELECT COUNT(*) INTO v_booked
    111   FROM booking
    112   WHERE flight_id = p_flight_id
    113   FOR UPDATE;
    114 
    115   IF v_booked >= v_capacity THEN
    116     ROLLBACK;
    117     SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Flight is full (capacity exceeded).';
    118   END IF;
    119 
    120   INSERT INTO booking (flight_id, seat, passenger_id, price)
    121   VALUES (p_flight_id, p_seat, p_passenger_id, p_price);
    122 
    123   COMMIT;
    124 END$$
    125 
    126 DELIMITER ;
     39ROLLBACK;
     40RESIGNAL;
    12741}}}
    12842
    129 '''Што прави процедурата?'''
     43со што промените од неуспешната трансакција не остануваат запишани.
    13044
    131 Оваа stored procedure безбедно резервира седиште на лет, спречувајќи overbooking и двојна резервација на исто седиште. Користи row-level locking (FOR UPDATE) за да ја заштити "check-then-write" логиката, каде што прво се проверува капацитетот и бројот на резервации, па потоа се прави insert. Без транзакција, две паралелни сесии би можеле истовремено да ги поминат проверките и да го надминат капацитетот. \\
     45`@txn_lab_pause` се користеше само при тестирање за намерно да се задржи една трансакција неколку секунди и полесно да се репродуцира паралелно извршување. Не претставува дел од бизнис логиката.
    13246
    133 Процедурата:
    134 1. Го заклучува летот и го чита капацитетот на авионот
    135 2. Го брои бројот на постоечки резервации (со lock)
    136 3. Проверува дали има слободно место
    137 4. Прави insert на новата резервација
    138 5. Прави commit или rollback при грешка
     47=== Тестирање
    13948
    140 Заклучувањето осигурува дека проверката на капацитетот и insert-от се случуваат атомично, спречувајќи race conditions. Ако седиштето е веќе зафатено, unique constraint ќе предизвика rollback, враќајќи ја базата во конзистентна состојба.
     49==== 1. Паралелна резервација на исто седиште
    14150
    142 ===== Procedure Change Seat (атомична промена)
    143 Ако новото седиште е веќе земено, не смееме да го изгубиме старото -> промената мора да биде all or nothing.
     51Во тестот две различни сесии се обидуваат да го резервираат истото седиште `26G` на flight `100`.
     52
     53Првата сесија повикува:
    14454
    14555{{{
    146 DELIMITER $$
     56SET @txn_lab_pause = 10;
    14757
    148 CREATE PROCEDURE sp_change_seat(
    149   IN p_booking_id INT,
    150   IN p_new_seat CHAR(4)
    151 )
    152 BEGIN
    153   DECLARE v_flight_id INT;
    154   DECLARE exit handler FOR SQLEXCEPTION
    155   BEGIN
    156     ROLLBACK;
    157     SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Seat change failed (transaction rolled back).';
    158   END;
    159 
    160   START TRANSACTION;
    161 
    162   SELECT flight_id INTO v_flight_id
    163   FROM booking
    164   WHERE booking_id = p_booking_id
    165   FOR UPDATE;
    166 
    167   UPDATE booking
    168   SET seat = p_new_seat
    169   WHERE booking_id = p_booking_id;
    170 
    171   COMMIT;
    172 END$$
    173 
    174 DELIMITER ;
     58CALL sp_reserve_seat(
     59    100,
     60    '26G',
     61    4,
     62    100.00
     63);
    17564}}}
    17665
    177 '''Што прави процедурата?'''
     66и резервацијата успешно се креира.
    17867
    179 Оваа stored procedure атомично го менува седиштето на резервација, осигурувајќи "all or nothing" принцип. Користи row-level locking (FOR UPDATE) за да спречи паралелни модификации на истата резервација (на пр. откажување или друга промена на седиште). \\
     68[[Image(F2_TXN_same_seat_A_success.PNG)]]
    18069
    181 Процедурата:
    182 1. Го заклучува записот за резервацијата
    183 2. Го менува седиштето
    184 3. Прави commit или rollback при грешка
     70'''Слика 1.''' Успешна резервација на `26G` за passenger `4`.
    18571
    186 Ако новото седиште е веќе земено (unique constraint violation) или ако се случи друга грешка, транзакцијата автоматски прави rollback, што значи дека старото седиште не се губи. Заклучувањето осигурува дека промената е атомична и дека не може да се случи конфликт со други операции (како sp_cancel_booking) кои работат врз истата резервација.
    187 
    188 ===== Procedure Cancel Booking
    189 За да спречиме паралелна модификација (пр. change seat) да се судри со cancel.
     72Во втората сесија истовремено се прави обид да се резервира истото седиште за passenger `5`:
    19073
    19174{{{
    192 DELIMITER $$
    193 
    194 CREATE PROCEDURE sp_cancel_booking(
    195   IN p_booking_id INT
    196 )
    197 BEGIN
    198   DECLARE exit handler FOR SQLEXCEPTION
    199   BEGIN
    200     ROLLBACK;
    201     SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cancel failed (transaction rolled back).';
    202   END;
    203 
    204   START TRANSACTION;
    205 
    206   SELECT booking_id
    207   FROM booking
    208   WHERE booking_id = p_booking_id
    209   FOR UPDATE;
    210 
    211   DELETE FROM booking
    212   WHERE booking_id = p_booking_id;
    213 
    214   COMMIT;
    215 END$$
    216 
    217 DELIMITER ;
     75CALL sp_reserve_seat(
     76    100,
     77    '26G',
     78    5,
     79    100.00
     80);
    21881}}}
    21982
    220 '''Што прави процедурата?'''
     83[[Image(F2_TXN_same_seat_B_error.PNG)]]
    22184
    222 Оваа stored procedure безбедно откажува резервација, спречувајќи конфликти со други паралелни операции (на пр. промена на седиште). Користи row-level locking (FOR UPDATE) за да осигура дека ако некоја друга трансakција се обидува да ја модифицира истата резервација истовремено (како sp_change_seat), едната ќе мора да почека додека другата заврши. \\
     85'''Слика 2.''' Втората резервација е одбиена со `30006: seat already occupied`.
    22386
    224 Процедурата:
    225 1. Го заклучува записот за резервацијата
    226 2. Го брише записот
    227 3. Прави commit или rollback при грешка
     87Овој тест покажува дека две паралелни резервации не можат да завршат со исто `(flight_id, seat)`. Додека првата трансакција го обработува flight `100`, втората операција не може да ја наруши истата состојба. По завршувањето на првата резервација, вториот повик се одбива со `30006: seat already occupied`.
    22888
    229 Заклучувањето спречува race conditions каде што две операции би можеле да работат врз истата резервација во ист момент, осигурувајќи конзистентност на податоците.
     89==== 2. Промена кон веќе зафатено седиште
    23090
    231 ===== Procedure Reschedule
    232 Ова е административна операција: летот добива друг авион (може и reschedule, ама најкритичен е капацитетот).
    233 * Бројот на bookings и капацитетот мора да се проверат во истиот „момент”.
    234 * Ако паралелно доаѓаат нови reservations, сакаме да го блокираме конфликтот.
     91Во автоматизираниот тест на flight `100` постојат две резервации:
    23592
    23693{{{
    237 DELIMITER $$
    238 
    239 CREATE PROCEDURE sp_change_flight_airplane(
    240   IN p_flight_id INT,
    241   IN p_new_airplane_id INT
    242 )
    243 BEGIN
    244   DECLARE v_booked INT;
    245   DECLARE v_new_capacity INT;
    246 
    247   DECLARE exit handler FOR SQLEXCEPTION
    248   BEGIN
    249     ROLLBACK;
    250     SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Reschedule failed (transaction rolled back).';
    251   END;
    252 
    253   START TRANSACTION;
    254 
    255   SELECT flight_id
    256   FROM flight
    257   WHERE flight_id = p_flight_id
    258   FOR UPDATE;
    259 
    260   SELECT COUNT(*) INTO v_booked
    261   FROM booking
    262   WHERE flight_id = p_flight_id
    263   FOR UPDATE;
    264 
    265   SELECT capacity INTO v_new_capacity
    266   FROM airplane
    267   WHERE airplane_id = p_new_airplane_id
    268   FOR UPDATE;
    269 
    270   IF v_booked > v_new_capacity THEN
    271     ROLLBACK;
    272     SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot change aircraft: existing bookings exceed new capacity.';
    273   END IF;
    274 
    275   UPDATE flight
    276   SET airplane_id = p_new_airplane_id
    277   WHERE flight_id = p_flight_id;
    278 
    279   COMMIT;
    280 END$$
    281 
    282 DELIMITER ;
     94booking 1 -> seat 1A
     95booking 2 -> seat 1B
    28396}}}
    28497
    285 '''Што прави процедурата?'''
     98Кога првата резервација се обидува да се премести на `1B`, процедурата враќа:
    28699
    287 Оваа stored procedure безбедно го менува авионот доделен на летот, притоа спречувајќи preBooking (overbooking). Користи row-level locking (FOR UPDATE) за да осигура дека ако повеќе операции се случуваат истовремено (како што се нови резервации додека се менува авионот), конфликтите се блокираат. \\
     100{{{
     10130016: target seat already occupied
     102}}}
    288103
    289 Процедурата:
    290 1. Го заклучува записот за летот
    291 2. Ги брои постоечките резервации (со lock)
    292 3. Го зема капацитетот на новиот авион (со lock)
    293 4. Валидира дека постоечките резервации се вклопуваат во новиот авион
    294 5. Го ажурира летот или прави rollback ако капацитетот е надминат
     104По `ROLLBACK`, првата резервација останува на `1A`, а втората на `1B`.
    295105
    296 Заклучувањето осигурува дека бројот на резервации и проверката на капацитетот се случуваат атомично, спречувајќи race conditions.
     106Со ова се проверува атомичноста на `sp_change_seat`: ако промената не може целосно да се изврши, старата состојба се задржува.
     107
     108==== 3. Промена и откажување на резервации
     109
     110При паралелниот тест една сесија го менува седиштето на една резервација од `1A` во `1C`, додека друга сесија откажува друга резервација на истиот лет.
     111
     112Поради locking на `flight` редот, операциите над резервациите за истиот лет се серијализираат и се извршуваат една по друга.
     113
     114==== 4. Проверка на капацитет
     115
     116За flight `200` беше поставен авион со capacity `1`.
     117
     118Две сесии се обидуваат да резервираат различни седишта. Првата резервација успева, а втората добива:
     119
     120{{{
     12130007: flight is full
     122}}}
     123
     124На крај останува точно една резервација.
     125
     126Овој тест е различен од UNIQUE проверката: седиштата можат да бидат различни, но вкупниот број на booking записи сепак не смее да го надмине капацитетот.
     127
     128==== 5. Промена кон авион со помал капацитет
     129
     130Flight `100` користи airplane `1` со capacity `2` и има две постоечки резервации.
     131
     132Се прави обид летот да се префрли на airplane `2`, кој има capacity `1`:
     133
     134{{{
     135CALL sp_change_flight_airplane(100, 2);
     136}}}
     137
     138[[Image(F2_TXN_downgrade_error.PNG)]]
     139
     140'''Слика 3.''' Промената е одбиена со `30035: new airplane capacity is too small`.
     141
     142Потоа се проверува состојбата на летот:
     143
     144[[Image(F2_TXN_downgrade_flight_state.PNG)]]
     145
     146'''Слика 4.''' Flight `100` и понатаму користи airplane `1` со capacity `2`.
     147
     148Бројот на резервации исто така останува непроменет:
     149
     150[[Image(F2_TXN_downgrade_booking_count.PNG)]]
     151
     152'''Слика 5.''' По неуспешната промена остануваат двете резервации.
     153
     154Со тоа се покажува дека процедурата ја одбива промената пред да се создаде состојба `bookings > capacity`, а по грешката се задржува претходната состојба.
     155
     156=== Резултати од валидацијата
     157
     158Покрај рачните Workbench проверки, сценаријата беа извршени и како одделни автоматизирани тестови.
     159
     160|| '''Сценарио''' || '''Очекувано''' || '''Резултат''' ||
     161|| Исто седиште || Само една резервација || PASS / `30006` ||
     162|| Capacity = 1 || Нема overbooking || PASS / `30007`, count = 1 ||
     163|| Зафатено target седиште || Старата позиција останува || PASS / `30016` ||
     164|| Change vs cancel || Операциите се серијализираат || PASS ||
     165|| Aircraft downgrade || Промената се одбива || PASS / `30035` ||
     166
     167По автоматизираните проверки:
     168
     169{{{
     170duplicate_groups = 0
     171capacity_violations = 0
     172}}}
     173
     174со што не беше пронајдена состојба со дуплирано седиште или надминат капацитет.
     175
     176=== Заклучок
     177
     178Тестовите покажуваат дека UNIQUE ограничувањето е доволно за директно спречување на две исти `(flight_id, seat)` вредности, но не е доволно за контрола на вкупниот капацитет.
     179
     180За контролата на капацитетот и паралелните промени е потребна трансакциска логика која ја заклучува состојбата на летот додека се вршат проверките и промените. Со `FOR UPDATE`, `COMMIT` и `ROLLBACK`, процедурите при запишување преку нив спречуваат overbooking и обезбедуваат неуспешната операција да не остави делумно изменета состојба.