Changes between Version 40 and Version 41 of Transactions

Timestamp:

02/10/26 18:11:04 (3 weeks ago)

Author:

222039

Comment:

--

Transactions

@@ -79 +79 @@
 * се осигуруваме дека операцијата е атомична (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 ;
+}}}
+
+