Трансакции

Вовед

Во рамките на оваа фаза, целта е да се прикаже како еден ваков систем за авионски резервации управува со паралелни барања од повеќе корисници во исто време, без да се наруши конзистентноста на податоците и практики за истото во еден 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. Корисник1 и Корисник2 во ист момент резервираат седиште 12A” на истиот лет.
   

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

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

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

3. Паралелно, оператор од авиокомпанијата прави 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 ;

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 ;

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 ;

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 ;