Transactions – dba_202526_airportdb

Context Navigation

Трансакции

Вовед

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

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

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

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

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

Core transactional domain во airportdb

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

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

airplane - табела која го носи најважниот капацитетен параметар: capacity. Ова е основата за контролирање на overbooking. Дури и кога различни седишта се резервираат паралелно, системот мора да осигури дека вкупниот број резервации за летот не го надминува капацитетот на авионот кој го извршува летот.

Дополнително, постојат и поддржувачки табли кои се важни за реалистичност и traceability:

passenger и passengerdetails – иако не се директниот извор на concurrency проблемите, тие често учествуваат во транзакции (на пример при креирање нов патник + резервација во една атомска операција).
flight_log – корисна за audit (логирање на историја кој и кога направил промена). Во реални системи audit логиката е критична, особено при debugging на проблеми со конкурентност и при следење на промени во распоредите или атрибутите на летовите.

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

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

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

ALTER TABLE booking
ADD CONSTRAINT uq_booking_flight_seat UNIQUE (flight_id, seat);

ALTER TABLE booking
ADD INDEX ix_booking_flight (flight_id);

ALTER TABLE flight
ADD INDEX ix_flight_airplane (airplane_id);

ALTER TABLE airplane
ADD INDEX ix_airplane_id_capacity (airplane_id, capacity);

UNIQUE (flight_id, seat) e најчист race-condition stopper за кога двајца купуваат исто седиште, ама во реален систем имаме и перформанси и lock behavior. Во concurrency сценарија (многу паралелни резервации), сакаме:

критичните проверки да бидат брзи (да не држат locks предолго),
да избегнеме full table scans што прават повеќе блокирања и оптоварување

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

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

Сценарио 1

Нашата база е дел од апликација за онлајн резервации. Во peak часови (петок вечер), стотици корисници паралелно купуваат седишта за ист лет.

Корисник1 и Корисник2 во ист момент резервираат седиште 12A” на истиот лет.

Без трансакции/ограничувања, може и двата корисници да добијат потвра => overbooking.

Корисник1 потоа сака да се премести од 12A на 14C. Во истиот момент, друг корисник се обидува да го земе 14C.

Ако промената не е атомична: старото седиште ослободено, новото неуспешно, или дупликат.

Паралелно, оператор од авиокомпанијата прави reschedule: го менува авионот за летот со помал капацитет (поради технички проблем).

Ако системот дозволи промена без да провери колку резервации веќе постојат, може да создаде лет каде bookings > capacity. Ова мора да се блокира или одбие.

Затоа овие проблеми во процедурите ги решаваме со трансакции така што:

ги заклучуваме релевантните редови додека трае проверката,
се осигуруваме дека операцијата е атомична (COMMIT или ROLLBACK),

Procedure Reserve Seat (спречува overbooking + double seat)

Затоа што имаме “check-then-write” логика: прво проверуваме капацитет/број резервации, па insert. Без транзакција, две сесии можат да поминат проверки паралелно и да го надминат капацитетот.

DELIMITER $$

CREATE PROCEDURE sp_reserve_seat(
  IN p_flight_id INT,
  IN p_seat CHAR(4),
  IN p_passenger_id INT,
  IN p_price DECIMAL(10,2)
)
BEGIN
  DECLARE v_capacity INT;
  DECLARE v_booked INT;
  DECLARE exit handler FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Reservation failed (transaction rolled back).';
  END;

  START TRANSACTION;

  SELECT a.capacity INTO v_capacity
  FROM flight f
  JOIN airplane a ON a.airplane_id = f.airplane_id
  WHERE f.flight_id = p_flight_id
  FOR UPDATE;

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

  IF v_booked >= v_capacity THEN
    ROLLBACK;
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Flight is full (capacity exceeded).';
  END IF;

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

  COMMIT;
END$$

DELIMITER ;

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

Оваа stored procedure безбедно резервира седиште на лет, спречувајќи overbooking и двојна резервација на исто седиште. Користи row-level locking (FOR UPDATE) за да ја заштити "check-then-write" логиката, каде што прво се проверува капацитетот и бројот на резервации, па потоа се прави insert. Без транзакција, две паралелни сесии би можеле истовремено да ги поминат проверките и да го надминат капацитетот.

Процедурата:

Го заклучува летот и го чита капацитетот на авионот
Го брои бројот на постоечки резервации (со lock)
Проверува дали има слободно место
Прави insert на новата резервација
Прави commit или rollback при грешка

Заклучувањето осигурува дека проверката на капацитетот и insert-от се случуваат атомично, спречувајќи race conditions. Ако седиштето е веќе зафатено, unique constraint ќе предизвика rollback, враќајќи ја базата во конзистентна состојба.

Procedure Change Seat (атомична промена)

