Глава 13 / Базы данных

Проект: сервис выдачи книг с проверяемыми гарантиями

Клуб готов пользоваться небольшим сервисом. Он должен показывать книги и свободные экземпляры, выдавать книгу читателю, принимать возврат и строить отчёт об активных займах. Теперь отдельные навыки соединяются: SQL определяет данные, ограничения защищают их, Go управляет запросами, а проверка восстановления показывает, что работа не зависит от одного удачного запуска. Проект выполняется на новой локальной базе, без настоящих персональных данных и без платных сервисов.

Контракт до обработчиков

Опишите четыре операции. Получение каталога возвращает книгу и число свободных экземпляров. Выдача принимает member_id и copy_id, возвращает номер созданного займа либо понятный конфликт. Возврат меняет состояние конкретного займа. Отчёт показывает читателя и число активных выдач, включая ноль. Даты задавайте явно или используйте одну определённую политику серверного времени; в тестах нужны фиксированные значения, чтобы результат не менялся от календаря.

Сначала решите, повторный возврат считается успешным подтверждением уже возвращённой записи или конфликтом. Оба контракта возможны, но случайная зависимость от RowsAffected непонятна клиенту. Отдельно задайте ответ для неизвестного loan id. Для выдачи повтор после потерянного ответа сложнее: клиенту нужен способ узнать исходный результат. Если добавляете ключ идемпотентности, храните запрос и результат транзакционно, проверяйте совпадение содержимого при повторе ключа. Частичный уникальный индекс защищает экземпляр, но сам не возвращает потерянный номер займа.

Аутентификация и полноценный интерфейс здесь не обязательны. Для локальной команды или HTTP-сервера ясно обозначьте учебные границы: переданный member_id не является доказательством личности. В настоящем приложении сервер получает разрешённого читателя из проверенной сессии и ограничивает доступ. Не публикуйте учебный сервер в интернет с открытым доступом.

Запросы на известном наборе

Свободные экземпляры надо считать отдельно от исторических займов, иначе каждый возврат размножит строки. Следующий запрос использует NOT EXISTS:

SELECT b.id, b.title,
       count(c.id) FILTER (WHERE NOT EXISTS (
         SELECT 1 FROM club.loans AS l
         WHERE l.copy_id = c.id AND l.returned_on IS NULL
       )) AS available
FROM club.books AS b
LEFT JOIN club.copies AS c ON c.book_id = b.id
GROUP BY b.id, b.title
ORDER BY b.id;

На seed ожидаются available: книга 10 — 0, книга 11 — 1, книга 12 — 1. Для книги без экземпляров count(c.id) останется нулём. Проверка NOT EXISTS говорит об отсутствии активной выдачи, а не об отсутствии любой истории. Это различие должно сохраниться и в обработчике.

Для первого пробного изменения используйте транзакцию:

BEGIN;
INSERT INTO club.loans
(id, member_id, copy_id, borrowed_on, due_on)
VALUES (2000, 3, 103, DATE '2026-09-18', DATE '2026-10-02')
RETURNING id;
SELECT count(*) FROM club.loans WHERE returned_on IS NULL;
ROLLBACK;

Ожидаются id=2000 и count=3 внутри транзакции, после Rollback активных снова два. Рабочий сервис не должен использовать константу 2000 для всех запросов. В отдельной проектной миграции выберите генерацию ключа, например identity/sequence, и обеспечьте старт после уже существующих seed id. Не используйте SELECT max(id)+1: два конкурирующих запроса вычислят одинаковое значение. Пропуски последовательности после отката допустимы; уникальность не означает непрерывную нумерацию.

Изменение и конкурентный конфликт

Выдачу защищает уникальный индекс one_active_loan_per_copy, а существование читателя и экземпляра — внешние ключи. При SQLSTATE 23505 обработчик определяет нарушенное ограничение, прежде чем превращать ошибку в «экземпляр занят»: повтор первичного ключа означает другую проблему. Ошибка базы не должна полностью уходить пользователю вместе с SQL, внутренними именами и строкой соединения. Лог должен сохранить достаточно контекста для разработчика без секрета подключения.

Проверку свободного экземпляра можно сделать для сообщения, но окончательное решение принимает защищённая запись. Между SELECT и INSERT другой клиент мог завершить выдачу. Два параллельных запроса на экземпляр 103 должны привести к одной активной записи; второй получает определённый конфликт после исхода первой транзакции. Если первая откатывается, второй может успешно продолжить. Измерьте оба сценария в двух соединениях, затем повторите через интерфейс Go.

Возврат выполняйте условным UPDATE по id и returned_on IS NULL, проверяя количество изменённых строк. Не переписывайте borrowed_on и не удаляйте историю. Если нужно в одной операции вернуть старую книгу и выдать следующую, обе записи относятся к одной транзакции через tx. При ошибке второй команды первая должна откатиться. Это наблюдаемое свойство, которое проверяется намеренно неверным copy_id.

Работа при нагрузке и сбое

Один общий sql.DB, параметры и закрытие Rows обязательны независимо от числа пользователей. Задайте ограниченный пул и таймауты. Для отчёта сохраните нулевые строки читателей, для списков добавьте стабильную сортировку и ограничение размера результата. На seed последовательное сканирование нормально; отдельная лаборатория со ста тысячами строк нужна, чтобы оценить индекс при большем объёме. Не выдавайте длительность локального теста за обещание промышленной нагрузки.

Составьте протокол восстановления: снимите собственный dump, восстановите новую пустую базу, запустите проверки и подключите приложение к восстановленной копии. Исходную базу оставьте доступной для сравнения. Запишите, сколько времени заняли восстановление и проверка. Если данные приложения не входят в dump или зависят от отдельной конфигурации, перечислите необходимое отдельно. Проверка должна быть воспроизводима другим человеком с тем же набором лабораторий.

Самостоятельная лаборатория

Отведите 3–5 часов, разбив работу на SQL, Go и проверку. Реализуйте четыре операции в небольшой команде либо HTTP-сервисе. Автоматизируйте проверки каталога 0/1/1, отчёта 2/0/0, успешной выдачи Вере, повторного конфликта, возврата и отката при ошибке. Создавайте отдельную новую базу для тестов, а не очищайте случайный адрес окружения.

Критерии готовности: исходные тесты проходят на seed; конфликт защищён в базе; запросы параметризованы; соединения освобождаются; последовательность шагов воспроизводится; восстановленная копия даёт те же ответы. README проекта содержит команды запуска и ограничения. Для отрицательных сценариев указаны ожидаемые ошибки, а не только скриншоты успешного случая.

Разбор

Две выдачи одной книги допустимы, если экземпляры разные; две активные выдачи одного экземпляра запрещены. Эта формулировка связывает модель, индекс и конкурентный тест. Пустой отчёт отличается от отсутствующего читателя, таймаут записи отличается от доказанного отката, успешный dump отличается от проверенного restore. Если можете показать каждое различие опытом и объяснить его без подглядывания в код, проект выполняет учебную задачу.

Первичные источники

PostgreSQL: ограничения, генерация identity, изоляция, Go: транзакции, резервирование.