A Read-Only SQLite Script Can Commit Your Pending Write

A writer inserts the synthetic token A. The writer can read it, but a newly opened read-only connection sees an empty table. Then the writer calls executescript('SELECT 1;'). The observer now sees A, even though the script contains no write. A subsequent rollback does not remove it.

The qualifiers matter immediately: the connection uses sqlite3.LEGACY_TRANSACTION_CONTROL, and a transaction is pending when the call enters. Python's executescript contract specifies a commit before the script in that combination. This is not a claim that every SQLite connection commits before every script, or that SELECT itself commits unrelated work. Here, read-only describes the script text, not the writer connection, which has already inserted A.

For a migration runner, the mistake is reviewing only the SQL string. The application may intend A and the following operation to remain one reversible unit, while the driver crosses a boundary before that operation starts. A normal return, a caught exception, and a successful rollback call can each coexist with the wrong application outcome.

This playbook uses nine disposable local cases to locate that boundary. It compares execute, SELECT-only and empty scripts, legacy explicit BEGIN, script-local BEGIN, both Boolean autocommit modes, and a refusal guard. The evidence is deliberately small: exact A/B rows, transaction flags, exceptions, and fresh independent observations. The result is a bounded choice of transaction ownership, not a promise that an arbitrary script is atomic.

Define the pending-work boundary under review

The application contract is that A remains uncommitted until its owner explicitly resolves the unit containing it. The diagnostic question is therefore not whether a script writes rows. It is whether entering the selected API makes A independently visible before the owner authorizes that transition.

Each case starts from the same measured baseline: writer rows are ['A'], observer rows are [], and in_transaction is True. The observer uses another connection to the same file, not another cursor on the writer. A second cursor would share the very transaction whose visibility is in question.

The headline concerns a Python driver action. Readers comparing server-side DDL commit boundaries should keep the layer distinction intact. This experiment does not import MySQL rules into SQLite. Its decisive controls contain SELECT alone or no SQL at all; neither needs a schema change to expose the problem.

Use four operational decisions throughout the review. REPAIR means the runner can commit owner-controlled legacy work unexpectedly. REFUSE_ENTRY means a guard declines that call while preserving the owner's pending work. ACCEPT_BOUNDED means a deliberately selected regime matches the declared row and rollback expectations. HOLD means trustworthy evidence is insufficient to reconstruct a historical boundary or qualify a changed implementation.

These decisions are separate from test status. A diagnostic can pass precisely because it successfully demonstrates an unacceptable design. In particular, observing A committed in the unsafe cases is the expected experimental result, not approval to ship those cases as a reusable transaction wrapper. Decide against the application's intended unit, not against the appealing appearance of a green test summary.

Record the driver, engine and connection policy

The supplied commissioning run used CPython 3.13.5 linked to SQLite 3.46.1 on Linux-6.18.44-x86_64-with-glibc2.41. A fresh local reproduction for this article used the same reported identities. Both recorded the following sqlite_source_id() value:

2024-08-13 09:16:08 c9c2ab54ba1f5f46360f1b4f35d849cd3f080e6fc2b6c60e91b16c63f69aalt1

Preserve that identifier verbatim rather than replacing it with a presumed release identifier. An interpreter version and its linked SQLite build are separate evidence fields. The Python 3.13 documentation page displayed 3.13.16 when rechecked on October 15, 2026; that page label does not change the tested interpreter to 3.13.16.

The writer policy is explicit in every case. Legacy cases use LEGACY_TRANSACTION_CONTROL with isolation_level='DEFERRED', except S4, which uses None and SQL BEGIN. S7 selects autocommit=False. S8 selects True and establishes SQL BEGIN itself. Each case owns a new database file containing only the deliberately pending A before intervention.

Treat SQLite connection initialization as a separate admission responsibility. This lab does not inspect unrelated connection settings or prove that a general-purpose factory has initialized every required feature.

Do not use one Boolean for every transaction concept

