Changes between Version 44 and Version 45 of Transactions

Timestamp:

02/10/26 18:15:07 (3 weeks ago)

Author:

222039

Comment:

--

Transactions

@@ -194 +194 @@
 }}}
 
+===== 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 ;
+}}}