conspects.iliay.ar

Statements

Спланируйте транзакции и выберите их уровни изоляции для базы данных Airline.

  1. Для каждой хранимой процедуры из предыдущего домашнего задания выберите минимальный допустимый уровень изоляции транзакций (с обоснованием).
  2. Реализуйте сценарий работы:
    1. Запрос списка свободных мест.
    2. Отображение списка свободных мест пользователю.
    3. Бронирование или покупка места, выбранного пользователем.

1

1

-- Аномалии:
--   - Косая запись - нет, read only
--   - Фантомная запись - нет, один SELECT
--   - Неповторяемое чтение - нет, один SELECT
--   - Грязное чтение - есть, можем прочитать, к примеру,
--     что место куплено, хотя транзакция не завершена
-- Избавляемся от аномалий:
--   - Грязное чтение
-- Достаточно уровня изоляции Read commited
START TRANSACTION READ ONLY ISOLATION LEVEL READ COMMITTED;

2

-- Аномалии:
--   - Косая запись - нет, изменяем данные только в Tickets
--   - Фантомная запись - нет, читаем один раз одну строчку из Tickets
--   - Неповторяемое чтение - есть, читаем несколько раз из Flights
--   - Грязное чтение - есть, можем прочитать, к примеру,
--     что место куплено, хотя транзакция не завершена
-- Избавляемся от аномалий:
--   - Неповторяемое чтение
--   - Грязное чтение
-- Достаточно уровня изоляции Repeatable read
START TRANSACTION READ WRITE ISOLATION LEVEL REPEATABLE READ;

3

-- Аномалии:
--   - Косая запись - нет, изменяем данные только в Tickets
--   - Фантомная запись - нет, не может произойти мешающие добавление данных в Tickets
--   - Неповторяемое чтение - есть, читаем несколько раз из Flights
--   - Грязное чтение - есть, можем прочитать, к примеру,
--     что место куплено, хотя транзакция не завершена
-- Избавляемся от аномалий:
--   - Неповторяемое чтение
--   - Грязное чтение
-- Достаточно уровня изоляции Repeatable read
START TRANSACTION READ WRITE ISOLATION LEVEL REPEATABLE READ;

4

-- Аномалии:
--   - Косая запись - нет, изменяем данные только в Tickets
--   - Фантомная запись - нет, читаем один раз одну строчку из Tickets
--   - Неповторяемое чтение - есть, читаем несколько раз из Flights
--   - Грязное чтение - есть, можем прочитать, к примеру,
--     что место куплено, хотя транзакция не завершена
-- Избавляемся от аномалий:
--   - Неповторяемое чтение
--   - Грязное чтение
-- Достаточно уровня изоляции Repeatable read
START TRANSACTION READ WRITE ISOLATION LEVEL REPEATABLE READ;

5

-- Аномалии:
--   - Косая запись - нет, изменяем данные только в Tickets
--   - Фантомная запись - нет, не может произойти мешающие добавление данных в Tickets
--   - Неповторяемое чтение - есть, читаем несколько раз из Flights
--   - Грязное чтение - есть, можем прочитать, к примеру,
--     что место куплено, хотя транзакция не завершена
-- Избавляемся от аномалий:
--   - Неповторяемое чтение
--   - Грязное чтение
-- Достаточно уровня изоляции Repeatable read
START TRANSACTION READ WRITE ISOLATION LEVEL REPEATABLE READ;

6

-- Аномалии:
--   - Косая запись - нет, read only
--   - Фантомная запись - нет, читаем один из Tickets
--   - Неповторяемое чтение - есть, читаем несколько раз из Flights
--   - Грязное чтение - есть, можем прочитать, к примеру,
--     что место куплено, хотя транзакция не завершена
-- Избавляемся от аномалий:
--   - Неповторяемое чтение
--   - Грязное чтение
-- Достаточно уровня изоляции Repeatable read
START TRANSACTION READ ONLY ISOLATION LEVEL REPEATABLE READ;

7

