Module 7: Test Data And Isolation In Integration

3. The transaction rollback as isolation

Next →

Description

With the diagnosis firm, the first cure arrives, and it's one of the most elegant there is in integration testing: using a transaction's rollback to isolate. The idea is as pretty as it is simple. A database lets you open a transaction, make all the changes you want inside it, and then decide: either you commit them with commit —and they stay permanent— or you discard them with rollback —and they disappear as if they never happened, leaving the database exactly as it was before opening the transaction—. The isolation technique is to leverage that second option: you wrap each test in a transaction and, when it ends, you rollback. What the test wrote vanishes, and the next test finds the database just as it was, without recreating it. You don't delete rows one by one, you don't destroy and rebuild the table, you don't reload the schema: you simply undo. It's fast —reverting is cheap— and it's total —it reverts everything the test touched, whether or not you anticipated each table—.

But this technique has a hard precondition that has to be understood before trusting it, or it will bite you. The rollback can only undo what hasn't been committed yet. If the code under test commits midway, that commit makes its changes permanent, and your later rollback no longer has anything to undo over them —the database stays contaminated anyway—. And it turns out that our SqliteBookingRepository.save does exactly that: it ends with self._conn.commit(). So this lesson has two honest faces: the rollback technique works wonderfully when you control the transaction and nobody commits ahead of time, and it isolates nothing when the code under test commits on its own. You're going to see both with real output, and you'll come out knowing when the rollback is the right tool and when you have to go to lesson 4's new-database fixture.

Connection to the module: lesson 2 told you the cure is giving each test a known state; this one gives you the first way to achieve it —reverting what the test wrote— without destroying or recreating the database. It's the technique the doubles guide previewed when, talking about save's commit, it noted that module 7 would use "writing without commit and doing rollback at the end". Here we fulfill that promise and add the fine print. Lesson 4 will give you the other way —a new database per test— that doesn't have the commit precondition and is therefore the workhorse when you integrate through a service that commits. Understanding both, and when to use each, is what lets you isolate any suite, not just the easy ones.

Analogy: the pencil draft

Think of how an accountant works who does complicated calculations before writing them up clean. They have a draft sheet where they write in pencil: they try a sum, cross it out, try another, jot down tentative totals. While it's in pencil, nothing is final: if the calculation doesn't add up, they erase everything and the sheet is clean again, ready for the next calculation, without needing a new sheet. Only when the calculation is right do they write it in ink in the ledger, and there it is permanent: ink doesn't erase.

The pencil is the transaction; the eraser is the rollback; the ink is the commit. Isolating a test with rollback is making it work in pencil: the test writes what it needs, verifies, and at the end you erase —rollback— so the sheet is left as it was, without recreating it. The next test writes in pencil on the same freshly erased sheet. Everything works wonderfully as long as nobody puts anything in ink midway through the calculation. And there's the trap: if the code you're testing, in the middle of its work, grabs the pen and writes a total to the ledger —does commit—, the eraser can no longer erase that line. That's why the rollback isolates perfectly when the whole process is in pencil, and fails when the process, on its own, dips the pen. Our save dips the pen on every save; keeping that in mind is half the lesson.

The mechanism: reverting leaves the database clean without recreating it

Let's start with the heart of the technique, isolated from everything else, with raw SQL. We want to see, with our own eyes, that after a rollback the row we wrote disappears but the table is still there: we recreate nothing, we just undid.

# tests/test_rollback_mechanism.py — the rollback mechanism, with raw SQL
import sqlite3
from reservo.sqlite_repo import SCHEMA

def test_rollback_discards_writes_without_recreating_the_db():
    conn = sqlite3.connect(":memory:")
    conn.execute(SCHEMA)
    conn.commit()                          # the schema is permanent

    # We write a row INSIDE the transaction, without commit.
    conn.execute(
        "INSERT INTO bookings VALUES "
        "('bk-1','focus','m-ana','2026-03-10T09:00:00','2026-03-10T12:00:00','confirmed',6000)")
    before = conn.execute("SELECT COUNT(*) FROM bookings").fetchone()[0]

    conn.rollback()                        # we revert the transaction
    after = conn.execute("SELECT COUNT(*) FROM bookings").fetchone()[0]

    # The table still exists (we didn't recreate it), but the row is gone.
    table_exists = conn.execute(
        "SELECT name FROM sqlite_master WHERE type='table' AND name='bookings'"
    ).fetchone() is not None

    print(f"\nrows before={before}  rows after={after}  table_exists={table_exists}")
    assert before == 1
    assert after == 0
    assert table_exists is True

