Здравствуйте, форумчане.
Замучался уже перекапывать интернет, решился, наконец, задать вопрос знатокам.
Нужно спроекторовать БД для системы бронирования номеров в гостинице. Задача вроде бы несложная, но есть одно НО: бронируемые единицы имеют иерархичесткую структуру. К примеру, есть двухэтажный домик, на каждом этаже по две комнаты. Забронировать можно как домик целиком, так и в отдельности каждый этаж и каждую комнату.
Имееющиеся таблицы:

vacancies (
vacancy_id,
parent_vacancy_id,
vacancy_name
);

booking_orders (
booking_order_id,
date_start,
date_end
);

bookings (
booking_order_id,
vacancy_id
);

Очевидно, что если забронировать комнату на втором этаже в период с 5 мая по 10 мая, то весь второй этаж, а также домик целиком забронировать на этот период уже не получится.
Также очевидно и обратное, что если на этот период забронировать домик целиком - то нельзя будет забронировать ни одну из комнат и ни один из этажей этого домика.
Вопрос: каким образом реализовать эти запреты в БД?
Я использую PostgreSQL, и без иерархической структуры это можно сделать простым применением CHECK. Но так как в моем случае проверяться должны ещё потомки и предки - я зашел в тупик.
Точнее, я загнал в тупик БД, создав такой триггер, который использует блокироровки и при нескольких конкурентных запросах создает deadlock :)) (хотя, в документации к PostgreSQL написано, что СУБД умеет справляться с дедлоками, убивая одину из конкурирующих транзакций)
Если блокировки не использовать - то возникают ситуации, когда бронирования накладываются друг на друга. Я знаю, почему это происходит, но не могу понять, как от этого избавиться.

Мой триггер имеет вид after insert on bookings for each row execute procedure ...

Если две конкурентных транзакции одновременно пытаются записать в таблицу bookings противоречащие данные, то для обеих вызывается этот триггер, но так как обе транзакции еще не завершены, то при проверке противоречий при чтении из таблицы bookings одна транзакция не видит результаты работы другой, и поэтому обе завершаются успешно. В результате мы имеем 2 записи, которые "проскочили" проверку валидности, но противоречат друг другу, так как их периоды бронирования пересекаются.

Кстати, для уточнения: если транзакции выполняются последовательно, то триггер работает, и попытки создать запись, противоречащюю уже имеющейся, вызывают ошибку. То есть, дело именно в конкурентных одновременных транзакциях. Я создал тестовое мультипоточное приложение (100 потоков, использующие каждый отдельное соединение с БД, одновременно создают случайные записи в таблице бронирования в пределах установленных ограничений), которое и выявило проблему.

Количество иерархий неограничено. Система должна быть как можно более универсальной. Может случиться и такое, что заказчик захочет каждую койку в комнате по-отдельности бронировать, это должно быть предусмотрено. Я думаю, единственным условием сделать то, чтобы заказчик четко вбил иерархическую структуру, и больше не менял её во время использования. При желании её как-то расширить ему придется создавать новую структуру. То есть, был домик с двумя комнатами, если он хочет сдавать там койки - то пусть создаст в БД новый домик с двумя комнатами и койками, а не добавляет койки в уже готовую структуру. Хотя, если можно найти простое и элегантное решение, чтобы гибко изменять структуру во время эксплуатации без побочных эффектов - было бы совсем хорошо. И волки сыты, и овцы целы :) Но мне кажется, это будет очень сложно, придется отслеживать очень много зависимостей.
Хотя, вот родилась идея использовать массивы для хранения ID всех зависимых единиц. Может, что-то из этого получится. И гибкое, и надёжное :) Пятый час утра - а я тут сижу реализовываю, чтоб не забыть как проснусь ))

Ещё сразу уточню, что в БД могут быть иерархии с разными степенями вложения. То есть, может быть просто однокомнатный домик (то есть, просто либо домик, либо комната, без зависимостей), может быть двухкомнатный домик, может быть двухэтажный домик с двумя комнатами на первом этаже, тремя на втором, и еще чердаком. То есть, все зависит от фантазии заказчика :) И всё это одновременно должно находится в БД, не конфликтовать друг с другом и правильным образом отслеживаться при бронировании.

SERG1257Единица бронирования день?
Если так то можно создать таблицу


и избавится от проверочного триггера как класса (приложение вставит по записи на каждую комнату каждого дня при бронировании на верхнем уровне) а ошибку будет генерить нарушение первичного ключа

Единица бронирования - произвольный период, измеряемый в днях. То есть, с 5 по 15 мая. Или с 3 июня по 6 сентября.

SERG1257,

За ссылочку большое спасибо, нашел там ответ на один нужный вопрос.
А насчет архивации - это да, уже думал об этом. Кроме архивации там ещё много интересных фишек можно сделать, чтоб снизить нагрузку на сервер. Но оптимизацией производительности я буду заниматься позже, сейчас бы заставить эту махину работать :)

Ещё одна вещь, которую я не понимаю: у меня есть триггер вида:

create constraint trigger trig_checkBookingPeriod after insert on bookings FROM booking_orders for each row execute procedure checkBookingPeriod();