-- Аномалии:
--   - Косая запись - нет, изменяем данные только в Tickets
--   - Фантомная запись - нет, читаем один из Tickets
--   - Неповторяемое чтение - есть, читаем несколько раз из Flights
--   - Грязное чтение - есть, можем прочитать, к примеру,
--     что место куплено, хотя транзакция не завершена
-- Избавляемся от аномалий:
--   - Неповторяемое чтение
--   - Грязное чтение
-- Достаточно уровня изоляции Repeatable read
START TRANSACTION READ ONLY ISOLATION LEVEL REPEATABLE READ;

8

-- Аномалии:
--   - Косая запись - нет, изменяем данные только в Tickets
--   - Фантомная запись - есть, может произойти запись в Tickets
--   - Неповторяемое чтение - есть, читаем несколько раз из Flights
--   - Грязное чтение - есть, можем прочитать, к примеру,
--     что место куплено, хотя транзакция не завершена
-- Избавляемся от аномалий:
--   - Фантомная запись
--   - Неповторяемое чтение
--   - Грязное чтение
-- Достаточно уровня изоляции Snapshot
START TRANSACTION READ WRITE ISOLATION LEVEL SNAPSHOT;

2

0

  1. Пользователь предоставляет логин (:UserId) и пароль (:Pass)
  2. Пользователь выбирает рейс (:FlightId)
  3. Получаем список доступных к покупке или бронированию для этого пользователя
  4. Пользователь выбирает место (:SeatNo) и выбирает забронировать или купить
  5. Запрос в зависимости от того что выбрал пользователь, забронировать или купить и от того забронировано ли данное место пользователем. Если запрос не выполнился успешно, нужно запросить список мест заного.

1

START TRANSACTION READ ONLY ISOLATION LEVEL READ COMMITTED;
SELECT SeatNo, BookedUntil FROM FreeOrReservedTickets
  WHERE FlightId = :FlightId
  AND (UserId = :UserId OR UserId IS NULL);
COMMIT;

2

  1. Пользователь вводит логин и пароль, а затем номер рейса
  2. Делается запрос на получение списка свободных или зарезервированных этим пользователейм мест, пользователь видит эти места и их статус (свободно/зарезервировано).
  3. Пользователь выбирает место и
    1. покупает его. Если место было зарезервировано, выполняется запрос 4, если нет то запрос 2
    2. резервирует его (только для свободных мест). Выполняется запрос 1
    3. Продлевает бронь (только если место было забронировано). Выполняется запрос 3
  4. Если запрос завершился с ошбкой, просим пользователя перезапросить список мест, т.к. ошибка означает что место уже забронировано, либо куплено.

3

START TRANSACTION READ ONLY ISOLATION LEVEL SNAPSHOT;
  SELECT Reserve(:UserId, :Pass, :FlightId, :SeatNo);
COMMIT;

START TRANSACTION READ ONLY ISOLATION LEVEL SNAPSHOT;
  SELECT BuyFree(:FlightId, :SeatNo);
COMMIT;

START TRANSACTION READ ONLY ISOLATION LEVEL REPEATABLE READ;
  SELECT ExtendReservation(:UserId, :Pass, :FlightId, :SeatNo);
COMMIT;

START TRANSACTION READ ONLY ISOLATION LEVEL REPEATABLE READ;
  SELECT BuyReserved(:UserId, :Pass, :FlightId, :SeatNo);
COMMIT;

Comments

  1. Транзакции
    • 1.1. FreeSeats
    • 1.2. Reserve
      • Выбран слишком сильный уровень изоляции: REPEATABLEREAD
    • 1.3. ExtendReservation
    • 1.4. BuyFree
      • Выбран слишком сильный уровень изоляции: REPEATABLEREAD
    • 1.5. BuyReserved
    • 1.6. FlightsStatistics
      • Выбран слишком сильный уровень изоляции: REPEATABLEREAD
    • 1.7. FlightsStat
      • Выбран слишком сильный уровень изоляции: REPEATABLEREAD
    • 1.8. CompressSeats
  2. Сценарий
    • 2.1. Запрос списка свободных мест
    • 2.2. Отображение списка свободных мест пользователю
    • 2.3. Бронирование или покупка места, выбранного пользователем
      • Выбран слишком сильный уровень изоляции