Notice the detail that makes this work: we create the schema and commit it before the part that matters to us, so the table is permanent. Then we insert the row without commit: that row lives only inside the open transaction. We count —there's one—, we rollback, and we count again. The row disappeared, but the table still exists, because its creation was committed before and the rollback doesn't touch it. Let's run with -s to see the print.

What to expect. On my machine (Python 3.14.0, pytest 9.1.1):

python3 -m pytest tests/test_rollback_mechanism.py -v -s
============================= test session starts ==============================
platform darwin -- Python 3.14.0, pytest-9.1.1, pluggy-1.6.0
collected 1 item

tests/test_rollback_mechanism.py::test_rollback_discards_writes_without_recreating_the_db
rows before=1  rows after=0  table_exists=True
PASSED

============================== 1 passed in 0.01s ===============================

There's the technique in one line of output: rows before=1 rows after=0 table_exists=True. We wrote a row, reverted it, and the database went back to zero rows without our recreating the table —table_exists=True confirms it—. That's what makes the rollback special compared to other ways of cleaning: it doesn't rebuild the database from the schema (slow), it doesn't delete row by row (fragile if you forget a table); it simply discards everything uncommitted in one stroke. That's why, when it applies, it's the fastest and most complete isolation technique there is.

The fixture that reverts in the teardown

Now let's turn the mechanism into real isolation of a suite. The idea: a database that lives for the whole module —the schema is created a single time—, and a fixture per test that, when it ends, does rollback to discard what that test wrote. Since the schema was committed once and each test's writes aren't committed, each test starts from an empty table over the same database, without ever recreating it.

# tests/test_rollback_isolation.py — isolate with rollback over a shared DB
import sqlite3
import pytest
from reservo.sqlite_repo import SCHEMA

@pytest.fixture(scope="module")
def db():
    # ONE DB for the whole module; the schema is created once and is permanent.
    conn = sqlite3.connect(":memory:")
    conn.execute(SCHEMA)
    conn.commit()
    yield conn
    conn.close()

@pytest.fixture
def conn(db):
    # Each test runs over the SAME connection, inside a transaction...
    yield db
    db.rollback()   # ...that's reverted at the end: the DB stays clean without recreating it

def seed_booking(conn, bid, room_id="focus"):
    # Inserts WITHOUT commit: the row lives only in the test's transaction.
    conn.execute(
        "INSERT INTO bookings VALUES (?,?,?,?,?,?,?)",
        (bid, room_id, "m-ana", "2026-03-10T09:00:00",
         "2026-03-10T12:00:00", "confirmed", 6000))

def count(conn, room_id="focus"):
    return conn.execute(
        "SELECT COUNT(*) FROM bookings WHERE room_id = ?", (room_id,)).fetchone()[0]

def test_a_seeds_two(conn):
    seed_booking(conn, "bk-1")
    seed_booking(conn, "bk-2")
    assert count(conn) == 2

def test_b_starts_clean(conn):
    assert count(conn) == 0        # test A's rollback cleaned the table
    seed_booking(conn, "bk-3")
    assert count(conn) == 1

def test_c_also_starts_clean(conn):
    assert count(conn) == 0        # and also after test B
    seed_booking(conn, "bk-4")
    assert count(conn) == 1

There are two fixtures, and the distinction between them is the key. The db fixture, of module scope, creates the connection and the schema once for the whole file: it's expensive, we don't want to repeat it per test. The conn fixture, of the default scope (function), runs once per test: it hands over the same db connection and, in its teardown —after the yield—, does db.rollback(). So test A seeds two bookings inside its transaction, verifies there are two, and when it ends the rollback discards them; test B opens its own transaction over a table that —thanks to that rollback— is empty again. Nobody recreates the database; it's just reverted between tests.

