SQLite Concurrency Problems: WAL Mode and Write Contention Resolution

Introduction

SQLite is often dismissed as "just a file database," but it is remarkably capable for single-server applications. I use it for the local state store in my trading system — holding order book snapshots, strategy parameters, and backtesting results. The performance is excellent for reads, but I hit concurrency issues when multiple processes tried to write simultaneously.

The symptoms were SQLITE_BUSY errors and occasional database is locked exceptions. These happened when the backtesting engine and the live trading engine tried to write to the same database at the same time. Here is how I resolved the concurrency problems.

Environment

• SQLite version: 3.44.0
• Operating system: Ubuntu 22.04
• Access pattern: 2 writer processes, 5 reader processes
• Journal mode: Default (DELETE)
• Database size: ~500MB
• Write frequency: ~100 writes/second during backtesting

Problem

The backtesting engine writes results to the database in bulk:

import sqlite3

def store_backtest_results(results):
    conn = sqlite3.connect("/data/trading.db")
    cursor = conn.cursor()
    for result in results:
        cursor.execute(
            "INSERT INTO backtest_results (strategy, symbol, date, pnl) VALUES (?, ?, ?, ?)",
            (result.strategy, result.symbol, result.date, result.pnl)
        )
    conn.commit()
    conn.close()

Simultaneously, the live trading engine updates order status:

def update_order_status(order_id, status):
    conn = sqlite3.connect("/data/trading.db")
    cursor = conn.cursor()
    cursor.execute(
        "UPDATE orders SET status = ?, updated_at = datetime('now') WHERE id = ?",
        (status, order_id)
    )
    conn.commit()
    conn.close()

Under concurrent access, both processes would intermittently fail:

2024-07-01 10:15:23 ERROR: sqlite3.OperationalError: database is locked
2024-07-01 10:15:23 ERROR: sqlite3.OperationalError: unable to open database file

The database is locked error occurs when one process holds a write lock and another process tries to write. With the default DELETE journal mode, writes are serialized, and SQLite raises an error rather than waiting.

Analysis

I checked the default journal mode:

conn = sqlite3.connect("/data/trading.db")
print(conn.execute("PRAGMA journal_mode;").fetchone())
# ('delete',)

The DELETE journal mode is SQLite's default. In this mode, when a transaction starts, SQLite creates a rollback journal file. Only one writer can hold the journal at a time. If a second writer tries to begin a transaction while the first is active, it gets SQLITE_BUSY.

The issue was compounded by the lack of a busy timeout. Without a timeout, SQLite immediately returns SQLITE_BUSY instead of waiting for the lock to be released.

I also checked the number of connections:

import os
import sqlite3

# Check file locks
db_path = "/data/trading.db"
stat = os.stat(db_path)
print(f"File size: {stat.st_size} bytes")

# Check for WAL files
for f in os.listdir("/data"):
    if "trading.db" in f:
        print(f"  {f}: {os.path.getsize(os.path.join('/data', f))} bytes")

The output showed no WAL files, confirming the default DELETE journal mode was in use.

Solution

I implemented a multi-part solution:

1. Enable WAL journal mode:

import sqlite3

def get_connection(db_path):
    conn = sqlite3.connect(db_path, timeout=30)
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA busy_timeout=5000")
    conn.execute("PRAGMA synchronous=NORMAL")
    conn.execute("PRAGMA cache_size=-64000")  # 64MB cache
    return conn

WAL (Write-Ahead Logging) mode allows multiple readers and one writer to operate concurrently without blocking each other. Writes go to a separate WAL file, and readers access the original database file.

2. Set a busy timeout:

# Wait up to 5 seconds for a lock before raising SQLITE_BUSY
conn.execute("PRAGMA busy_timeout=5000")

The busy timeout tells SQLite to retry for up to 5 seconds when a lock is held by another process, instead of immediately failing.

3. Restructure the write pattern for bulk operations:

