跳到正文
DEV Community· Wenlo·· 2 天前AI 評分43

如何拒絕重疊預約,同時避免留下未完成的交易

Reject an overlapping reservation without leaving half a transaction

AI 導讀

這個 Python SQLite 範例示範如何在批次預約遇到重疊時,回滾整個交易,避免保留批次中先前已成功的預約。範例以半開區間判斷重疊,並用資料庫 CHECK 約束拒絕結束時間早於開始時間的預約。

正文

Wenlo

Wenlo

Posted on Nov 20 AI-assisted

A batch contains two fictional studio reservations. The first is valid; the second overlaps it. Should the valid first row remain after the batch fails? For an all-or-nothing operation, the answer is no. This in-memory SQLite example makes the rollback visible.

Run the example

I ran this example in PythonIDE on an English iPhone simulator. All reservation data is fictional.

import sqlite3

# Fictional times are minutes after midnight on one date.
db = sqlite3.connect(':memory:')
db.execute("""
CREATE TABLE reservations (
    room TEXT NOT NULL,
    start INTEGER NOT NULL,
    finish INTEGER NOT NULL,
    CHECK (0 <= start AND start < finish AND finish <= 1440)
)""")

def reserve(room, start, finish):
    conflict = db.execute("""
        SELECT 1 FROM reservations
        WHERE room = ? AND start < ? AND finish > ?
        LIMIT 1
    """, (room, finish, start)).fetchone()
    if conflict:
        raise ValueError('Overlapping reservation')
    db.execute('INSERT INTO reservations VALUES (?, ?, ?)',
               (room, start, finish))

def snapshot():
    return db.execute('SELECT * FROM reservations ORDER BY start').fetchall()

with db:
    reserve('Studio', 540, 600)  # 09:00–10:00
before = snapshot()
print('INITIAL:', before)
try:
    with db:
        reserve('Studio', 600, 660)  # Touching is allowed.
        reserve('Studio', 630, 690)  # Overlaps the new row.
except ValueError as error:
    print('Batch rejected:', error)
assert snapshot() == before
print('AFTER ROLLBACK:', snapshot())
with db:
    reserve('Studio', 600, 660)
print('AFTER VALID INSERT:', snapshot())
assert len(snapshot()) == 2
try:
    with db:
        reserve('Other room', 720, 700)
except sqlite3.IntegrityError:
    print('Database CHECK rejected a reversed interval.')
assert len(snapshot()) == 2
print('Rollback and interval checks passed.')
db.close()

SQLite reservation source, part 1

SQLite reservation source, part 2

Actual SQLite rollback and interval checks

Read the behavior and its boundaries

Intervals use [start, finish): a reservation ending at 10:00 does not conflict with one starting at 10:00. The overlap test requires an existing start before the proposed finish and an existing finish after the proposed start. SQL placeholders bind the room and times rather than interpolating text.

The seed is committed before the failing batch. Inside with db, a ValueError escapes after the second request, so the context manager rolls back the first insertion too. An equality assertion compares the database with its pre-batch snapshot. A later valid insertion succeeds, while a reversed interval fails the database CHECK constraint.

The expected row counts are one after rollback and two after the subsequent valid insert. This is a single-connection teaching example, not a complete multi-user booking system. Cross-connection concurrency needs a deliberate locking/transaction design, and these integer times refer to one date with no timezone semantics. The in-memory database disappears when closed.

Try one change

Catch the overlap error inside the with block instead of outside. Explain why swallowing the error changes what the context manager sees, and inspect whether the first insertion is committed.

I build PythonIDE, a Python coding app for iPhone and iPad. AI assisted with drafting and translation; the code and output were checked in PythonIDE on an iPhone simulator.

View PythonIDE on the App Store

來源:DEV Community · dev.to