What to expect. On my machine (Python 3.14.0, pytest 9.1.1):

python3 -m pytest tests/test_rollback_isolation.py -v
============================= test session starts ==============================
platform darwin -- Python 3.14.0, pytest-9.1.1, pluggy-1.6.0
collected 3 items

tests/test_rollback_isolation.py::test_a_seeds_two PASSED                  [ 33%]
tests/test_rollback_isolation.py::test_b_starts_clean PASSED              [ 66%]
tests/test_rollback_isolation.py::test_c_also_starts_clean PASSED         [100%]

============================== 3 passed in 0.01s ===============================

Three greens, and notice what tests B and C assert: count == 0 at the start. Over a database shared by the whole module, each starts with the table empty because the previous one's rollback erased its rows. And the proof that this is real isolation and not a coincidence of the order: let's run it backwards.

python3 -m pytest tests/test_rollback_isolation.py::test_c_also_starts_clean \
                  tests/test_rollback_isolation.py::test_b_starts_clean \
                  tests/test_rollback_isolation.py::test_a_seeds_two -v
collected 3 items

tests/test_rollback_isolation.py::test_c_also_starts_clean PASSED         [ 33%]
tests/test_rollback_isolation.py::test_b_starts_clean PASSED              [ 66%]
tests/test_rollback_isolation.py::test_a_seeds_two PASSED                 [100%]

Green in reversed order too. Each test starts from a clean table regardless of who ran before, because each one's rollback leaves the database as it was. That's exactly what lesson 2 asked for —each test with a known state— achieved without recreating the database a single time.

The hard precondition: the commit of the code under test

Here comes the fine print, and it's honest and important. Everything above worked because we controlled the transaction: we seeded with a raw INSERT without commit, and the only one who commits or reverts is our teardown. But what happens if instead of seeding with raw SQL we integrate through the real service, whose save commits? Let's test it, because the result is the most valuable lesson of all.

# tests/test_rollback_caveat.py — the rollback does NOT isolate if the code under test commits
import sqlite3
import pytest
from datetime import datetime
from reservo.calendar import Calendar
from reservo.doubles import FixedClock, SpyEmailSender, StubPaymentGateway
from reservo.models import Member, Room
from reservo.services import BookingService
from reservo.sqlite_repo import SqliteBookingRepository, SCHEMA

FOCUS = Room("focus", "Focus", 4, 2500); ANA = Member("m-ana", "Ana", "pro")

@pytest.fixture(scope="module")
def db():
    conn = sqlite3.connect(":memory:"); conn.execute(SCHEMA); conn.commit()
    yield conn; conn.close()

@pytest.fixture
def conn(db):
    yield db
    db.rollback()   # we try to revert... but save() already committed

def make_service(repo):
    return BookingService(Calendar(), FixedClock(datetime(2026, 3, 1, 9)),
                          StubPaymentGateway(True), SpyEmailSender(), repo)

def count(conn):
    return conn.execute("SELECT COUNT(*) FROM bookings").fetchone()[0]

def test_a_books_through_the_service(conn):
    make_service(SqliteBookingRepository(conn)).book(
        FOCUS, ANA, datetime(2026, 3, 10, 9), datetime(2026, 3, 10, 12))
    assert count(conn) == 1

def test_b_expects_clean_but_inherits(conn):
    assert count(conn) == 0   # the rollback should have cleaned... but save committed

It's the same rollback fixture as before. The only difference is that test A no longer seeds with raw SQL: it books through the service, and book calls repo.save, which commits. Test B expects A's rollback to have cleaned the table.

What to expect. On my machine (Python 3.14.0, pytest 9.1.1):

python3 -m pytest tests/test_rollback_caveat.py -v
============================= test session starts ==============================
platform darwin -- Python 3.14.0, pytest-9.1.1, pluggy-1.6.0
collected 2 items

tests/test_rollback_caveat.py::test_a_books_through_the_service PASSED     [ 50%]
tests/test_rollback_caveat.py::test_b_expects_clean_but_inherits FAILED   [100%]

=================================== FAILURES ===================================
_______________________ test_b_expects_clean_but_inherits _______________________

    def test_b_expects_clean_but_inherits(conn):
