Software EngineeringDatabases

Serializing SQLite Writes in a Parallel Rust Test Suite

How a Rust SSO test suite exposed SQLite write contention, why timeouts and raw transaction SQL did not solve it, and how one writer connection stabilized eight parallel workers.

Diagram of many waiting connections converging on a database with separate reader and writer paths
Lead image Diagram of many waiting connections converging on a database with separate reader and writer paths
On this page

Audio edition

0:00 / 4:33

Ready

We moved our SSO integration tests to SQLite because a local test run should not require a PostgreSQL service. The first version kept the PostgreSQL-era pool configuration: up to 100 connections, with the tests running across eight workers.

The suite began failing with 500 Internal Server Error responses and Database busy messages. The failures looked intermittent, but the underlying mismatch was straightforward: we had changed the database without changing our model of write concurrency.

SQLite permits concurrent read transactions across connections, but only one write transaction at a time. That limit still applies in write-ahead logging mode, even though WAL allows readers and a writer to make progress together. The distinction matters: SQLite supports concurrency, just not concurrent writers to the same database file. The SQLite transaction documentation states that boundary directly.

Several test workers contending for SQLite’s single writer

Waiting did not remove the contention

Our first response was to wait longer. We set busy_timeout to 30 seconds, then added application-level retries with exponential backoff around the transaction logic.

That reduced some immediate failures, but it made the test suite slower and less predictable. Requests that normally completed in about 50 milliseconds could take as long as 15 seconds. We still had several workers competing for one write slot; we had only changed how long they waited and how often they tried again.

This is the narrow job of a busy timeout: when a connection encounters a lock, its busy handler waits up to the configured duration before SQLite returns SQLITE_BUSY. It is useful for brief contention, but it does not create more write capacity. SQLite’s busy-timeout documentation makes that behavior explicit.

Retries adding another queue in front of the same write lock

BEGIN IMMEDIATE still needs one connection

The next attempt addressed when SQLite acquires the write transaction. A normal BEGIN is deferred: the transaction starts with the first database access and is upgraded when a write occurs. BEGIN IMMEDIATE starts a write transaction at once, so lock contention appears at the beginning rather than halfway through the work. It can still return SQLITE_BUSY when another writer is active.

Using BEGIN IMMEDIATE was reasonable. Sending the statements independently through a connection pool was not:

db.execute("BEGIN IMMEDIATE").await?;
db.query("...").await?;
db.execute("COMMIT").await?;

Those calls did not carry a transaction handle. The pool could choose a different connection for a later statement, so BEGIN, the queries, and COMMIT were not guaranteed to share transaction state. We saw errors including cannot start a transaction within a transaction.

The important rule was not “use raw transaction SQL.” It was “keep every operation in a transaction on the connection that began it.” Our ORM’s transaction handle already provides that ownership.

A separate admission path for writes

We changed the SQLite configuration to expose two paths:

  • The ordinary pool serves reads, where multiple connections are useful.
  • A writer pool, configured with max_connections = 1, serves transactions that modify the database.

The SQLite branch of our transaction helper now begins and completes each write through that writer pool. In simplified form:

#[cfg(feature = "db_sqlite")]
{
    let txn = db_writer.begin().await?;

    apply_changes(&txn).await?;

    txn.commit().await?;
}

All write operations must use txn; sending one of them back through the general pool would reintroduce the same ownership problem. The one-connection writer pool also acts as an application-side admission queue. Instead of letting eight test workers race for SQLite’s write lock, it admits their write transactions one at a time.

Reader connections beside a single application-managed writer path

This serialization is local to our test process. If several processes open the same SQLite file, each process can still present its own writer, so an application must remain prepared for SQLITE_BUSY. The change solved the contention pattern we had measured; it is not a system-wide lock service.

What changed in the test suite

After routing SQLite writes through the single-connection writer pool, the integration suite passed consistently with eight workers and the long timeout failures disappeared. We kept read concurrency while making write serialization explicit.

That result is deliberately narrow. It does not show that SQLite writes scale with a larger pool, nor does it establish that the same configuration suits a production workload. It shows that, for this test suite, pool size was not write capacity. Once the application admitted writes at the rate SQLite could execute them, the intermittent failures stopped.

Continue reading

Complete index →