Python's autocommit attribute selects a driver policy. isolation_level configures legacy handling. in_transaction reports the low-level transaction state; True does not require that a row has been changed. These are not interchangeable labels for whether a particular row is committed.

S7 makes the distinction concrete: after rollback, both connections report empty rows, yet in_transaction is True because the driver has opened a fresh transaction. Conversely, S8 keeps Python's autocommit=True while SQL BEGIN makes in_transaction=True. Record the policy and the flag together, then read the rows. Neither flag by itself identifies which work belongs to the current transaction.

Run the complete nine-case observer laboratory

Save the complete source below as sqlite_script_fixture.py in a writable directory you own. Run it with the intended interpreter:

python sqlite_script_fixture.py

It creates a separate local evidence directory and one database per case. No external database is accepted. Setup uses an autocommit connection to create work(token TEXT NOT NULL UNIQUE) and then closes that connection before the measured writer starts. This separates schema setup from A's transaction.

Every observer opens the same file afresh in read-only URI mode (mode=ro), reads the sorted token list, and closes. SQLite's independent-connection isolation rules explain why this ordinary observer cannot see the writer's uncommitted changes. The lab uses neither shared-cache dirty reads nor concurrent writers. Reopening also avoids accidentally reusing an observer's earlier read transaction.

The expectations in SPECS are authored before execution. They are not inferred from whatever rows happen to survive. For each case, the code requires the isolated baseline, applies one intervention, captures the exception and post-entry state, performs the mode-appropriate rollback, and reconciles final writer and observer rows. The trace is supporting evidence of issued SQL, not a replacement for those independent checks.

"""CPython 3.12+ transaction-mode lab, one disposable SQLite file per case."""

import hashlib

import json

import platform

import sqlite3

import tempfile

from datetime import datetime, timezone

from pathlib import Path

BAD = "INSERT INTO work VALUES('B'); INSERT INTO work VALUES('B');"

SPECS = [

 ('S1_execute_select','legacy','execute',[],[]),

 ('S2_script_select','legacy','select',['A'],['A']),

 ('S3_empty_script','legacy','empty',['A'],['A']),

 ('S4_legacy_explicit_begin','legacy_none','select',['A'],['A']),

 ('S5_naked_failed_script','legacy','bad',['A','B'],['A','B']),

 ('S6_script_begin_too_late','legacy','begin_bad',['A'],['A']),

 ('S7_autocommit_false','false','bad',[],[]),

 ('S8_autocommit_true_sql_begin','true','bad',[],[]),

 ('S9_guard_pending_legacy','legacy','guard',[],[])]

def require(ok,message):

    if not ok:

        raise RuntimeError(message)

def rows(db):

    return [r[0] for r in db.execute('SELECT token FROM work ORDER BY token').fetchall()]

def observer(path):

    db=sqlite3.connect(path.as_uri()+'?mode=ro',uri=True,autocommit=True)

    try:

        return rows(db)

    finally:

        db.close()

def guarded_script(db,text):

    if db.autocommit == sqlite3.LEGACY_TRANSACTION_CONTROL and db.in_transaction:

        raise ValueError('Pending legacy transaction: script entry refused')

    return db.executescript(text)

