DEV Community

Wenlo
Wenlo

Posted on AI-assisted

Reject an overlapping reservation without leaving half a transaction

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()
Enter fullscreen mode Exit fullscreen mode

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

Top comments (0)