Determining Successful Inserts vs Ignored Conflicts in SQLite

Understanding INSERT OR IGNORE Behavior and sqlite3_changes() Nuances

Issue Overview: Distinguishing Between Actual Inserts and Ignored Conflicts

When executing an INSERT OR IGNORE statement in SQLite, developers often need to determine whether a new row was inserted or if the operation was ignored due to a uniqueness constraint violation. The sqlite3_step() function returns SQLITE_DONE in both scenarios, making it impossible to discern the outcome directly from the step result. This ambiguity necessitates a mechanism to differentiate between the two cases.

The sqlite3_changes() function is designed to report the number of rows modified by the most recent INSERT, UPDATE, or DELETE operation. For an INSERT OR IGNORE statement, this function returns the number of rows successfully inserted. If the insertion was ignored due to a conflict (e.g., a duplicate primary key), sqlite3_changes() returns 0. However, improper use of this function—such as invoking it within the same SQL statement or misinterpreting transaction boundaries—can lead to incorrect results.

A critical example involves the RETURNING changes() clause. When used within an UPDATE or INSERT statement, changes() may return values from prior operations if executed within the same implicit transaction. This behavior highlights the importance of understanding SQLite’s transactional model and the timing of sqlite3_changes() calls.

Possible Causes: Misinterpreting Transaction Scope and Function Timing

1. Incorrect Timing of sqlite3_changes() Invocation
   The sqlite3_changes() function reflects the total number of rows modified by the most recently completed SQL statement. If called during the execution of a statement (e.g., within a RETURNING clause), it may return stale data from a prior operation. For example:

   UPDATE tbl SET col = 1 RETURNING changes();  -- Might report prior changes  
   

   Here, changes() inside the RETURNING clause does not account for the current UPDATE’s modifications because the statement has not yet finalized.

2. Transaction Boundaries and Cumulative Changes
   SQLite operates in autocommit mode by default, treating each statement as an independent transaction. However, when explicit transactions (BEGIN/COMMIT) are used, sqlite3_changes() aggregates the total rows modified across all statements within the transaction. This can lead to unexpected values if intermediate operations modify rows.

3. Side Effects from Triggers or Foreign Key Actions
   If the target table has triggers or foreign key constraints with cascading actions, these may modify additional rows. Such side effects increment the sqlite3_changes() count, making it difficult to isolate the impact of the original INSERT OR IGNORE statement.

4. Batched Inserts and Partial Success
   When inserting multiple rows in a single statement (e.g., INSERT OR IGNORE INTO tbl VALUES (...), (...), ...), sqlite3_changes() reports the total number of rows inserted, excluding those ignored due to conflicts. Developers expecting per-row granularity may misinterpret this aggregate value.

Troubleshooting Steps, Solutions & Fixes: Ensuring Accurate Change Counting

1. Call sqlite3_changes() Immediately After Statement Execution

To capture the correct row count, invoke sqlite3_changes() immediately after sqlite3_step() returns SQLITE_DONE and before executing any subsequent SQL statements. For example:

int rc = sqlite3_step(stmt);
if (rc == SQLITE_DONE) {
    int rows_inserted = sqlite3_changes(db);
    if (rows_inserted > 0) {
        // Row was inserted
    } else {
        // Row was ignored
    }
}

This ensures no intervening operations alter the change count.

2. Avoid Using changes() Within the Same Statement

Do not rely on the changes() SQL function within RETURNING clauses or nested queries. Instead, use application-layer calls to sqlite3_changes() after the statement completes. For example, instead of:

INSERT OR IGNORE INTO tbl (...) VALUES (...) RETURNING changes();

Execute the insertion and then check sqlite3_changes() programmatically.

3. Isolate Operations in Explicit Transactions

When using transactions, reset the change count before critical operations:

sqlite3_exec(db, "BEGIN;", 0, 0, 0);
// Execute INSERT OR IGNORE
int changes = sqlite3_changes(db);
sqlite3_exec(db, "COMMIT;", 0, 0, 0);

This prevents cumulative counts from prior statements within the transaction.

4. Account for Triggers and Cascading Actions

Review table definitions for triggers or foreign key constraints. Test INSERT OR IGNORE in isolation to determine whether side effects contribute to the change count. If necessary, disable triggers temporarily using PRAGMA defer_foreign_keys=ON; or PRAGMA ignore_triggers=ON; (if supported).

5. Use sqlite3_total_changes() for Session-Wide Tracking

For advanced scenarios, sqlite3_total_changes() returns the total rows modified since the database connection was opened. Differentiate between session-wide and per-operation counts:

int before = sqlite3_total_changes(db);
// Execute INSERT OR IGNORE
int after = sqlite3_total_changes(db);
int rows_inserted = after - before;

6. Validate with last_insert_rowid() for Single-Row Inserts

For tables with an INTEGER PRIMARY KEY, sqlite3_last_insert_rowid() returns the auto-incremented ID of the most recently inserted row. If the insertion was ignored, this value remains unchanged:

sqlite3_int64 before_id = sqlite3_last_insert_rowid(db);
// Execute INSERT OR IGNORE
sqlite3_int64 after_id = sqlite3_last_insert_rowid(db);
if (before_id != after_id) {
    // Insert succeeded
}

7. Test with Direct Queries for Conflicts

For complex scenarios, execute a SELECT before insertion to check for existing rows. This avoids reliance on sqlite3_changes():

// Pseudocode
int exists = 0;
sqlite3_prepare_v2(db, "SELECT 1 FROM tbl WHERE key = ?", -1, &stmt, 0);
sqlite3_bind_text(stmt, 1, key, -1, SQLITE_STATIC);
if (sqlite3_step(stmt) == SQLITE_ROW) {
    exists = 1;
}
sqlite3_reset(stmt);
if (!exists) {
    // Proceed with INSERT
}

8. Benchmark and Profile Edge Cases

Create unit tests for scenarios such as:

• Inserting a duplicate row in a table with a unique constraint.
• Inserting multiple rows with mixed conflicts.
• Concurrent transactions modifying the same table.
  Example test for batched inserts:

-- Setup
CREATE TABLE test (id INTEGER PRIMARY KEY, val UNIQUE);
INSERT OR IGNORE INTO test VALUES (1, 'a'), (2, 'b'), (3, 'c');
-- Returns 3 changes
INSERT OR IGNORE INTO test VALUES (2, 'b'), (4, 'd');
-- sqlite3_changes() should return 1 (only 'd' inserted)

By adhering to these practices, developers can reliably distinguish between successful inserts and ignored conflicts while avoiding common pitfalls associated with SQLite’s change-counting mechanics.