def main():

    folder=Path(tempfile.mkdtemp(prefix='script-evidence-',dir=Path(__file__).parent)).resolve()

    report={'status':'FAIL','started_utc':datetime.now(timezone.utc).isoformat(),

        'python':platform.python_version(),'sqlite':sqlite3.sqlite_version,

        'platform':platform.platform(),

        'source_sha256':hashlib.sha256(Path(__file__).read_bytes()).hexdigest(),'cases':[]}

    try:

        for name,mode,intervention,seen_expected,final_expected in SPECS:

            row={'id':name,'status':'FAIL','mode':mode,'trace':[]}; report['cases'].append(row)

            path=folder/(name+'.db')

            seed=sqlite3.connect(path,autocommit=True)

            seed.execute('CREATE TABLE work(token TEXT NOT NULL UNIQUE)')

            report['sqlite_source_id']=seed.execute('SELECT sqlite_source_id()').fetchone()[0]

            seed.close()

            policy={'false':False,'true':True}.get(mode,sqlite3.LEGACY_TRANSACTION_CONTROL)

            db=sqlite3.connect(path,autocommit=policy,

                isolation_level=None if mode=='legacy_none' else 'DEFERRED')

            try:

                db.set_trace_callback(row['trace'].append)

                row['initial_in_transaction']=db.in_transaction

                if mode in ('true','legacy_none'):

                    db.execute('BEGIN')

                db.execute("INSERT INTO work VALUES('A')")

                row['before']={'writer':rows(db),'observer':observer(path),

                               'in_transaction':db.in_transaction}

                require(row['before']=={'writer':['A'],'observer':[], 'in_transaction':True},

                        name+': baseline not isolated')

                caught=None

                try:

                    if intervention=='execute':

                        db.execute('SELECT 1').fetchall()

                    elif intervention=='select':

                        db.executescript('SELECT 1;').close()

                    elif intervention=='empty':

                        db.executescript('').close()

                    elif intervention=='bad':

                        db.executescript(BAD).close()

                    elif intervention=='begin_bad':

                        db.executescript('BEGIN; '+BAD+' COMMIT;').close()

                    else:

                        guarded_script(db,'SELECT 1;').close()

                except (sqlite3.IntegrityError,ValueError) as error:

                    caught=type(error).__name__

                expected_error=('IntegrityError' if intervention in ('bad','begin_bad') else

                                'ValueError' if intervention=='guard' else None)

                row.update(exception=caught,writer_after=rows(db),

                    observer_after=observer(path),txn_after=db.in_transaction)

                require(caught==expected_error,name+': wrong error')

                require(row['observer_after']==seen_expected,name+': wrong commit boundary')

                if mode=='true':

                    db.rollback()  # Documented no-op; confirm it did not end SQL BEGIN.

                    row['after_connection_rollback']=rows(db)

                    require(db.in_transaction and rows(db)==['A','B'],'No-op control changed')

                    db.execute('ROLLBACK')

                else:

                    db.rollback()

                row.update(final_observer=observer(path),final_writer=rows(db),

                           final_in_transaction=db.in_transaction)

                require(row['final_observer']==final_expected and row['final_writer']==final_expected,

                        name+': final rows wrong')

                require(row['final_in_transaction']==(mode=='false'),name+': transaction state wrong')

                row['status']='PASS'

            finally:

                if db.in_transaction:

                    db.execute('ROLLBACK')

                db.close()

        require(len(report['cases'])==9,'Missing case')

        report['status']='PASS_9_CASES'

    except Exception as error:

        report['error']={'type':type(error).__name__,'message':str(error)}

        raise

    finally:

        report['finished_utc']=datetime.now(timezone.utc).isoformat()

        target=folder/'results.json'; target.write_text(json.dumps(report,indent=2)+'\n')

        print(target)

if name=='__main__':

    main()

The embedded source's SHA-256, computed from UTF-8 bytes with LF line endings and a final newline, is:

d15134a409bf71e67ae0d8dd2e4f042e8e63a21182367ef805f0611aba325d1f

The supplied commissioning record reports PASS_9_CASES, starting at 2026-10-15T07:53:46.238609+00:00 and finishing at 2026-10-15T07:53:46.250981+00:00. The unchanged source was also executed locally for this article, starting at 2026-10-15T08:55:12.544608+00:00 and finishing at 2026-10-15T08:55:12.563333+00:00. It exited successfully with PASS_9_CASES, the same source digest, and matching checkpoints for all nine cases. These timestamps identify runs, not a performance benchmark.

The output is a printed path to results.json, not a printed PASS banner. Inspect that file. It contains environment identities, source digest, per-case traces, exception types, writer rows, observer rows, and transaction flags. The nine-case reconciliation table later in this article reproduces the measured outcomes; S8's additional method-rollback checkpoint is explained in its own section.