Ако новото седиште е веќе земено, не смееме да го изгубиме старото -> промената мора да биде all or nothing.

DELIMITER $$

CREATE PROCEDURE sp_change_seat(
  IN p_booking_id INT,
  IN p_new_seat CHAR(4)
)
BEGIN
  DECLARE v_flight_id INT;
  DECLARE exit handler FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Seat change failed (transaction rolled back).';
  END;

  START TRANSACTION;

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

  UPDATE booking
  SET seat = p_new_seat
  WHERE booking_id = p_booking_id;

  COMMIT;
END$$

DELIMITER ;

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

Оваа stored procedure атомично го менува седиштето на резервација, осигурувајќи "all or nothing" принцип. Користи row-level locking (FOR UPDATE) за да спречи паралелни модификации на истата резервација (на пр. откажување или друга промена на седиште).

Процедурата:

Го заклучува записот за резервацијата
Го менува седиштето
Прави commit или rollback при грешка

Ако новото седиште е веќе земено (unique constraint violation) или ако се случи друга грешка, транзакцијата автоматски прави rollback, што значи дека старото седиште не се губи. Заклучувањето осигурува дека промената е атомична и дека не може да се случи конфликт со други операции (како sp_cancel_booking) кои работат врз истата резервација.

Procedure Cancel Booking

За да спречиме паралелна модификација (пр. change seat) да се судри со cancel.

DELIMITER $$

CREATE PROCEDURE sp_cancel_booking(
  IN p_booking_id INT
)
BEGIN
  DECLARE exit handler FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cancel failed (transaction rolled back).';
  END;

  START TRANSACTION;

  SELECT booking_id
  FROM booking
  WHERE booking_id = p_booking_id
  FOR UPDATE;

  DELETE FROM booking
  WHERE booking_id = p_booking_id;

  COMMIT;
END$$

DELIMITER ;

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

Оваа stored procedure безбедно откажува резервација, спречувајќи конфликти со други паралелни операции (на пр. промена на седиште). Користи row-level locking (FOR UPDATE) за да осигура дека ако некоја друга трансakција се обидува да ја модифицира истата резервација истовремено (како sp_change_seat), едната ќе мора да почека додека другата заврши.

Процедурата:

Го заклучува записот за резервацијата
Го брише записот
Прави commit или rollback при грешка

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

Procedure Reschedule

Ова е административна операција: летот добива друг авион (може и reschedule, ама најкритичен е капацитетот).

Бројот на bookings и капацитетот мора да се проверат во истиот „момент”.
Ако паралелно доаѓаат нови reservations, сакаме да го блокираме конфликтот.

DELIMITER $$

CREATE PROCEDURE sp_change_flight_airplane(
  IN p_flight_id INT,
  IN p_new_airplane_id INT
)
BEGIN
  DECLARE v_booked INT;
  DECLARE v_new_capacity INT;

  DECLARE exit handler FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Reschedule failed (transaction rolled back).';
  END;

  START TRANSACTION;

  SELECT flight_id
  FROM flight
  WHERE flight_id = p_flight_id
  FOR UPDATE;

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

  SELECT capacity INTO v_new_capacity
  FROM airplane
  WHERE airplane_id = p_new_airplane_id
  FOR UPDATE;

  IF v_booked > v_new_capacity THEN
    ROLLBACK;
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot change aircraft: existing bookings exceed new capacity.';
  END IF;

  UPDATE flight
  SET airplane_id = p_new_airplane_id
  WHERE flight_id = p_flight_id;

  COMMIT;
END$$

DELIMITER ;

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

Оваа stored procedure безбедно го менува авионот доделен на летот, притоа спречувајќи preBooking (overbooking). Користи row-level locking (FOR UPDATE) за да осигура дека ако повеќе операции се случуваат истовремено (како што се нови резервации додека се менува авионот), конфликтите се блокираат.

Процедурата:

Го заклучува записот за летот
Ги брои постоечките резервации (со lock)
Го зема капацитетот на новиот авион (со lock)
Валидира дека постоечките резервации се вклопуваат во новиот авион
Го ажурира летот или прави rollback ако капацитетот е надминат

Заклучувањето осигурува дека бројот на резервации и проверката на капацитетот се случуваат атомично, спречувајќи race conditions.

Last modified 3 weeks ago Last modified on 02/10/26 19:57:31

Attachments (3)

F2 IMG 1.png (25.8 KB ) - added by 222039 3 weeks ago.
F2 IMG 2.png (35.5 KB ) - added by 222039 3 weeks ago.
F2 IMG 3.png (15.0 KB ) - added by 222039 3 weeks ago.

Download all attachments as: .zip

Note: See TracWiki for help on using the wiki.

Download in other formats:

Plain Text