если в него добавить строку
LOCK TABLE bookings IN SHARE MODE;
То возникает deadlock при попытке вставить строку в таблицу несколькими параллельными потоками. Если поток один - то все работает нормально. Откуда берется дэдлок? В самой процедуре триггера никаких записей ни в какие таблицы не делается, только чтение для проверки консистенции. Есть догадка, что он может возникать в случае, если проверка в треггере не проходит и выстреливает исключение (exception), что приводит к откату транзакции. Тогда системой из таблицы удаляется запись, которая не прошла проверку валидности, для этого делается LOCK IN EXCLUSIVE MODE, а это уже может вызвать дэдлок. В общем, понятно, что ничего не понятно =/

softwarer,
Каскадные блокировки не рулят. Если у родителя есть два потомка, и один потомок каскадом заблокирует родителя, то второй останется незаблокированным, но заблокировать его уже будет нельзя, так как родитель заблокирован другим потомком. То есть, в доме 2 комнаты, одну бронирут - вторая остается свободной, но первая каскадом бронирует родителя, в результате свободную вторую забронировать уже нельзя.

ЫUniquad,

Единицей бронируемого места всегда должна быть наименьшая из возможных, т.е. койка. Тогда домик будет суммой коек, расположенных в нем. В этом случае вы сможете обслужить клиента, которому потребуются, напр., просто 10 коек, и неважно в скольких домиках/комнатах они окажутся.

Да, это было бы проще всего. А как раскидывать потом клиентов, чтобы они вдруг не оказались в номере с какими-нибудь левыми чуваками? Или если приезжает большая группа людей, и они хотели бы все остановиться на одном этаже?

Фух, ребята, вроде бы наконец-таки нарыл решение. Всем спасибо за информацию, было очень поучительно для меня пообщаться со знатоками БД. Я в этом вопросе не особо опытен :)
Оказалось, что триггер проверки на целостность нужно было явно объявить как constraint trigger deferrable initially deferred. Тогда проверка производится после окончания транзакции, и накладок не возникает. И не понадобились никакие принудительные блокировки.
Пока гоняю тесты, и, скрестив пальцы, жду результатов) Но по первым впечатлениям всё работает (тьфу-тьфу-тьфу) :)

Если кому интересно, могу сказать, что периоды я реализовал в виде геометрического типа BOX((start_day, start_day), (end_day, end_day)), и проверяю их на пересечение, пользуясь оператором && (start_day/end_day высчитываю, отнимая '2000-01-01' от даты начала бронирования).
Кроме того, в отдельном поле типа Array я храню айди всех предков и потомков узла, чтобы каждый раз при проверке целостности не обходить дерево вызовом дополнительных SELECT. Массив с айдишниками предков заполняется автоматически всё теми же триггерами, всё это очень красиво и элегантно выглядит (за это я и люблю программирование :)) ).

Я знаю, что поле типа box можно индексировать при помощи gist, и это очень большое ускорение при большом количестве данных, а вот с полем Array пока не разобрался, не знаю, помогут ли здесь индексы для оптимизации.

И еще, может, кому пригодится: в интернете нашел совет человека, сталкивавшегося с такой же проблемой, но только без иерархической структуры. Он для проверки пересечений обошелся единственным полем BOX, в котором одним из измерений является ID комнаты. Очень элегантное решение, и его же можно использовать в версии postgresql 9.0 с их новой фишкой exclude constraint.
Выглядит это как BOX((start_day, room_id), (end_day, room_id+0.5)). 0.5 добавляется, чтобы прямоугольник не оказался нулевой высоты(или ширины).
Всё гениальное просто :))

ChAЭто ППЦ, а не решение, расстрел воробьёв из пушек. Хорошо, если это всего лишь учебное задание, за коммерческое - увольнение без выходного пособия без права работы за компьютером, как минимум, 5 лет.
В чем пипец? Почему воробьев из пушек? Как тогда сделать лучше? Критика неконструктивна, предложи решение лучше :)
Про монопольные и разделяемые блокировки я думал, но проблема в том, что у нас есть периоды блокировки, которые начинаются и заканчиваются в произвольное время. Единственное решение, которое я вижу для этой схемы - это создавать запись для каждого отдельного дня. Но тогда количество записей в таблице будет огромным.

ChA,

Смысл таблицы bookings как раз в том, что один заказ может включать несколько единиц. Период у всех один и тот же. Например, 4 человека хотят приехать в отель с 1 по 10 мая и остановиться в 2-х разных номерах.
Но даты я продублировал в bookings (опять же, триггером перед вставкой), чтобы при проверке не обращаться к таблице booking_orders. Для оптимизации.
Сейчас моя таблица booking_orders выглядит так:




Ы,

Да, видимо, так будет гораздо проще следить за целостностью данных. Я отказался от этой идее в самом начале, потому что хотелось, чтобы вся работа проводилась "за сценой", чтобы вся процедура бронирования со стороны клиента свелась к простому INSERT INTO booking_orders / INSERT INTO bookings. Интересно, а какие плюсы у такого подхода по сравнению с тем, что я уже реализовал? Ну, кроме того, что можно избавиться от триггера на проверку противоречий и использовать ecxlude constraint.