Read the checks according to what the code actually asserts. It directly checks baseline equality, expected exception type, observer-after rows, final writer and observer equality, final transaction state, and case count. It records writer_after and txn_after for review rather than comparing every recorded field to a separate expected constant. S8 additionally asserts that the connection-method rollback leaves its SQL transaction and A/B intact.

The report starts as FAIL. An unexpected exception inside the protected run is added to the in-memory report and re-raised; the finally block then attempts to save partial evidence. A write failure can prevent results.json from being saved, so retain process error output too. A missing case, wrong observer result, or wrong final state cannot become PASS_9_CASES with the unchanged source. Failures before the evidence folder or report exists are setup failures. An absent or corrupt JSON file is not an empty successful experiment.

Do not edit the expected lists to accommodate an unexplained result. Preserve the failing directory, interpreter identity, source bytes, and exception. A modified fixture has a different digest and needs its own execution record. Even a line-ending change alters the source bytes. The supplied PASS belongs only to the supplied bytes and environment; the fresh reproduction is separately attributed for the same reason.

The fixture uses only the standard library, but the selected interpreter must support the explicit autocommit API. Its opening docstring describes a CPython 3.12+ API requirement, not evidence that every such interpreter and linked SQLite build has passed. There are no package installations or browser dependencies to stand in for the actual database test.

Finally, distinguish measured checkpoints from cleanup. S7 records its final True transaction flag before the finally block issues SQL ROLLBACK and closes the connection. Because the trace list remains live, that cleanup statement also appears in the retained trace. Likewise, S7's initial transaction begins during connection creation, before the trace callback is installed. Missing an initial BEGIN in that trace does not contradict its recorded initial state.

Compare execute with SELECT-only executescript

S1_execute_select is the baseline control. A is pending on a legacy DEFERRED connection. Calling execute('SELECT 1').fetchall() leaves writer rows ['A'], observer rows [], and the transaction active. The later connection rollback removes A. Final writer and observer rows are empty, and the final transaction flag is False.

S2_script_select changes the intervention, not the pending token or connection policy. Calling executescript('SELECT 1;') returns normally, but the observer immediately sees ['A']. The transaction flag is now False. Calling rollback afterward leaves A visible to both connections.

That contrast rules out the SELECT result as the acceptance signal. Both interventions contain the same harmless constant query. One preserves the owner-controlled boundary; the other crosses it. Checking only that the call did not raise would accept both while missing the property the application actually needs.

For this bounded single-statement task, S1 supports selecting execute instead of routing the query through a script runner. It does not establish that every string can be transferred unchanged to execute, or authorize splitting arbitrary SQL into pieces. The admitted input here is one known SELECT, and its expected transaction behavior has been measured explicitly.

The API boundary can precede every script statement

In the reproduction, S2's writer trace places COMMIT before SELECT 1;. The CPython 3.13.5 cursor implementation likewise checks the legacy policy and engine transaction state before preparing script statements. The trace locates the action; the independent observer establishes A's visibility after it.

This does not mean every attempted call commits regardless of argument validity or earlier API checks. The claim concerns the admitted strings, healthy connection, and successful pre-entry commit demonstrated here. Keep that boundary narrower than a slogan about all failures or all scripts.

Mark an existing runner REPAIR when its contract says A must remain pending but S2 exposes it. The repair target is the runner's entry policy, not SELECT syntax and not a rollback method that no longer has A in its transaction.

Use empty text and legacy explicit BEGIN as controls

S3_empty_script removes even the SELECT. The input is the empty string, passed to executescript('') while A is pending on the same legacy policy. The recorded outcome matches S2: no exception, writer and observer both see A, the transaction is no longer active, and rollback leaves A present.

This is a particularly useful negative control because there is no script statement to blame. A review that searches the script for INSERT, ALTER, or COMMIT cannot explain this case. The relevant action belongs to the driver entry path. An empty script is not a neutral transaction operation under these admitted conditions.

Do not replace this case with a wrapper that skips empty strings and then claim to have reproduced the driver behavior. Such a wrapper may avoid this particular call, but that is a different intervention. The supplied case deliberately enters executescript with empty text and observes what that entry does.

