Введение
Группа хочет прийти вместе. Бронирование одного места по запросу на двух человек — не успех. Вы сохраните инструкцию SQL для повторного использования, которая возвращает настоящее подтверждение для всей группы либо ничего не меняет, если места недостаточно. Настоящая ошибка уникального ключа проверит, принадлежат ли выделение мест и подтверждение одной транзакции.
Это независимое занятие подготавливает новые мастер-классы и исходные бронирования; файлы из предыдущего занятия не используются. Вам нужны его понятия транзакции, блокировки строк и ROLLBACK, а также базовые PostgreSQL SELECT, INSERT и UPDATE. Именованные параметры клиента, целочисленные преобразования и обобщённое табличное выражение, изменяющее данные, объясняются здесь. Workshop Desk — оболочка; Desk A и Desk B — отдельные соединения psql с одной базой данных на одной машине. Оценка длительности автором включает чтение и обычные ошибки, без исследования времени прохождения новичками.
Создайте /home/labex/project/workshop-desk/reserve.sql. Сохраните предоставленные инструменты, определения таблиц, исходные бронирования и ID, название, вместимость и заметку каждого мастер-класса. Ваш файл содержит одну инструкцию SQL без команд psql, команд оболочки, явных BEGIN/COMMIT, изменений конфигурации или внешних ресурсов. Он принимает четыре описанных параметра и возвращает четыре поля фактического бронирования. Эквивалентный безопасный SQL принимается. Это контролируемое упражнение READ COMMITTED без платежей, автоматических повторов или распределённых гарантий допуска.
Свяжите выделение мест с подтверждением в сохранённом запросе
На этом шаге вы сохраните одну инструкцию, которая не может создать подтверждение, если фактически не выделила места группе.
Начните в Workshop Desk. ./ запускает подготовленную программу из текущего каталога. Программа состояния читает настоящие таблицы, не восполняя отсутствующую работу:
./desk status
Мастер-класс 201 имеет вместимость 6, одно исходное забронированное место и 5 свободных мест. У мастер-класса 202 есть 3 места, у 203 — 4. original-family|201|1|Original должен сохраниться. Первый вывод использует workshop_id|title|capacity|places_left|booked_seats; второй — request_code|workshop_id|seats|desk.
Ваш файл будет принимать переменные psql: workshop_id, seats, request_code и desk. Это параметры клиента, передаваемые отдельно для каждого запроса, а не столбцы базы данных или постоянные настройки сервера. Форма :'name' просит psql вставить эту переменную как безопасно заключённый в кавычки строковый литерал SQL; она сохраняет апостроф внутри кода запроса. Не заменяйте её на :name без кавычек. Синтаксис PostgreSQL ::integer преобразует текстовый ID мастер-класса и положительный размер группы в целые числа.
WITH claimed AS (...) именует обобщённое табличное выражение, или CTE: промежуточное отношение, используемое этой инструкцией. Здесь CTE выполняет условный UPDATE. Его RETURNING workshop_id даёт одну строку, только если вся запрошенная группа помещается. INSERT ... SELECT ... FROM claimed использует эту фактически возвращённую строку. Поэтому пустой результат выделения мест не создаёт INSERT. Последний RETURNING сообщает фактически вставленное бронирование. Не перечитывайте основную таблицу, ожидая увидеть в ней новые значения изменяющего данные CTE в той же инструкции; используйте его отношение RETURNING.
Вычитание использует запрошенный размер, и условие WHERE проверяет тот же размер. Одна инструкция выполняется в неявной транзакции: либо выделение мест и INSERT оба успешны, либо ошибка SQL откатывает оба изменения. В последующей проверке с двумя операторами явная транзакция удержит эту же инструкцию незафиксированной, чтобы вы могли наблюдать её блокировку.
Сохраните полную инструкцию. cat > reserve.sql заменяет этот файл следующим текстом. Маркер here-document SQL в кавычках не даёт оболочке менять содержимое. Вставьте завершающий SQL отдельно на строке. Выполняйте это в оболочке Workshop Desk, а не в одной из вкладок базы данных:
cat > reserve.sql <<'SQL'
WITH claimed AS (
UPDATE workshops
SET places_left = places_left - :'seats'::integer
WHERE workshop_id = :'workshop_id'::integer
AND places_left >= :'seats'::integer
AND :'seats'::integer > 0
RETURNING workshop_id
)
INSERT INTO bookings (request_code, workshop_id, seats, desk)
SELECT :'request_code', workshop_id, :'seats'::integer, :'desk'
FROM claimed
RETURNING request_code, workshop_id, seats, desk;
SQL
Прочитайте сохранённый файл перед использованием. cat показывает его; опечатку следует исправлять в сохранённом источнике, а не скрывать отдельной командой:
cat reserve.sql
desk book выполняет именно этот файл со штатными параметрами psql; он не предоставляет эталонный запрос, не создаёт запасные бронирования и не исправляет остатки. Аргументы — ID мастер-класса, положительный размер группы, уникальный код запроса и метка оператора. Выполните обычный запрос на двух человек:
./desk book 201 2 normal-group A
Фактически возвращённая строка:
normal-group|201|2|A
Изучите источник достоверных данных:
./desk status
Теперь у мастер-класса 201 осталось 3 места и забронировано 3. Исходная семья сохраняется рядом с вашим точным бронированием на двух человек. Одна возвращённая строка не доказывает правильность алгоритма: проверка также читает настоящие строки, сохраняет исходные записи и вызывает ваш неизменённый источник на других изолированных данных, включая настоящие конкурирующие соединения. Эти временные тестовые базы никогда не исправляют живое рабочее место.
Если SQL завершается ошибкой, программа выводит штатную ошибку и завершается ненулевым кодом; она не называет это нехваткой мест. В интерактивной явной транзакции сначала используйте ROLLBACK в той же вкладке оператора. Незафиксированная опечатка ничего не меняет. Если вы случайно зафиксировали новое ошибочное пробное бронирование, удалите только его точный код и пересчитайте только остаток его мастер-класса в одной транзакции, как показано в предыдущем занятии. Например, в Desk A это восстановление, безопасное и при отсутствии ошибки, удаляет только гипотетический typo-trial на 201:
BEGIN;
DELETE FROM bookings WHERE request_code = 'typo-trial' AND workshop_id = 201;
UPDATE workshops
SET places_left = capacity -
(SELECT COALESCE(SUM(seats), 0) FROM bookings WHERE workshop_id = 201)
WHERE workshop_id = 201;
COMMIT;
Никогда не удаляйте original-family или допустимое запрошенное бронирование. Изучите состояние перед решением, какая работа осталась. Не повторяйте уже успешный запрос лишь потому, что последующая команда завершилась ошибкой.
Проверьте ожидающие группы и неудачное подтверждение
На этом шаге вы выполните тот же сохранённый файл в двух независимых штатных соединениях, продолжите работу другого мастер-класса и докажете, что неудачный INSERT не может израсходовать места.
Переключитесь на Desk A. Приглашение означает, что вы внутри psql, а не оболочки. SQL заканчивается точкой с запятой. Команды psql, начинающиеся с обратной косой черты, управляют клиентом и не имеют точки с запятой. \set задаёт переменную клиента в этом одном соединении; она не переносится в другую вкладку. \i читает и выполняет названный файл. Абсолютное имя файла работает независимо от текущего каталога клиента.
Начните транзакцию READ COMMITTED и отправьте группу из двух человек на мастер-класс 202, где изначально три места:
BEGIN ISOLATION LEVEL READ COMMITTED;
\set workshop_id 202
\set seats 2
\set request_code race-A
\set desk A
\i /home/labex/project/workshop-desk/reserve.sql
A видит race-A | 202 | 2 | A и INSERT 0 1: инструкция вставила одну строку бронирования, представляющую два места. Это пока не зафиксировано. * в приглашении обозначает открытую транзакцию.
Переключитесь на Desk B и подготовьте его независимый запрос:
BEGIN ISOLATION LEVEL READ COMMITTED;
\set workshop_id 202
\set seats 2
\set request_code race-B
\set desk B
\i /home/labex/project/workshop-desk/reserve.sql
B ждёт блокировку строки, удерживаемую A, не возвращая приглашения. Оставьте его ждать и переключитесь на Workshop Desk:
./desk waits
У Desk B поле wait_event_type равно Lock, а в blocked_by указан фактический ID серверного процесса A. При READ COMMITTED после фиксации A ожидающий UPDATE повторно проверит достаточность мест по обновлённой строке. Видимый ранее остаток — не бронь.
Оставаясь в Workshop Desk, используйте тот же сохранённый файл, чтобы забронировать другой мастер-класс:
./desk book 203 2 other-workshop C
Он возвращает other-workshop|203|2|C и завершается, пока A всё ещё удерживает 202. Не вводите одну глобальную блокировку, которая без необходимости останавливает не связанный с этим мастер-класс.
Переключитесь на Desk A:
COMMIT;
Переключитесь на Desk B. Теперь его ожидающий файл завершается без возвращённых строк и с INSERT 0 0. Осталось лишь одно место, поэтому группа из двух человек не помещается. Не разделяйте группу и не вставляйте отдельное подтверждение. Завершите транзакцию B:
ROLLBACK;
Переключитесь на Workshop Desk:
./desk status
У мастер-класса 202 осталось одно место и есть ровно race-A|202|2|A; race-B отсутствует. У мастер-класса 203 осталось два места и есть его бронирование на двух человек. Проверяйте связь, а не только успешный код завершения.
Теперь вызовите ошибку INSERT, пока на 202 всё ещё достаточно места, чтобы выделить одно. Первичный ключ требует уникальности каждого кода запроса во всей таблице бронирований, а не только внутри мастер-класса. Код original-family уже принадлежит 201. Повторное использование здесь должно дать SQLSTATE 23505 — нарушение уникальности ключа. Это ожидаемый отказ, а не результат отсутствия мест:
./desk book 202 1 original-family A
Выполнение файла сообщает штатную ошибку и завершается со статусом 3. Зафиксируйте этот статус немедленно, прежде чем другая команда оболочки заменит $? — статус завершения предыдущей команды:
printf 'Previous native exit: %s\n' "$?"
Ожидается Previous native exit: 3. Ненулевой статус отличает ошибку от успешного результата без строк. Неявная транзакция инструкции должна отменить предшествующее выделение мест. Прочитайте реестр снова:
./desk status
У мастер-класса 202 по-прежнему одно место, а original-family не изменилось на 201. Приём вроде ON CONFLICT DO NOTHING после выделения места может скрыть ошибку INSERT, одновременно зафиксировав уменьшение остатка. Это не атомарный отказ: счётчик перестал бы соответствовать фактическим бронированиям. Проверка испытывает этот случай при достаточном количестве мест, поэтому пустое выделение не может случайно обеспечить прохождение проверки ошибки.
Используйте новый код запроса, чтобы допустимым образом забронировать последнее место:
./desk book 202 1 after-error A
Ожидается after-error|202|1|A. Теперь запросите ещё одну группу из двух человек, когда мест уже нет:
./desk book 202 2 too-late B
Программа печатает No booking returned. Inspect the real ledger before drawing a conclusion. Этот вызов завершается с нулевым кодом, потому что SQL был допустимым и не вернул бронирования; он не должен оставлять строку too-late и менять остаток.
./desk status
Итоговые новые бронирования: normal-group (201, 2, A), race-A (202, 2, A), other-workshop (203, 2, C) и after-error (202, 1, A), плюс нетронутая исходная семья. Нет race-B или too-late. Остатки мест соответственно 3, 0 и 2.
Если вы отмените ожидающий файл через Ctrl+C, SQLSTATE 57014 переведёт его явную транзакцию в состояние ошибки. Как отработано ранее, используйте ROLLBACK в той же вкладке базы данных перед повторной попыткой. Сначала изучите зафиксированный реестр: другой оператор мог уже успешно завершить работу. Интерактивная ошибка SQL и обычный результат из нуля строк требуют разных решений.
Резюме
Сохранённый SQL превращает настоящее условное выделение мест ровно в одно подтверждение всей группы. Два штатных соединения продемонстрировали ожидание и повторную проверку, пока другой мастер-класс продолжал работу. Повторный код запроса вызвал настоящую ошибку INSERT и откат выделения мест; последующий допустимый запрос использовал всё ещё свободное место. Файл для повторного использования и живой реестр сохраняют исходные данные и отличают нехватку мест от ошибки SQL.