>       assert count(conn) == 0   # the rollback should have cleaned... but save committed
E       assert 1 == 0

It fails. Test B expected an empty table and found test A's booking, despite the rollback. Why? Because test A's book, internally, called save, which did commit, and that commit made the booking permanent before our teardown could revert anything. When the rollback runs, the transaction that contained that row was already committed; there's nothing left to undo. The pencil was written in ink midway through the calculation, and the eraser doesn't erase the ink.

This is the rollback's precondition, demonstrated: it only isolates if nobody commits inside the test. It works wonderfully when you control the transaction —tests that seed with raw SQL, or a repository designed not to commit and to leave the commit/rollback to whoever calls it—. It doesn't work, as-is, when you integrate through a service that commits on every write, like ours. And there's the reason for lesson 4's existence: when the code under test commits, the way out isn't to fight the transactions, but to give each test a new database, so the commit has no one to contaminate because that database dies with the test.

When the rollback is the right tool

Let's gather the criterion, because it's what you take away. The rollback as isolation shines when two conditions hold. First: you control the transaction —the test or the fixture decides when it's committed or reverted, and the code under test doesn't commit on its own—. Second: recreating the database is expensive —a big schema, many tables, reference data to load—, and you want to pay that cost a single time (in the module- or session-scope fixture) and revert cheaply between tests. In that scenario, the rollback is unbeatable: you pay the CREATE once and each test costs a rollback, which is almost free.

The rollback is not the tool when the code under test commits inside the test —like our service— and you don't want to or can't change it to defer the commit. In that case, lesson 4's new-database-per-test is simpler and more robust: it doesn't depend on anyone committing, because each test has its own database that's destroyed entirely when it ends. Many teams, in fact, use both depending on the case: rollback for tests that operate over the repository or the SQL directly controlling the transaction, and a new database for tests that cross services that commit. Knowing which applies in each case —and why— is what this lesson leaves you.

Common mistakes

Expecting the rollback to undo someone else's commit. What happens: the test is wrapped in a transaction and a rollback is done at the end, but the code under test did commit inside, and the database stays contaminated. Why it happens: one thinks of "the test's transaction" as if it encompassed everything, but an intermediate commit closes that transaction and opens another. How to detect it: if you isolate with rollback and still see state inheritance, look for a commit inside the code you test (in Reservo, save's). How to fix it: either defer that commit (a repository that doesn't commit and leaves control to the caller), or use lesson 4's new-database-per-test.

Recreating the schema in each test "to be safe". What happens: someone puts the CREATE TABLE and the reference-data loading in the function-scope fixture, running for every test. Why it happens: it seems the safest. How to detect it: if your integration suite is slow and the profile shows time in creating tables and loading repeated seeds, you're paying the expensive cost N times. How to fix it: put the expensive and stable (schema, immutable reference data) in a module- or session-scope fixture, commit it once, and use the per-test rollback for what changes. You pay the CREATE once and it reverts cheaply.

Doing rollback in the test body instead of in the teardown. What happens: conn.rollback() is put as the last line of the test. Why it happens: it's the most direct. How to detect it: if the test fails before that line, the rollback doesn't run and the next test inherits the garbage —the same problem as the DELETE at the end from lesson 1—. How to fix it: put the rollback after the yield in a fixture, where pytest runs it no matter what, even if the test fails. The isolation lives in the fixture's teardown, not in the test body.

Exercises

Exercise 1 — Predict with and without commit. You have the conn fixture that does rollback in the teardown. A test does, in this order: conn.execute("INSERT ...") (without commit), then assert count(conn) == 1. The next test does assert count(conn) == 0. Predict the result. Now change the first test so that, after the INSERT, it does conn.commit(). Predict again.

See solution

Without commit: the first test inserts inside its transaction and sees count == 1 (passes). In its teardown, the fixture does rollback, which discards that uncommitted insertion. The second test sees count == 0 (passes). Both green: it's the rollback fixture case working, because nobody committed.