S4_legacy_explicit_begin controls a different misunderstanding. It selects legacy mode with isolation_level=None, explicitly executes SQL BEGIN, and inserts A. Before the script, the observer still sees nothing and in_transaction is True. After the SELECT-only script, the observer sees A, and rollback cannot remove it.

None disables the driver's legacy implicit opening of transactions; it does not exempt an already pending legacy transaction from script-entry commit. In S4, the application opened that transaction itself. The entry condition concerns its presence, not whether the driver or caller originally opened it.

Read the legacy isolation-level explanation within its stated legacy scope. Its discussion of independence from isolation_level must not become a claim that both Boolean autocommit modes precommit too. S7 and S8 directly test those different policies.

Together, S3 and S4 eliminate two tempting explanations: that a write statement is required inside the script, or that explicit BEGIN under None protects A from legacy script entry. Neither explanation survives the observer evidence. The appropriate decision remains REPAIR when preserving the owner's pending A is required.

Separate the failing statement from earlier script writes

S5_naked_failed_script enters a script containing two ordinary inserts of B. The first can satisfy the table's UNIQUE constraint; the second cannot. There is no BEGIN or COMMIT in the script text. A is already pending when the legacy entry path commits it.

The recorded exception is sqlite3.IntegrityError. Nevertheless, after that exception the writer and observer both see ['A','B'], and no transaction remains active. The final rollback changes neither connection's rows. This is a failed script with committed progress, not a failed attempt that left the database untouched.

There are two boundaries to distinguish. A becomes committed before script statements begin. The first B insertion then runs without an explicit transaction enclosing both inserts. SQLite's implicit-transaction rules account for that completed statement committing independently. The duplicate insertion fails afterward. Treating the exception as a receipt for cancellation of the whole call loses both distinctions.

ABORT is not ROLLBACK

The fixture's UNIQUE constraint uses the default ABORT behavior. SQLite's ABORT conflict semantics undo the failing statement's changes while preserving earlier statements in an active transaction. ABORT is not the ROLLBACK conflict algorithm, and the Python exception class does not silently choose a larger application rollback unit.

In S5, the earlier B statement has already completed its own implicit transaction. In S6, S7, and S8, an earlier B remains pending in a transaction that the owner can still roll back. The same duplicate-key failure therefore needs transaction context before its consequences can be classified.

Do not repair S5 by swallowing the error or declaring the first B an acceptable partial result. That would change the application contract. Retain the first successful insertion and the duplicate attempt in the evidence, identify the already committed A/B, and classify the unsafe entry as REPAIR. No automatic production correction follows from this synthetic-token example.

Show why BEGIN inside the script is too late for A

S6_script_begin_too_late appears to add the missing safety wrapper: its text starts with BEGIN, inserts B twice, and ends with COMMIT. But text order is not API-entry order. The pending legacy transaction containing A is committed before the script's BEGIN executes.

After the duplicate B raises IntegrityError, the writer sees ['A','B'] while the observer sees only ['A']. The transaction flag is True. The trailing script COMMIT was not reached. Connection rollback then removes B, leaving final writer and observer rows ['A'] and a False transaction flag.

This outcome proves that rollback did useful work. B was pending in the script-local transaction, and rollback removed it. What it could not do was enlarge that transaction backward to include A. An application can therefore have a functioning rollback and a broken claim about the scope of the reversible unit at the same time.

Use the trace as a timeline: the reproduction records the earlier A insertion, the driver's COMMIT, the script's BEGIN, two B attempts, and the eventual ROLLBACK. The observer checkpoint between failure and rollback separates committed A from pending B. A final-only check would show A but would lose that distinction in the middle.

This is also why insertion receipts are not commit evidence. No generated identifier is needed here. Reading the exact synthetic token through an independent connection answers the relevant visibility question directly; a scalar associated with insertion would answer another question.

