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()
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.



Top comments (0)