‫جلوگیری از تداخل رزرو (Double Booking) در سطح پایگاه داده

چکیده: یکی از چالش‌های کلاسیک و در عین حال بحرانی در طراحی سیستم‌های رزرو (هتل، بلیط، نوبت‌دهی و مدیریت منابع)، پدیده تداخل رزرو (Double Booking) ناشی از شرایط رقابتی بررسی و سپس اقدام (Check-Then-Act Race Condition) است. در حالی که توسعه‌دهندگان معمولاً تلاش می‌کنند این مسئله را در لایه اپلیکیشن (Application Layer) با ابزارهایی مانند Semaphoreها یا قفل‌های توزیع‌شده (Distributed Locks) حل کنند، ذات توزیع‌پذیر و چند‌مسیری بودن سامانه‌ها این راهکارها را شکننده و آسیب‌پذیر می‌سازد. قاعده طلایی معماری داده حکم می‌کند که قوانین صحت داده (Data Integrity Constraints) باید مستقیماً توسط پایگاه داده تضمین شوند. پایگاه داده PostgreSQL با تکیه بر Exclusion Constraints و نمایه‌های GiST راهکاری ظریف، تک‌خطی و توکار ارائه می‌دهد. با این حال، پایگاه داده Microsoft SQL Server فاقد چنین محدودیتی به‌صورت ذاتی است. در این مقاله، ضمن کالبدشکافی ریاضی بازه‌های زمانی نیمه‌باز، نحوه پیاده‌سازی این الگو در PostgreSQL بررسی شده و سپس سه معماری و الگوی پیاده‌سازی معادل برای Microsoft SQL Server و Entity Framework Core (EF Core) با تحلیل مزایا، معایب و کدهای تست همزمانی ارائه می‌گردد.

if (await repo.IsAvailableAsync(roomId, checkIn, checkOut))
{
    await repo.AddAsync(new Booking(roomId, checkIn, checkOut));
}

• رزرو الف: ۱۰ تا ۱۵ ژوئیه
• رزرو ب: ۱۵ تا ۱۸ ژوئیه

نکته حساس: یکی از رایج‌ترین باگ‌ها در سیستم‌های رزرواسیون، استفاده اشتباه از عملگر => به جای > است که باعث مسدود شدن روزهای پشت‌سر‌هم (Back-to-Back) می‌شود.

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE bookings (
    id          BIGSERIAL PRIMARY KEY,
    room_id     INT NOT NULL,
    check_in    DATE NOT NULL,
    check_out   DATE NOT NULL,
    status      TEXT NOT NULL DEFAULT 'pending',
    expires_at  TIMESTAMPTZ,
    CONSTRAINT valid_stay CHECK (check_in < check_out),
    CONSTRAINT no_overlapping_bookings
        EXCLUDE USING gist (
            room_id WITH =,
            daterange(check_in, check_out, '[)') WITH &&
        )
        WHERE (status IN ('pending', 'confirmed'))
);

CREATE TABLE Rooms (
    Id INT PRIMARY KEY
);

CREATE TABLE Bookings (
    Id BIGINT IDENTITY(1,1) PRIMARY KEY,
    RoomId INT NOT NULL FOREIGN KEY REFERENCES Rooms(Id),
    CheckIn DATE NOT NULL,
    CheckOut DATE NOT NULL,
    Status NVARCHAR(20) NOT NULL DEFAULT 'pending',
    ExpiresAt DATETIMEOFFSET NULL,
    CONSTRAINT CK_Bookings_Dates CHECK (CheckIn < CheckOut)
);

CREATE TABLE BookingNights (
    BookingId BIGINT NOT NULL FOREIGN KEY REFERENCES Bookings(Id) ON DELETE CASCADE,
    RoomId INT NOT NULL,
    [Night] DATE NOT NULL,
    IsActive BIT NOT NULL DEFAULT 1,
    CONSTRAINT PK_BookingNights PRIMARY KEY (BookingId, [Night])
);