If the intended unit is A plus the script's work, adding BEGIN inside the legacy script is not the repair. Classify that proposal as REPAIR and move the decision outward to connection ownership and entry policy. A separately authorized transaction containing only script work would be a different contract, not retrospective proof that A was protected. Qualify that contract explicitly rather than changing the meaning of the original test after observing its failure.

Qualify autocommit=False as a different ownership model

S7_autocommit_false selects False when creating the writer. A and the failing two-insert script execute inside the driver-managed transaction. Unlike the pending legacy cases, script entry does not commit A. After the duplicate fails, writer rows are ['A','B'], observer rows are empty, and the transaction remains active.

Connection rollback removes both tokens. Final writer and observer rows are []. This supports ACCEPT_BOUNDED for the tested policy: one owner controls the transaction, the admitted script contains no transaction-control statements, and the exact failure path leaves A/B removable together.

The result is not permission to set False on an unknown borrowed connection and assume the surrounding application has adopted this model. Select it through the connection lifecycle, document which component owns commit and rollback, and qualify the scripts admitted to that connection. A helper's local preference cannot define the caller's larger unit of work.

In particular, adding script-level BEGIN needs separate review. SQLite does not nest BEGIN transactions. A script copied from an explicit-SQL ownership model may therefore conflict with a transaction already maintained by the driver. S7 intentionally uses the naked failing script, not S6's BEGIN-prefixed text.

A fresh transaction after rollback is expected here

The final True flag is not evidence that rollback failed. The Python rollback contract describes opening a new transaction after rollback under False. The reproduction shows the corresponding sequence: ROLLBACK, then BEGIN, with empty final rows.

Record the two statements and both row views rather than demanding False as a universal success flag. A check that rejects every active transaction after rollback would falsely reject this qualified case. A check that accepts True without reading rows would be equally weak, because the flag cannot distinguish the original work from a newly opened empty transaction.

The bounded acceptance rests on content restoration and deliberate policy together. It does not cover scripts that commit themselves, every possible SQLite error, crash durability, or production wrapper behavior that the fixture never executes.

Use SQL rollback when autocommit=True owns no DB-API transaction

S8_autocommit_true_sql_begin selects True at connection creation, then explicitly executes SQL BEGIN before inserting A. This is not the same sequence as allowing every statement to commit independently. The caller has established an engine transaction while leaving Python's connection-method transaction management disabled.

After the duplicate B raises IntegrityError, writer rows are ['A','B'], observer rows are [], and in_transaction is True. A has not been precommitted on script entry. These checkpoints match S7 so far, but the rollback action must be interpreted under S8's different ownership policy.

The fixture deliberately calls db.rollback() first as a negative control. It then records after_connection_rollback=['A','B'] and requires that the transaction remains active. That method call is a no-op here. The subsequent db.execute('ROLLBACK') removes A/B. Final writer and observer rows are empty, and the final transaction flag is False.

The CPython 3.13.5 rollback implementation makes the policy distinction explicit: its transaction-ending branches handle legacy and disabled-autocommit policies, not the True policy. The measured no-op is therefore consistent with the documented contract, not a reason to distrust all rollback operations.

One observation limit matters: S8 does not open another observer specifically between the method call and SQL ROLLBACK. Its no-op assertion uses surviving writer rows and active transaction state; the independently opened observers bracket the failure and final SQL rollback. Do not invent an extra checkpoint that the fixture did not collect.

Accept this regime only when the caller deliberately owns SQL BEGIN and SQL termination, and all relevant error paths use that ownership model. Renaming a helper to “rollback” does not establish what it executes. Changing autocommit to True on an unknown active connection is not this repair; it is a policy transition with its own commit consequences. S8 chooses True before work begins and proves the resulting bounded behavior.

Refuse an unsafe pending legacy script entry

S9_guard_pending_legacy applies the smallest prevention rule that matches the identified hazard. Before calling executescript, guarded_script checks whether the connection is in legacy mode and has an active transaction. If both conditions hold, it raises ValueError and never enters the script API.

