PostgreSQL exclusion constraints: Chặn lịch đặt chỗ chồng lấn

Hai request đặt cùng một phòng có thể cùng chạy câu “kiểm tra còn trống”, cùng thấy không có lịch trùng và cùng INSERT thành công. Transaction hay application lock viết thiếu một code path vẫn không bảo vệ được invariant. PostgreSQL giải quyết trực tiếp bằng range types và exclusion constraint: với cùng tài nguyên, không cho hai khoảng thời gian overlap tồn tại đồng thời.

Bài viết xây dựng schema booking thực tế bằng tstzrange, toán tử overlap &&, GiST và btree_gist; đồng thời xử lý biên thời gian, trạng thái hủy, concurrency, truy vấn availability và migration. Nội dung dựa trên tài liệu PostgreSQL chính thức về range types, constraints và btree_gist.

1. Vì sao SELECT rồi INSERT vẫn double-book?

BEGIN;

SELECT 1
FROM bookings
WHERE room_id = 42
  AND starts_at < :new_end
  AND ends_at > :new_start;

-- Nếu không có row, application INSERT.
INSERT INTO bookings (...);
COMMIT;

Ở isolation mặc định, hai transaction có thể đọc cùng snapshot trước khi transaction nào INSERT. Cả hai đều thấy “trống”. Khóa row cũng không giúp khi row xung đột chưa tồn tại; advisory lock chỉ an toàn nếu mọi code path tuân thủ cùng protocol.

Invariant “không overlap trong cùng room” nên được database thực thi. Application vẫn có thể kiểm tra trước để trả UX tốt, nhưng constraint là hàng rào cuối cùng.

2. Range type mô hình hóa khoảng thời gian

PostgreSQL có tsrange cho timestamp không timezone và tstzrange cho timestamp with time zone. Với lịch diễn ra tại các instant tuyệt đối, tstzrange thường an toàn hơn; giá trị được so sánh theo instant sau khi input timezone được chuyển đổi.

SELECT tstzrange(
    '2026-10-03 09:00+07',
    '2026-10-03 10:00+07',
    '[)'
);

[) nghĩa bắt đầu inclusive, kết thúc exclusive. Booking 09:00–10:00 và 10:00–11:00 liền nhau nhưng không overlap. Đây là quy ước rất phù hợp cho scheduling vì không cần trừ một microsecond giả tạo.

3. Schema production cơ bản

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE bookings (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_id bigint NOT NULL,
    starts_at timestamptz NOT NULL,
    ends_at timestamptz NOT NULL,
    status text NOT NULL DEFAULT 'confirmed',
    slot tstzrange GENERATED ALWAYS AS (
        tstzrange(starts_at, ends_at, '[)')
    ) STORED,
    created_at timestamptz NOT NULL DEFAULT now(),

    CONSTRAINT bookings_time_order
        CHECK (starts_at < ends_at),

    CONSTRAINT bookings_no_confirmed_overlap
        EXCLUDE USING gist (
            room_id WITH =,
            slot WITH &&
        )
        WHERE (status = 'confirmed')
);

btree_gist cung cấp GiST operator class có hành vi equality cho scalar như bigint; range type đã có GiST support cho overlap. Constraint nói rằng không được có một cặp row mà cả room_id = và slot && cùng đúng.

Thêm exclusion constraint tự tạo index phù hợp để thực thi. Không tạo thêm một GiST index giống hệt nếu chưa chứng minh cần thiết.

4. NOT NULL và CHECK không phải trang trí

Exclusion constraint cho phép một cặp row nếu ít nhất một phép so sánh trả false hoặc null. Vì vậy room_id, hai đầu thời gian và range cần tránh null nếu nghiệp vụ yêu cầu bảo vệ tuyệt đối.

CHECK (starts_at < ends_at) chặn khoảng rỗng và thời gian đảo ngược. Range rỗng không overlap bất kỳ range nào, nên nếu bỏ CHECK, nhiều “booking” rỗng có thể lọt qua mà không vi phạm constraint.

5. Partial constraint cho trạng thái booking

Predicate WHERE (status = 'confirmed') cho phép row cancelled hoặc draft giữ lịch sử mà không chiếm slot. Khi cập nhật một draft thành confirmed, chính UPDATE đó được kiểm tra và có thể thất bại nếu slot đã bị đặt.

Hãy giới hạn domain trạng thái:

ALTER TABLE bookings
ADD CONSTRAINT bookings_status_valid
CHECK (status IN ('draft', 'confirmed', 'cancelled'));

Nếu “pending payment” cũng phải giữ chỗ, đưa nó vào predicate hoặc mô hình hóa blocks_inventory boolean có contract rõ ràng. Predicate sai một trạng thái là lỗ hổng nghiệp vụ, không phải lỗi index.

6. Concurrent INSERT được giải quyết thế nào?

Khi hai transaction chèn overlap cho cùng room, PostgreSQL phối hợp qua cơ chế constraint/index. Tùy timing, một transaction có thể phải chờ transaction còn lại; khi conflict được xác nhận, một phía nhận lỗi exclusion_violation với SQLSTATE 23P01.

INSERT INTO bookings (room_id, starts_at, ends_at)
VALUES (42, '2026-10-03 09:00+07', '2026-10-03 10:00+07');

Application phải bắt đúng SQLSTATE và trả lỗi domain như “khung giờ vừa được người khác đặt”. Không biến mọi database error thành HTTP 409; connection failure, disk error hay syntax error cần luồng khác.

7. Kiểm tra availability vẫn hữu ích cho UX

