Duplicate Rowid Assignment Causing Index Inconsistency in SQLite

Understanding Rowid Reuse and Index Corruption in a Single-Threaded WAL Environment

Issue Overview: Mismatched Index and Data Records Due to Duplicate Rowid Assignment

The core issue involves an SQLite database operating in Write-Ahead Logging (WAL) mode with two connections, where a single-threaded write process results in index/data inconsistency. After a series of insertions and deletions, the database reports corruption via pragma integrity_check, specifically citing a "wrong # of entries in index sqlite_autoindex_key." The problem manifests as index entries referencing rowids that no longer exist in the data table. For example:

• Insertions generate rowids 1, 2, 3, 4, 2, 5 (note the duplicate 2).
• Deleting rowid 2 removes both an index entry and a data record, leaving orphaned index entries.

SQLite’s rowid allocation mechanism is designed to avoid duplicates under normal conditions. When a table lacks an explicit INTEGER PRIMARY KEY, SQLite assigns a unique 64-bit signed integer rowid automatically. Rowid values are typically monotonically increasing, but deletions can create gaps. Reuse of rowids is possible only in specific scenarios (e.g., VACUUM operations or explicit INSERT statements that override rowid values). The observed duplicate assignment of rowid 2 during insertion suggests an anomaly in rowid management, compounded by improper schema design or transactional isolation.

Key factors contributing to the issue include:

1. Schema Design Flaws:

   • The table is named table, which is an SQL reserved keyword. While SQLite permits reserved words as identifiers when quoted, inconsistent quoting can lead to parsing errors or unintended behavior.
   • The key column is declared as TEXT PRIMARY KEY DEFAULT ''. This creates an implicit unique index (sqlite_autoindex_key) on the key column. However, the default empty string ('') for the key column introduces a high risk of primary key collisions if inserts omit explicit values for key.

2. Rowid Management:

   • The absence of an INTEGER PRIMARY KEY forces SQLite to manage rowids independently. When rows are deleted, their rowids become candidates for reuse in subsequent inserts if the AUTOINCREMENT keyword is not used. However, rowid reuse should not occur during normal operation unless the maximum rowid value is reached.

3. Transactional Isolation in WAL Mode:

   • WAL mode allows readers to coexist with writers, but write operations are serialized. The presence of two connections raises questions about whether read operations (e.g., index scans) interfere with write operations, especially if transactions are not properly bounded.

4. Filesystem and Mount Configuration:

   • The database resides on an ext3 filesystem. While ext3 is journaled, improper mount options (e.g., noatime, data=writeback) or filesystem corruption could lead to incomplete writes or index/data desynchronization.

Diagnosing Causes: Reserved Keywords, Rowid Reuse, and Transactional Gaps

1. Reserved Keyword Conflicts and Schema Misconfiguration

The use of table as a table name without proper quoting (e.g., [table] or "table") can cause parsing ambiguities. For example, the statement CREATE INDEX table_home_idx on table(home) might be misinterpreted by SQLite’s parser if table is treated as a keyword rather than an identifier. This could lead to incorrect index creation or silent failures.

Additionally, the key column’s default value (DEFAULT '') creates a unique constraint violation if multiple rows are inserted without explicitly specifying a key value. For example:

INSERT INTO "table" (home, name) VALUES ('home1', 'name1');  -- key = ''
INSERT INTO "table" (home, name) VALUES ('home2', 'name2');  -- key = '' (violates PRIMARY KEY)

This would result in an error on the second insert. However, if the application suppresses errors or uses INSERT OR IGNORE, duplicate rowids could theoretically occur due to retries.

2. Implicit Rowid Reuse Without AUTOINCREMENT

When a table lacks an INTEGER PRIMARY KEY, SQLite assigns rowids from a pool that includes gaps created by deletions. For example:

DELETE FROM "table" WHERE rowid = 2;
INSERT INTO "table" (key) VALUES ('key5');  -- rowid 2 may be reused

Without AUTOINCREMENT, SQLite is free to reuse rowids from previously deleted rows. However, this reuse should not occur while a transaction is active or if the rowid is still referenced by other data structures (e.g., indices).

The duplicate rowid 2 observed in the insertion sequence (1, 2, 3, 4, 2, 5) suggests either:

• A manual override of rowid via INSERT INTO "table" (rowid, ...) VALUES (2, ...);
• A race condition in rowid allocation due to concurrent transactions.

3. Incomplete Transactions and WAL Mode Edge Cases

The user mentions that operations are "processed in the SQLite transaction," but the exact transaction boundaries are unclear. If transactions are not explicitly committed or rolled back, partial writes could leave indices in an inconsistent state. For example:

BEGIN;
INSERT INTO "table" (key) VALUES ('key1');  -- rowid 1
INSERT INTO "table" (key) VALUES ('key2');  -- rowid 2
COMMIT;

BEGIN;
DELETE FROM "table" WHERE rowid = 2;
-- Crash or disconnect before COMMIT

In this scenario, the deletion of rowid 2 would not be finalized, leaving the index entry intact.

WAL mode introduces additional complexity:

• Writers append changes to the WAL file, which are later checkpointed into the main database.
• Readers using the second connection might see stale versions of the data, leading to incorrect index updates.

4. Filesystem and Storage Layer Issues

The ext3 filesystem is generally reliable, but mounting it with options like async or noatime can delay write operations, increasing the risk of corruption during crashes. Additionally, the 30-second sleep between inserts (as described in the user’s sequence) could allow filesystem buffers to flush inconsistently.

Resolving the Issue: Schema Corrections, Isolation Enforcement, and Integrity Checks

Step 1: Correct Schema Design and Reserved Keyword Handling

• Rename the Table: Replace table with a non-reserved keyword (e.g., records).
• Quote Identifiers: Use double quotes or square brackets for table names.
• Fix Primary Key Definition:

  CREATE TABLE "records" (
    home TEXT DEFAULT '',
    name TEXT DEFAULT '',
    age INTEGER DEFAULT 0,
    family_num INTEGER DEFAULT 0,  -- Correct typo: "famliy_num" → "family_num"
    height INTEGER DEFAULT 0,
    priv TEXT DEFAULT '',
    key TEXT PRIMARY KEY NOT NULL  -- Remove DEFAULT '' to prevent empty key collisions
  );
  

  • Remove DEFAULT '' from the key column to enforce explicit key values.
  • Add NOT NULL to prevent NULL key values.

Step 2: Enforce Explicit Transactions and Isolation Levels

• Use Explicit Transactions:

  BEGIN IMMEDIATE;  -- Acquire a write lock immediately
  INSERT INTO "records" (key, home) VALUES ('key1', 'home1');
  INSERT INTO "records" (key, home) VALUES ('key2', 'home2');
  COMMIT;
  

• Avoid Implicit Rowid Reuse:
  Add an INTEGER PRIMARY KEY AUTOINCREMENT column to prevent rowid reuse:

  CREATE TABLE "records" (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    key TEXT NOT NULL UNIQUE,
    ...
  );
  

  This guarantees monotonically increasing rowids, eliminating reuse.

Step 3: Validate Index Integrity and Filesystem Stability

• Run Integrity Checks:

  pragma quick_check;  -- Faster than integrity_check
  pragma foreign_key_check;
  

• Check WAL File Health:
  Ensure the WAL file (<database>-wal) and shared memory file (<database>-shm) are not orphaned or corrupted.

Step 4: Upgrade SQLite and Test on a Standard Filesystem

• Upgrade to SQLite 3.43.0 or newer to leverage bug fixes and improvements.
• Test on ext4 or ZFS: These filesystems offer better crash consistency guarantees.

Step 5: Reproduce the Issue in a Controlled Environment

• Minimal Repro Script:

  .open test.db
  CREATE TABLE "records" (key TEXT PRIMARY KEY NOT NULL);
  CREATE INDEX records_home_idx ON "records"(home);
  BEGIN;
  INSERT INTO "records" (key) VALUES ('key1');  -- rowid 1
  INSERT INTO "records" (key) VALUES ('key2');  -- rowid 2
  DELETE FROM "records" WHERE key = 'key2';
  INSERT INTO "records" (key) VALUES ('key5');  -- rowid 3 (not 2)
  COMMIT;
  pragma integrity_check;
  

• Monitor Rowid Allocation:

  SELECT rowid, * FROM "records";
  

By addressing schema design flaws, enforcing transactional boundaries, and validating storage layer integrity, the index/data inconsistency caused by duplicate rowids can be systematically resolved.