The observed checkpoint is no script execution, writer rows ['A'], observer rows [], and an active transaction. The owner then rolls back A. Both final row lists are empty, and the final flag is False. This is REFUSE_ENTRY, not a successfully executed script and not an implicit decision to discard the work.

Notice that the guard does not inspect whether the text contains a write. That would miss S2 and S3, whose SELECT-only and empty inputs still cross the legacy boundary. The guarded condition is the connection state at entry, not a classification of the script's apparent purpose.

The check must also stay tied to the actual connection used for execution. In this single-owner fixture, nothing else changes that connection between the guard and call. A wrapper with additional hooks or shared connection use would need separate qualification; this helper is not a concurrency-control mechanism.

A guard must not decide to commit unknown work

Do not replace the refusal with a convenience commit, rollback, or mode toggle. A commit publishes the caller's pending work; a rollback discards it. Neither is a neutral preparation step. Python's autocommit attribute contract also states that changing the attribute to True commits pending work.

The owner must decide whether to commit A, abandon it, defer the script, use another appropriately initialized connection, or create a connection under another reviewed policy. Another connection does not automatically share A's pending transaction. These alternatives therefore need an explicit application decision, not a silent helper-level substitution.

The guard only prevents this known pre-entry hazard. It does not validate arbitrary SQL, guarantee atomicity, approve every idle legacy script, or reconstruct a commit that already occurred. Its value is narrower and concrete: refusal preserves the owner's ability to resolve the pending A demonstrated by this case.

Reconcile the commit timeline, not only the final rows

The following checkpoints were recorded in the supplied run and reproduced by the unchanged local execution. “Observer after” means a fresh independent read after the intervention or its expected exception, before the final rollback. “Final rows” matched on both writer and observer. True and False describe in_transaction, not the connection's autocommit policy.

Legacy means LEGACY_TRANSACTION_CONTROL; only S4 changes its isolation_level to None. The failing script is the two B inserts defined by BAD. S6 adds script BEGIN and an unreached COMMIT. S8 uses SQL ROLLBACK after its connection-method no-op control; the other cases use connection rollback for the measured final action.

Writer-after rows were ['A'] in S1–S4 and S9, and ['A','B'] in S5–S8. These writer views alone cannot distinguish S5's committed A/B from S7's pending A/B. The observer-after column supplies the missing evidence. Likewise, the same final ['A'] in S2 and S6 does not reveal that S6 also removed a pending B.

Read the trace callback output with its recording limits. Driver-issued COMMIT appears before the SELECT in S2 and before script BEGIN in S6. An engine-managed implicit commit after a completed statement need not appear as a separate traced SQL COMMIT. Absence of that string therefore cannot prove absence of a commit. Combine known statement order, transaction state, and independent visibility instead of treating the trace as a universal transaction log.

The observer tests this original live file, not a copied artifact. Qualification of committed state versus a backup artifact is a different workflow. Neither a readable backup nor a count of files created closes this transaction-boundary test.

A complete diagnosis preserves both the state that became committed and the state that remained reversible. Final row equality is necessary for the selected recovery expectation, but the intermediate checkpoint explains whether the intended unit was ever one transaction. That distinction is the reason to retain all nine rows rather than reducing the report to a single PASS value or one final table snapshot.

Recover after a commit without promising time reversal

When a pending legacy write has already crossed the entry boundary, begin with evidence preservation, not an automatic corrective statement. Retain the actual driver and SQLite identities, connection policy, literal script, preceding statement sequence, errors, transaction flags, and any trustworthy observer checkpoints. Identify who owned A and what state that owner intended to preserve.

In the laboratory, A is an invented token in a disposable file, so its original absence is known. Real application state may not be so recoverable. A current row containing the expected-looking value does not establish when it committed, whether another authorized operation later changed it, or whether the original unit contained additional effects not represented in that row.

SQLite's transaction lifecycle distinguishes work in the current transaction from work already committed. Applied to S6, rollback cancels pending B but does not reach A's completed transaction. A message saying that rollback returned successfully does not reconstruct that earlier history.