SELECT NOT EXISTS (
    SELECT 1
    FROM bookings
    WHERE room_id = $1
      AND status = 'confirmed'
      AND slot && tstzrange($2, $3, '[)')
) AS available;

Truy vấn này có thể dùng GiST index do constraint tạo và giúp giao diện tô lịch trống. Nhưng kết quả chỉ đúng tại thời điểm query; INSERT/UPDATE vẫn phải sẵn sàng nhận conflict do request khác chen vào.

8. Biên thời gian và timezone

• Lưu instant bằng timestamptz; lưu thêm timezone IANA của địa điểm nếu cần tái tạo giờ địa phương.
• API nên nhận timestamp có offset rõ ràng hoặc local time + timezone ID, rồi xử lý DST ambiguity có chủ đích.
• Dùng cùng quy ước [) cho create, query, report và cache key.
• Không dùng BETWEEN tùy tiện vì nó inclusive cả hai đầu và dễ làm slot liền nhau thành conflict giả.
• Không cộng/trừ “1 millisecond” để xử lý boundary; range operator đã mô hình hóa đúng.

9. Update, kéo dài và đổi phòng

Constraint kiểm tra cả INSERT lẫn UPDATE. Các thao tác sau đều có thể nhận 23P01:

-- Kéo dài thời gian
UPDATE bookings
SET ends_at = ends_at + interval '30 minutes'
WHERE id = $1;

-- Chuyển phòng
UPDATE bookings
SET room_id = $2
WHERE id = $1;

-- Khôi phục booking đã hủy
UPDATE bookings
SET status = 'confirmed'
WHERE id = $1;

Nếu cần đổi nhiều booking nguyên tử, sắp xếp thao tác nhất quán để giảm deadlock và cân nhắc constraint DEFERRABLE theo workflow. Đừng bật deferred mặc định nếu không có test chứng minh semantics cuối transaction là điều nghiệp vụ mong muốn.

10. Truy vấn khoảng trống

Constraint chặn overlap nhưng không tự sinh danh sách slot trống. Một cách thực tế là tạo candidate slots bằng generate_series(), rồi loại slot overlap:

WITH candidates AS (
    SELECT tstzrange(t, t + interval '30 minutes', '[)') AS slot
    FROM generate_series(
        $1::timestamptz,
        $2::timestamptz - interval '30 minutes',
        interval '30 minutes'
    ) AS t
)
SELECT c.slot
FROM candidates c
WHERE NOT EXISTS (
    SELECT 1
    FROM bookings b
    WHERE b.room_id = $3
      AND b.status = 'confirmed'
      AND b.slot && c.slot
)
ORDER BY lower(c.slot);

Giới hạn cửa sổ generate_series, timezone và số candidate. Với lịch phức tạp, calendar availability còn phải trừ giờ đóng cửa, maintenance và exception.

11. Migration từ hai cột thời gian

1. Audit dữ liệu null, reversed hoặc empty.
2. Tìm overlap hiện có bằng self-join với điều kiện ID để không đếm đôi.
3. Quyết định merge, cancel hay sửa dữ liệu xung đột.
4. Cài btree_gist theo change process và quyền production.
5. Thêm generated range/CHECK, backfill nếu kiến trúc dùng cột range thường.
6. Thêm constraint trong maintenance plan; dự toán scan, lock, I/O và replica lag.
7. Deploy application bắt 23P01 trước khi mở concurrent traffic hoàn toàn.

SELECT a.id, b.id, a.room_id, a.slot, b.slot
FROM bookings a
JOIN bookings b
  ON a.room_id = b.room_id
 AND a.id < b.id
 AND a.status = 'confirmed'
 AND b.status = 'confirmed'
 AND a.slot && b.slot;

12. Những lỗi thiết kế thường gặp

• Chỉ SELECT-before-INSERT và tin rằng transaction đã đủ.
• Dùng inclusive cả hai đầu khiến booking liền nhau bị coi overlap.
• Cho phép null hoặc empty range rồi nghĩ constraint vẫn chặn mọi trường hợp.
• Quên trạng thái mới trong partial predicate.
• Dùng timestamp không timezone cho lịch xuyên timezone mà không có contract.
• Bắt mọi integrity error thành “slot đã đặt”.
• Tạo index trùng với index constraint.
• Thêm constraint khi production đã có overlap mà chưa audit.

Checklist production

• Invariant được biểu diễn bằng EXCLUDE USING gist, không chỉ application check.
• Dùng tstzrange(..., '[)') nhất quán.
• Các cột tài nguyên và thời gian là NOT NULL; có CHECK start < end.
• Predicate trạng thái bao phủ mọi trạng thái chiếm inventory.
• Application bắt riêng SQLSTATE 23P01.
• Availability query chỉ phục vụ UX; write vẫn chịu constraint.
• Đã test concurrent insert, update, cancel/restore và slot liền nhau.
• Migration audit overlap trước khi tạo constraint.
• Theo dõi conflict rate, latency chờ và deadlock.

Kết luận

Range type và exclusion constraint biến quy tắc “một tài nguyên không được có hai lịch chồng nhau” thành invariant của PostgreSQL, nơi mọi code path và mọi request đồng thời đều phải tuân thủ. Schema tốt dùng biên [), NOT NULL, CHECK, predicate trạng thái chính xác và xử lý 23P01 như conflict nghiệp vụ. Khi database giữ hàng rào cuối, application có thể tập trung vào UX mà không đánh cược tính đúng đắn vào một câu SELECT dễ race.