-- این نمایه مانع از تداخل می‌شود
CREATE UNIQUE NONCLUSTERED INDEX UX_RoomNights_UniqueActiveNight
ON BookingNights (RoomId, [Night])
WHERE IsActive = 1;

public class Booking
{
    public long Id { get; set; }
    public int RoomId { get; set; }
    public DateOnly CheckIn { get; set; }
    public DateOnly CheckOut { get; set; }
    public string Status { get; set; } = "pending";
    public DateTimeOffset? ExpiresAt { get; set; }
    public List<BookingNight> Nights { get; set; } = new();
}

public class BookingNight
{
    public long BookingId { get; set; }
    public Booking Booking { get; set; } = null!;
    public int RoomId { get; set; }
    public DateOnly Night { get; set; }
    public bool IsActive { get; set; }
}

// در متد OnModelCreating
modelBuilder.Entity<BookingNight>(entity =>
{
    entity.HasKey(bn => new { bn.BookingId, bn.Night });

    // قید یکتایی فیلترشده (Filtered Index)
    entity.HasIndex(bn => new { bn.RoomId, bn.Night })
          .IsUnique()
          .HasFilter("[IsActive] = 1");
});

• مزیت: سرعت فوق‌العاده بالا، خواندن بسیار ساده برای تقویم هتل، تکیه بر نمایه‌های B-Tree استاندارد، و استقلال ۱۰۰ درصدی از تریگرها.
• عیب: افزایش تعداد سطرها در جدول اقامت‌های طولانی (البته برای ذخیره‌سازی داده‌های تاریخ، بسیار سبک است).

هشدار معماری بسیار مهم: تریگرهای معمولی در SQL Server به طور پیش‌فرض تحت سطح انزوای READ COMMITTED اجرا می‌شوند و در برابر شرایط رقابتی (Race Condition) به شدت آسیب‌پذیرند. برای مسدود کردن رخداد رقابتی در تریگر، استعلام تداخل در تریگر باید از WITH (HOLDLOCK) (معادل Range-Lock / Serializable) یا جدول قفل منبع (Mutex Table) استفاده کند.