Compensation is a new transaction

A corrective write after an unintended commit is new work. It needs an authorized target state and evidence that applying the correction will not overwrite later legitimate changes. Its success cannot be relabeled as proof that the original transaction rolled back. The fixture supplies no production compensation automation and no universal inverse for an application's operations.

Use HOLD when the original values, affected unit, or ordering of later work cannot be established from trustworthy evidence. Holding the recovery claim does not require inventing an outage or diagnosing an engine failure. It means declining to make a stronger assertion than the retained history supports.

Keep diagnosis and repair separate in the recovery record. It is possible to establish that a runner permits unsafe entry and therefore needs REPAIR, while still holding judgment on which historical business changes that defect actually committed. Correcting the future entry policy is useful; it does not, by itself, resolve the past.

Assign transaction ownership and regression triggers

A maintainable design names three responsibilities. The connection factory selects the initial regime and makes it observable. The caller owns the pending unit and authorizes its resolution. The script runner owns its entry checks and the literal scripts it admits. No component should borrow another component's authority merely because it holds a connection reference.

Apply the responsibility split in a concrete review sequence:

1.    Identify the connection created for the operation, its actual autocommit policy, and any wrapper that changes it. Record the pending unit's owner before reviewing the SQL. A setting described in configuration is not enough when another layer replaces the connection or mutates policy afterward.

2.    State the required visibility checkpoints before running the test. For a protected A/B unit, the fresh observer must remain empty after the expected failure, and the selected rollback must leave both final row lists empty. Keep the expected exception and final transaction flag specific to the chosen regime.

3.    Exercise the runner's actual call path in a disposable integration fixture. Include entry hooks, the selected literal script, and the real rollback helper rather than testing a stand-in with similar names. This application integration check is proposed work, not an execution claimed by the nine-case standard-library laboratory.

4.    Assign an owner to each discrepancy. Unsafe pending legacy entry needs REPAIR; the pre-entry guard should produce REFUSE_ENTRY; a matched, intentionally selected regime may receive ACCEPT_BOUNDED. An unexplained result, missing evidence, or uncertain historical boundary requires HOLD, with the partial evidence retained.

Rerun after changing the interpreter, linked engine, connection factory, wrapper, rollback helper, guard placement, or script text. Preserve the previous record as a comparison, not as a certificate transferable to the new implementation. A different script containing transaction-control statements is not covered merely because the old BAD string passed under False.

The verification SELECT is intentionally simple. Background on small SQL verification queries may help reviewers read the token manifest, but analytical query design is not the qualification target. Here, row identity and visibility at named checkpoints matter more than an aggregate that happens to remain unchanged.

Keep this regression small enough to inspect end to end. Its value comes from isolating the API boundary with negative controls, not from representing every migration, recovery, or database lifecycle concern. Broader tests can complement it without obscuring the specific ownership decision these nine cases support.

Build database judgment through structured practice

The defensible decision is specific. Refuse a pending legacy script entry when its owner has not authorized a commit. For a single admitted SELECT, S1 preserves the pending boundary. For the tested failing script, False supports connection-method rollback of A/B, while True with SQL BEGIN requires SQL ROLLBACK. Neither regime should be installed blindly on an unknown active connection.

Practice explaining why S6 removes B but retains A, and why S7 ends with empty rows and an active transaction. Those two explanations force a reviewer to distinguish statement failure, transaction ownership, independent visibility, and cleanup. They are better evidence of judgment than memorizing that one API is “safe” or another is “unsafe” without conditions.

For broader structured learning, explore Refonte Learning's Database Administrator Essentials. The program page lists database design, SQL optimization, backup and recovery, practical projects, and personalized mentorship. Its stated duration is three months with a commitment of 12–14 hours per week. These are broader database-learning opportunities, not a promise that this exact Python sqlite3 laboratory is a named module.

Carry the same standard into that practice: define the unit, name its owner, select the transaction regime deliberately, and require the independent checkpoint that proves the property under review.