Repeated Rows When Modifying Tables During SELECT Iteration in SQLite

Understanding Row Visibility and Modification During Cursor Traversal

Issue Overview

When iterating over a table using a SELECT loop in SQLite and modifying the same table during iteration (via DELETE, INSERT, or UPDATE), developers may encounter unexpected behavior such as repeated rows, skipped rows, or phantom rows. This occurs because SQLite’s isolation model allows changes made within the same database connection to become visible to the ongoing query. The problem is exacerbated by the query planner’s use of indexes, the visitation order of rows, and the transactional boundaries governing visibility.

The crux of the issue lies in SQLite’s cursor stability guarantees. When a SELECT statement is executed, the query planner determines the order in which rows are visited based on available indexes and WHERE clause optimizations. If modifications are made to the table during iteration, subsequent steps of the cursor may encounter rows that have been altered, deleted, or inserted. For example:

• Deleting a row that has not yet been visited by the cursor might cause it to reappear if the deletion shifts the visitation order.
• Updating a row’s rowid could reposition it in the traversal sequence.
• Inserting new rows might interleave with existing rows in ways that violate the expected iteration order.

This behavior is not a defect but a consequence of SQLite’s ACID compliance and isolation levels. The documentation explicitly states that the visibility of modifications during iteration is undefined unless specific precautions are taken.

Factors Leading to Non-Deterministic Row Order and Visibility

Possible Causes

1. Query Planner Index Selection and Visitation Order
   SQLite’s query planner selects the most efficient access path for a query. If an index is used, rows are visited in the order dictated by the index structure (e.g., ascending or descending key values). Without an explicit ORDER BY clause, the traversal order is not guaranteed to align with the physical storage order (rowid sequence). Modifications to the table during iteration (e.g., deleting a row) may invalidate the index’s traversal logic, causing rows to be revisited or skipped.

2. Same-Connection Transaction Isolation
   SQLite operates with serializable transaction isolation by default. However, when a single connection modifies a table during an active SELECT query, those changes are immediately visible to subsequent sqlite3_step() calls. This is because the connection’s transaction context includes both the read (SELECT) and write (DELETE/INSERT/UPDATE) operations. For example:

   • Deleting a row that has not yet been visited by the cursor might cause the query planner to revisit earlier rows if the index structure is altered.
   • Inserting a row with a rowid lower than the current cursor position could reintroduce the row into the result set.

3. Lack of Result Set Materialization
   By default, SQLite processes SELECT queries lazily, fetching rows one at a time. This means the full result set is not precomputed unless forced by operations like ORDER BY without index support or Common Table Expressions (CTEs) with MATERIALIZED hints. Without materialization, modifications to the table can dynamically affect the rows yet to be processed by the cursor.

Guaranteeing Stable Iteration with Modifications

Troubleshooting Steps, Solutions & Fixes

1. Enforce Deterministic Visitation Order with ORDER BY +rowid

To prevent the query planner from using an index that could lead to non-deterministic row order, use an ORDER BY clause with a prefix that disables index optimization. The + operator before a column name forces SQLite to treat the expression as a literal, bypassing index-based sorting:

SELECT rowid, * FROM table WHERE ... ORDER BY +rowid ASC;

This ensures rows are visited in ascending rowid order, effectively materializing the result set at the start of the query. Modifications to "future" rows (those with higher rowid values) will not affect the iteration, as the traversal order is fixed. However, modifications to "past" rows (already visited) are safe, and inserts with rowid values higher than the current cursor position will not appear in the current iteration.

Caveats:

• This approach assumes rowid is stable and not modified during iteration.
• If the table uses WITHOUT ROWID or a custom primary key, adjust the ORDER BY clause accordingly (e.g., ORDER BY +primary_key).

2. Materialize the Result Set Using CTEs or Temporary Tables

For complex queries where ORDER BY is insufficient, precompute the result set into a temporary structure. This decouples the iteration from live table modifications.

Example with CTE and MATERIALIZED Hint:

WITH materialized_cte AS MATERIALIZED (
  SELECT rowid, * FROM table WHERE ...
)
SELECT * FROM materialized_cte ORDER BY rowid;

The MATERIALIZED hint forces SQLite to evaluate the CTE and store its results in an ephemeral table before iteration begins. Changes to the original table during iteration will not affect the materialized result set.

Alternative: Temporary Table:

BEGIN IMMEDIATE;
CREATE TEMP TABLE snapshot AS SELECT rowid, * FROM table WHERE ... ORDER BY rowid;
-- Iterate over 'snapshot' and modify the original table as needed
COMMIT;

This method is heavier but guarantees isolation, as the temporary table is unaffected by concurrent modifications.

3. Two-Pass Strategy: Collect First, Modify Later

Separate the read and write phases to avoid interference:

1. First Pass: Collect all rowid values or primary keys into a list.
2. Second Pass: Iterate over the list and perform modifications.

Example:

-- First pass: Collect keys
BEGIN IMMEDIATE;
CREATE TEMP TABLE keys AS SELECT rowid FROM table WHERE ... ORDER BY rowid ASC;

-- Second pass: Process keys
DECLARE @current_rowid INTEGER;
DECLARE cursor CURSOR FOR SELECT rowid FROM keys ORDER BY rowid;
OPEN cursor;
FETCH NEXT FROM cursor INTO @current_rowid;
WHILE @@FETCH_STATUS = 0 BEGIN
  -- Perform DELETE/UPDATE based on @current_rowid
  FETCH NEXT FROM cursor INTO @current_rowid;
END;
CLOSE cursor;
COMMIT;

This approach eliminates ambiguity, as modifications are based on a static snapshot of keys.

4. Use Separate Connections for Reading and Writing

Leverage SQLite’s connection-level isolation by using one connection for the SELECT query and another for modifications. Changes made in the writer connection will not be visible to the reader connection until the transaction is committed.

Workflow:

1. Reader Connection:

   BEGIN IMMEDIATE;
   SELECT rowid, * FROM table WHERE ... ORDER BY rowid;
   -- Step through results without modification
   

2. Writer Connection:

   BEGIN IMMEDIATE;
   -- Perform DELETES/INSERTS/UPDATES based on reader's results
   COMMIT;
   

This method is ideal for applications with concurrency control, though it requires careful transaction management.

Final Recommendations:

• Prefer ORDER BY +rowid ASC for simple compaction tasks (as described in the forum’s "tune" command example).
• Use temporary tables or CTEs for complex workflows requiring full snapshot isolation.
• Avoid modifying rows that have not yet been visited by the cursor unless the visitation order is strictly enforced.
• Test all scenarios with EXPLAIN QUERY PLAN to verify index usage and traversal order.