CREATE OR ALTER TRIGGER TR_Bookings_PreventOverlap
ON Bookings
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- در صورتی که سطر درج شده فعال باشد
    IF EXISTS (
        SELECT 1
        FROM inserted i
        -- استفاده از HOLDLOCK برای تضمین ممانعت از race condition
        JOIN Bookings b WITH (HOLDLOCK, UPDLOCK) ON i.RoomId = b.RoomId
        WHERE i.Id <> b.Id
          AND i.Status IN ('pending', 'confirmed')
          AND b.Status IN ('pending', 'confirmed')
          AND i.CheckIn < b.CheckOut 
          AND b.CheckIn < i.CheckOut
    )
    BEGIN
        RAISERROR('تداخل تاریخ برای این اتاق وجود دارد (Double Booking Detected).', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END
END;

public async Task<IResult> CreateBooking(CreateBookingRequest req, AppDbContext db, CancellationToken ct)
{
    var booking = new Booking
    {
        RoomId = req.RoomId,
        CheckIn = req.CheckIn,
        CheckOut = req.CheckOut,
        Status = "pending",
        ExpiresAt = DateTimeOffset.UtcNow.AddMinutes(10)
    };

    // تولید شب‌ها برای الگوی Room-Nights
    for (var date = req.CheckIn; date < req.CheckOut; date = date.AddDays(1))
    {
        booking.Nights.Add(new BookingNight
        {
            RoomId = req.RoomId,
            Night = date,
            IsActive = true
        });
    }

    db.Bookings.Add(booking);

    try
    {
        await db.SaveChangesAsync(ct);
        return Results.Created($"/api/bookings/{booking.Id}", booking);
    }
    catch (DbUpdateException ex) when (ex.InnerException is SqlException sqlEx && 
                                      (sqlEx.Number == 2601 || sqlEx.Number == 2627))
    {
        // تشخیص نقض نمایه یکتا در الگوی جدول شب‌ها
        return Results.Problem(
            statusCode: StatusCodes.Status409Conflict,
            title: "این اتاق در بازه زمانی انتخابی قبلاً رزرو شده است.");
    }
    catch (DbUpdateException ex) when (ex.InnerException is SqlException sqlEx && sqlEx.Number == 50000)
    {
        // خطای سفارشی RAISERROR ناشی از تریگر
        return Results.Problem(
            statusCode: StatusCodes.Status409Conflict,
            title: sqlEx.Message);
    }
}

• پاک‌سازی در زمان نوشتن (Write-Time Cleanup): درون همان تراکنشِ ثبت رزرو جدید، ابتدا رکوردهای Pending منقضی شده مربوط به همان اتاق ابطال شوند.
• سرویس پس‌زمینه (.NET BackgroundService): دوره‌ای (مثلاً هر یک دقیقه) سطرهای منقضی‌شده عمومی را بروزرسانی کند.

-- اجرای درون تراکنش پیش از درج رزرو جدید
BEGIN TRANSACTION;

    -- آزاد کردن رزروهای منقضی شده همین اتاق
    UPDATE Bookings
    SET Status = 'expired'
    WHERE RoomId = @RoomId
      AND Status = 'pending'
      AND ExpiresAt < SYSUTCDATETIME();

    -- در صورت استفاده از الگوی Room-Nights
    UPDATE bn
    SET bn.IsActive = 0
    FROM BookingNights bn
    INNER JOIN Bookings b ON bn.BookingId = b.Id
    WHERE b.RoomId = @RoomId AND b.Status = 'expired';

    -- اکنون عملیات درج رزرو با خیال راحت انجام می‌شود...
COMMIT TRANSACTION;

[Fact]
public async Task ConcurrentBookings_ForSameRoomAndDates_OnlyOneSucceeds()
{
    // Arrange: ۱۰ درخواست رزرو همزمان برای یک اتاق و یک بازه
    var request = new CreateBookingRequest(
        RoomId: 101, 
        CheckIn: new DateOnly(2026, 7, 10), 
        CheckOut: new DateOnly(2026, 7, 15)
    );

    const int concurrentRequests = 10;

    // Act: شبیه‌سازی ارسال همزمان درخواست‌ها
    var tasks = Enumerable.Range(0, concurrentRequests)
        .Select(_ => _client.PostAsJsonAsync("/api/bookings", request));

    var responses = await Task.WhenAll(tasks);

    // Assert: دقیقاً یک درخواست موفق و بقیه با خطای 409 برگردند
    Assert.Single(responses, r => r.StatusCode == HttpStatusCode.Created);
    Assert.Equal(concurrentRequests - 1, responses.Count(r => r.StatusCode == HttpStatusCode.Conflict));
}

تذکر مهم: هرگز این تست را با InMemory Database یا SQLite in-memory در EF Core اجرا نکنید؛ زیرا این فراهم‌کننده‌ها رفتار موتورهای قفل‌گذاری و نمایه‌سازی SQL Server یا PostgreSQL را شبیه‌سازی نمی‌کنند و نتایج به شدت گمراه‌کننده خواهند بود.

• در PostgreSQL: استفاده از Exclusion Constraint مبتنی بر GiST به همراه بازه‌های نیمه‌باز [daterange) بالاترین کارایی و کمترین کدنویسی را به همراه دارد.
• در SQL Server: با توجه به عدم وجود قیدهای بازه‌ای، «الگوی جدول شب‌ها (Room-Nights) به همراه Unique Filtered Index» بهترین، تمیزترین و پرفورمنس‌ترین الگو برای پروژه‌های بزرگ در اکوسیستم .NET و EF Core است. در صورتی که مدل داده نباید تغییر کند، تریگرهای مجهز به قفل‌های بازه‌ای یکپارچه (HOLDLOCK) تنها سپر مطمئن خواهند بود.
• در لایه #C، همیشه باید کدهای خطای خطوط پایگاه داده صید شده و در قالب پاسخ استاندارد HTTP 409 Conflict به کاربر تحویل داده شوند.