With commit: the first test inserts and commits; the row stays permanent. It sees count == 1 (passes). In the teardown, the fixture does rollback, but the row is already committed, so there's nothing to revert over it: it stays in the table. The second test sees count == 1, not 0, and assert count(conn) == 0 fails. It's exactly the caveat case: a commit inside the test defeats the teardown's rollback.

The rule you're fixing: the rollback undoes only the uncommitted. The presence of a single commit within the test's scope breaks the rollback isolation. That's why you have to know whether the code under test commits —and ours, via save, does—.

Exercise 2 — Why two fixtures and not one. In the rollback suite, the schema is created in the db fixture (module scope) and the rollback is in the conn fixture (function scope). Explain what would break if you put everything in a single function-scope fixture that creates the connection, the schema, does yield, and then rollback. Would it isolate the same? What cost would change?

See solution

It would isolate the same —in fact, it would isolate even better, because each test would have its own connection and its own database—, but the rollback would be unnecessary: if each test creates its own :memory: connection and its own schema, the whole database dies when the connection closes at the end of the test, without needing to revert anything. That is, precisely, lesson 4's new-database-per-test fixture, not the rollback technique.

What changes is the cost: creating the connection and running the CREATE TABLE (plus any reference-data loading) in each test means paying that cost N times. The point of splitting into two fixtures —db of module scope for the expensive and stable, conn of function scope for the cheap rollback— is to pay the CREATE a single time and have the per-test isolation cost only a rollback. With a trivial schema like Reservo's, the difference is negligible; with a big schema and heavy reference data, it's the difference between a fast suite and a slow one. The rollback technique exists precisely for that case: when recreating is expensive and you want to do it once.

Exercise 3 — Rescue the rollback isolation from the service case. The caveat showed that integrating through book (which commits) breaks the rollback. Without switching to lesson 4's new-database-per-test, propose a change in the repository's design that would make the rollback isolate again, and explain what cost or risk that change has.

See solution

The change is removing the commit from save and leaving the transaction control to whoever calls the repository. That is, save only does the INSERT/UPDATE and doesn't commit; the one who decides commit or rollback is the higher-level code (in production, a "unit of work" or the request handler; in the tests, the fixture). With save not committing, the booking book creates would live inside the test's transaction, and the teardown's rollback would discard it: the rollback isolation would work again even if you integrate through the service.

The cost and the risk: moving the transaction control outward is a real design decision with consequences. In production, someone has to remember to commit, and if they forget, the bookings don't persist —a serious bug—. It also changes the repository's contract: it's no longer "save and it stays permanent" but "save inside a transaction that someone else commits". It's a legitimate and common pattern (the "Unit of Work"), but it's an architecture decision, not a test trick. That's why many teams, instead of rewriting the repository to be able to isolate with rollback, prefer lesson 4's new-database-per-test: it isolates without asking anything of the production code's design. Both are valid; the choice depends on whether external transaction control serves you in production too or you'd only do it for the tests.

Summary and next step

In this lesson you installed the first isolation technique: the transaction rollback. You saw the mechanism with raw SQL —a row written and reverted, the database to zero without recreating the table (rows before=1 rows after=0 table_exists=True)— and turned it into isolation of a suite with two fixtures: a module-scope one that creates the schema once, and a per-test one that does rollback in the teardown, leaving each test with the table clean in any order. And you learned the hard precondition, demonstrated with a real failure: the rollback undoes only the uncommitted, so a commit inside the test —like save's— defeats it. With that you know when the rollback is the right tool (you control the transaction, recreating is expensive) and when not (the code under test commits).

Before moving on you should be able to: explain why the rollback leaves the database clean without recreating it; write the module/function fixture pair that isolates with rollback; and predict why an intermediate commit breaks the isolation.

What comes next is the other technique, the one that doesn't have the commit precondition and therefore solves the case the rollback couldn't. In lesson 4 you're going to write a fixture with yield that creates a fresh temporary database per test and destroys it in the teardown. Since each test has its own database, save's commit no longer contaminates anyone: there's no neighbor to inherit, because the database dies with the test. You're going to see the yield as the exact border between setup and teardown, and understand why this is the tool you'll use most when you integrate through services that commit.

Resources

← Previous2. Why shared real state contaminatesNext →4. Temporary DB fixtures with yield