A SQLite Import Can Block Normal Saves

Article · Interrupted execution

An import runs on a background thread, but ordinary saves now return busy errors. Moving the work away from the interface did not create another SQLite writer slot.

WAL mode lets readers and a writer overlap. It still serializes writers. Look at the import's transaction lifetime before increasing the connection count or adding a longer timeout. A transaction around the whole input can keep write access long after the next foreground save needs it.

For an import that may commit progressively, I would make each batch small and resumable. Store its rows and checkpoint in the same transaction. Then give foreground work a deliberate turn between batches.

Reproduce contention without a timing guess

The fixture opens two connections to a temporary file and enables WAL. One connection imports data. The other represents a foreground save to a settings row. Both are in the same process, on the same host.

The importer begins an immediate transaction, inserts two rows, and advances its progress marker. Before that transaction ends, the foreground connection tries to save. Its busy timeout is zero so the result is a direct SQLITE_BUSY, rather than a measurement of a retry policy.

The fixture rolls back the import transaction. An observer must see zero imported rows and the original checkpoint, one. This checks that the progress marker did not get ahead of the data it describes.

The complete Rust source is sqlite_import_turns.rs, using rusqlite 0.41.0 and its pinned bundled SQLite dependency. The existing WAL investigation explains the writer behavior behind the busy result.

Commit the batch with its checkpoint

The comparison imports rows one through four in two batches. Parsing is represented by known small integer inputs. In a real importer, read and validate a bounded amount before taking the write transaction.

let tx = importer.transaction_with_behavior(TransactionBehavior::Immediate)?;
        for id in first..=last {
            tx.execute("INSERT INTO imported VALUES (?1)", [id])?;
        }
        tx.execute(
            "UPDATE progress SET next_id = ?1 WHERE input_id = 'input-v1'",
            [last + 1],
        )?;
        tx.commit()?;
        foreground.execute("UPDATE settings SET value = 'saved' WHERE id = 1", [])?;

After each commit, the foreground connection updates its settings row successfully. The order is deliberate: this fixture gives the save a turn between the two import transactions. It does not establish that SQLite automatically schedules waiting writers fairly.

After the first batch, the importer connection closes and reopens the file. The stored next ID must be three. The second batch continues from that boundary, and the final observer must see rows one, two, three, and four exactly once, with checkpoint five.

This is a connection-reopen recovery check. It is not a process-kill, disk-full, or power-loss test. Those failures need additional controlled scenarios and the application's chosen SQLite durability settings.

Give the checkpoint an input identity

The marker is stored under input-v1. Its meaning is limited to this fixed ordered input. A file path alone is not a stable identity when the file can change between attempts.

A real job should record an input content identity or immutable source version, parser version, and the position that can safely be resumed. For a provider stream without stable offsets, use record identities and an explicit duplicate policy instead of pretending an integer byte position is enough.

Commit the marker only with the corresponding data. Writing progress first can skip rows after a restart. Writing data and progress in separate commits can repeat rows. A unique source identity can help detect repeats, but the importer still needs a decision for changed input under the same identity.

Rows also have different sizes. A hundred short codes and a hundred large documents can have very different parsing and memory costs. Bound both rows and bytes where appropriate. Do not hold the write transaction open while downloading the next page or waiting for user input.

Choose how foreground work gets a turn

A bounded busy timeout can make a save wait briefly for a writer. It is not a fairness guarantee. An importer that immediately starts another transaction may still make an interactive application unpleasant.

One option is a database worker that schedules bounded commands from foreground and background queues. Define its turn policy. Another is an explicit pause or admission rule around import batches. The right choice depends on whether the product can pause the import and what latency its save path promises.

Measure the actual foreground path before choosing batch limits. This fixture makes no throughput or latency comparison. It checks a held transaction, a specific busy outcome, rollback, successful saves between commits, and a resumed checkpoint. Those facts are useful starting points for a later measured workload.

SQLite's WAL documentation describes the concurrency model and its limits. More connections do not create concurrent writers. Keep the distinction clear when reading a connection-pool graph.

Chunking changes atomicity. If an import must become visible all at once, progressively committing business rows may violate the requirement. Consider staging data and a deliberate final visibility change, then check the lock and space cost of that operation. A smooth progress bar is not a reason to quietly weaken the data contract.

A useful importer tells the truth about completed work and leaves room for the rest of the application. Check both properties together: stored progress should support recovery, and normal saves should have a defined way to proceed.