def store_backtest_results(results):
    conn = get_connection("/data/trading.db")
    cursor = conn.cursor()

    # Use a single transaction for all inserts
    cursor.execute("BEGIN")
    cursor.executemany(
        "INSERT OR IGNORE INTO backtest_results (strategy, symbol, date, pnl) VALUES (?, ?, ?, ?)",
        [(r.strategy, r.symbol, r.date, r.pnl) for r in results]
    )
    cursor.execute("COMMIT")
    conn.close()

Using executemany with a single transaction is much faster and holds the lock for a shorter time than individual inserts.

4. Use WAL checkpointing to prevent WAL growth:

def checkpoint_wal(db_path):
    """Force a WAL checkpoint to merge WAL data into the main database."""
    conn = sqlite3.connect(db_path)
    conn.execute("PRAGMA wal_checkpoint(TRUNCATE)")
    conn.close()

In WAL mode, the WAL file grows until it is checkpointed. Without regular checkpointing, the WAL file can grow large and slow down reads. I added a periodic checkpoint:

import threading
import time

def wal_checkpoint_daemon(db_path, interval=300):
    """Periodically checkpoint the WAL file."""
    while True:
        time.sleep(interval)
        try:
            conn = sqlite3.connect(db_path)
            conn.execute("PRAGMA wal_checkpoint(PASSIVE)")
            conn.close()
        except Exception as e:
            print(f"Checkpoint error: {e}")

# Start the checkpoint daemon
threading.Thread(
    target=wal_checkpoint_daemon,
    args=("/data/trading.db",),
    daemon=True
).start()

5. Verify WAL mode is active:

conn = sqlite3.connect("/data/trading.db")
result = conn.execute("PRAGMA journal_mode;").fetchone()
print(f"Journal mode: {result[0]}")
# Journal mode: wal

# Check WAL file size
for f in os.listdir("/data"):
    if "trading.db-wal" in f:
        print(f"WAL file: {os.path.getsize(os.path.join('/data', f))} bytes")

After implementing all changes, I ran a concurrent stress test:

import multiprocessing
import sqlite3
import time

def writer(process_id, iterations):
    conn = get_connection("/data/trading.db")
    for i in range(iterations):
        conn.execute(
            "INSERT INTO test_data (process_id, value) VALUES (?, ?)",
            (process_id, f"value_{i}")
        )
        conn.commit()
    conn.close()

def reader(process_id, iterations):
    conn = get_connection("/data/trading.db")
    for i in range(iterations):
        conn.execute("SELECT count(*) FROM test_data").fetchone()
    conn.close()

# Run 4 writers and 4 readers concurrently
processes = []
for i in range(4):
    processes.append(multiprocessing.Process(target=writer, args=(i, 1000)))
    processes.append(multiprocessing.Process(target=reader, args=(i+4, 1000)))

start = time.time()
for p in processes:
    p.start()
for p in processes:
    p.join()
elapsed = time.time() - start

print(f"Completed in {elapsed:.2f}s with no SQLITE_BUSY errors")
# Completed in 3.45s with no SQLITE_BUSY errors

Zero SQLITE_BUSY errors with 8 concurrent processes.

Lessons Learned

1. WAL mode is essential for concurrency — WAL mode allows concurrent readers and a writer without blocking. It should be the default for any application with concurrent access.
2. Always set a busy timeout — Without a timeout, SQLite fails immediately when a lock is held. Set busy_timeout to at least 5000ms for production workloads.
3. Batch writes in transactions — Individual commits are expensive and hold locks longer. Batch operations in a single transaction.
4. Monitor WAL file size — In WAL mode, the WAL file grows until checkpointed. Use periodic PASSIVE checkpoints to keep it manageable.
5. SQLite is not a network database — Concurrency works well for processes on the same machine. For multi-server access, use PostgreSQL or MySQL instead.

This blog does not accept any external sponsorships, affiliate marketing, or ad revenue.