sqlite3.OperationalError: cannot start a transaction within a transaction on update when git diff includes deleted files

[Syntek-Studio]

Environment

• Python: 3.14.3
• SQLite: (system default)
• code-review-graph: (installed version)
• OS: Linux

Steps to reproduce

1. Have a working tree where git diff --name-only HEAD~1 returns a mix of deleted files and modified/added files (e.g. a branch with file deletions plus code changes).
2. Run code-review-graph update.

Error

sqlite3.OperationalError: cannot start a transaction within a transaction

Full traceback:

File "code_review_graph/tools/build.py", line 311, in build_or_update_graph
    result = incremental_update(root, store, base=base)
File "code_review_graph/incremental.py", line 515, in incremental_update
    store.store_file_nodes_edges(str(repo_root / rel_path), nodes, edges, fhash)
File "code_review_graph/graph.py", line 239, in store_file_nodes_edges
    self._conn.execute("BEGIN IMMEDIATE")
sqlite3.OperationalError: cannot start a transaction within a transaction

Root cause

GraphStore.__init__ opens the connection with the default isolation_level="". Python's sqlite3 module automatically issues an implicit BEGIN DEFERRED before the first DML statement in that mode.

In incremental_update (and full_build), when deleted files are detected (line 471 / line 356), store.remove_file_data() issues DELETE statements that silently open an implicit DEFERRED transaction. This transaction is never committed before store_file_nodes_edges is called for the next file, which then tries BEGIN IMMEDIATE — SQLite rejects nesting an explicit IMMEDIATE inside an already-open DEFERRED transaction.

Note: the same issue exists in full_build (lines 355–356 call remove_file_data on stale files before the parse loop).

Suggested fix

In graph.py, store_file_nodes_edges, flush any pending implicit transaction before issuing BEGIN IMMEDIATE:

def store_file_nodes_edges(self, file_path, nodes, edges, fhash=""):
    """Atomically replace all data for a file."""
    # Flush any implicit transaction started by a prior DML call (e.g.
    # remove_file_data DELETE statements). Python's sqlite3 auto-begins a
    # DEFERRED transaction on the first DML; BEGIN IMMEDIATE inside an active
    # transaction raises OperationalError on Python 3.12+.
    if self._conn.in_transaction:
        self._conn.commit()
    self._conn.execute("BEGIN IMMEDIATE")
    ...

Connection.in_transaction is available since Python 3.2.

[tirth8205]

Confirmed — this is a SQLite transaction nesting issue in graph.py. The BEGIN IMMEDIATE in store_file_nodes_edges conflicts when an implicit transaction is already open (e.g., from remove_file_data during deleted file handling). PR #94 addresses this. Tracking for the next release.

[gfleg77]

Confirming this still reproduces on v2.2.2 / Python 3.11 / Windows 10 (original report was Linux + 3.14.3), so the bug is platform-independent — as expected, since it's purely about Python sqlite3's legacy isolation_level="" auto-begin behavior.

Trigger in my case: code-review-graph build on a repo where 2 previously-indexed files had been added to the ignore pattern set, so full_build's stale-removal loop at incremental.py:355-356 was the one leaking the implicit transaction into the parallel parse loop.

Workaround while waiting for PR #94 — a minimal call-site fix (alternative to the defensive in_transaction check in store_file_nodes_edges):

--- a/code_review_graph/incremental.py
+++ b/code_review_graph/incremental.py
@@ full_build
     existing_files = set(store.get_all_files())
     current_abs = {str(repo_root / f) for f in files}
-    for stale in existing_files - current_abs:
+    stale_files = existing_files - current_abs
+    for stale in stale_files:
         store.remove_file_data(stale)
+    if stale_files:
+        store.commit()

Same pattern for the incremental_update deleted-files loop around line 471. This matches the already-correct watcher path at incremental.py:603 which does store.commit() after remove_file_data.

Any rough ETA on a release cut that includes PR #94? The CLI is effectively unusable for build / update once any file gets removed or ignored, so folks hitting this currently have to patch site-packages or downgrade.

[tirth8205]

This is the same SQLite concurrency bug as #110. The root cause is that Python's sqlite3 module auto-begins a DEFERRED transaction on the first DML statement when isolation_level is set, and then store_file_nodes_edges tries BEGIN IMMEDIATE inside the already-open transaction.

The fix (check self._conn.in_transaction and commit before BEGIN IMMEDIATE) is straightforward. This was closed — was it actually fixed in a subsequent release? Checking the code, this bug may still be present. If users are still seeing this error on current versions, this should be reopened.

[tirth8205]

Reopening: the SQLite transaction bug is still present in current code. graph.py line 239 calls self._conn.execute('BEGIN IMMEDIATE') without checking self._conn.in_transaction first. When remove_file_data() is called before (e.g., for deleted files in incremental_update), it issues DELETE statements that auto-begin a DEFERRED transaction in Python's sqlite3 module. The subsequent BEGIN IMMEDIATE then fails with 'cannot start a transaction within a transaction'.

Fix: add an in_transaction check before BEGIN IMMEDIATE:

if self._conn.in_transaction:
    self._conn.commit()
self._conn.execute('BEGIN IMMEDIATE')

Issue #110 is the same bug.

[kerimboukadida]

Hello there, so the bug is still there this is the versions i'm actually using :
code-review-graph: v2.2.2
Python : 3.13.11
SQLite: (system default)
OS: windows