Unexpected Partial INSERT Commit on Function Error During Transaction

Understanding Partial Row Insertion Despite Runtime Errors in Transactions

When executing an INSERT statement within an explicit transaction in SQLite, developers may encounter scenarios where partial row insertion occurs despite runtime errors (e.g., invalid function arguments). For example, consider a multi-row INSERT that includes a call to a function like nth_value() with invalid parameters. If one row’s computation succeeds (e.g., VALUES (0)) and another fails (e.g., nth_value(0, -1)), the error may cause the statement to abort, but the successful row remains committed to the table. This contradicts the expectation that all changes from the INSERT statement should be rolled back if any part of it fails.

This behavior stems from SQLite’s internal optimization mechanisms, specifically how it handles statement journals (temporary rollback logs for individual SQL statements). Historically, SQLite avoided creating statement journals unless explicitly necessary, as they incur performance overhead. However, modern SQL functions (e.g., JSON validation, window functions with strict argument checks) can raise runtime errors, which were not fully accounted for in the byte-code generator’s optimization logic.

Root Causes: Statement Journal Optimization and Error Detection Gaps

The core issue arises from three interrelated factors:

1. Byte-Code Generator Optimization Logic
   SQLite’s byte-code generator determines whether a statement journal is required based on static analysis of the SQL statement. A statement journal ensures that if a DML operation (e.g., INSERT, UPDATE) aborts partway through execution, all changes made by that statement are rolled back. The generator avoids creating a journal unless it detects conditions that necessitate one. These conditions include:

   • The statement modifies multiple rows.
   • The statement contains operations that might abort (e.g., constraint violations).
   • The statement is part of a larger transaction.

   However, prior to the fix in SQLite version 3.39.0, the byte-code generator did not account for runtime errors raised by SQL functions (e.g., json(), nth_value()). This oversight led to scenarios where the generator incorrectly skipped the creation of a statement journal, assuming no abort was possible.

2. Legacy Assumptions About Function Behavior
   Early versions of SQLite assumed that built-in functions would never raise errors—they would return NULL or default values instead. For example, dividing by zero in a query returns NULL, not an error. However, newer functions like json() or window functions such as nth_value() introduced strict validation logic that explicitly raises runtime errors for invalid inputs. The byte-code generator’s optimization logic did not evolve to recognize these functions as potential sources of aborts, leading to incomplete rollbacks.

3. Conflict Resolution Strategy Mismatch
   The default conflict resolution strategy for SQLite statements is ABORT, which rolls back the current statement (not the entire transaction) upon error. However, without a statement journal, the partial changes from the statement cannot be rolled back. This creates an inconsistency: the user expects atomicity at the statement level, but the absence of a journal leaves committed changes intact.

Resolving Partial Commits: Workarounds, Fixes, and Best Practices

1. Upgrade to SQLite 3.39.0 or Later

The fix for this issue was implemented in commit 5c95ae6c (August 2022). This patch modifies the byte-code generator to force the creation of a statement journal if a SQL statement contains any function call. While this ensures correctness, it may incur a performance penalty due to the overhead of journal management.

Verification Steps:

• Check your SQLite version:

  SELECT sqlite_version();  
  

• If the version is prior to 3.39.0, upgrade using the latest source or binary distribution.

2. Refactor Multi-Row INSERT Statements

If upgrading is not immediately feasible, restructure INSERT statements to isolate error-prone function calls from non-error-prone rows. For example, split the original INSERT into two separate statements:

INSERT INTO v0 (c1) VALUES (0);  
INSERT INTO v0 (c1) VALUES (nth_value(0, -1) OVER ());  

The first INSERT will commit successfully, while the second will fail and leave the transaction in a state where it can be rolled back explicitly. However, this approach sacrifices atomicity and may not be suitable for all use cases.

3. Use SAVEPOINT for Fine-Grained Control

Wrap error-prone operations in a SAVEPOINT to manually control rollback boundaries:

BEGIN;  
SAVEPOINT sp1;  
INSERT INTO v0 (c1) VALUES (0), (nth_value(0, -1) OVER ());  
RELEASE sp1;  
COMMIT;  

If the INSERT fails, roll back to the savepoint:

ROLLBACK TO sp1;  

This ensures that only the failed statement is rolled back, while other operations in the transaction remain unaffected.

4. Avoid Error-Prone Functions in Multi-Row Inserts

Validate inputs before inserting them into the database. For instance, compute the value of nth_value(0, -1) OVER () in a separate SELECT statement and check for errors before proceeding with the INSERT.

5. Enable Strict Function Error Handling (Advanced)

For custom SQL functions, explicitly mark those that may raise errors using the SQLITE_DIRECTONLY flag or other error-reporting mechanisms. This signals the byte-code generator to treat such functions as potential abort sources, ensuring statement journals are created.

6. Monitor Performance Implications

After applying the fix, benchmark critical transactions to assess the impact of statement journal creation. Use EXPLAIN to analyze byte-code generation:

EXPLAIN INSERT INTO v0 (c1) VALUES (0), (nth_value(0, -1) OVER ());  

Look for the OP_Transaction opcode with a BEGINSTMT flag, indicating a statement journal is active.

By addressing the byte-code generator’s oversight and adopting defensive coding practices, developers can ensure atomicity in multi-row INSERT operations, even when using modern SQL functions with strict error handling.