SQLite Concurrency and Production Deployment

SQLite is the most widely deployed database in the world: it runs inside every phone, browser, and countless desktop applications, and increasingly behind production web applications, edge services, and single-server products. It is a library, not a server — the database is a file, transactions are fully ACID, and there is nothing to operate. For many workloads it is faster and simpler than a client–server database.

Its trade-offs are specific and manageable once understood. SQLite allows many readers but only one writer at a time, so configuration — journal mode, busy timeouts, transaction types — determines whether concurrent access works smoothly or fails with "database is locked". Backups must be taken safely while the database is in use, migrations work within SQLite's ALTER TABLE limits, and the file must live on local storage. This page covers the settings and practices that make SQLite dependable in production.

TL;DR

• SQLite allows one writer at a time; readers can proceed concurrently in WAL mode.
• Enable journal_mode=WAL, set a busy_timeout, and start write transactions with BEGIN IMMEDIATE.
• Choose durability with synchronous (NORMAL is common with WAL; FULL for maximum durability).
• Turn on foreign_keys (off by default) and consider STRICT tables for type enforcement.
• Track schema versions with PRAGMA user_version; some changes require the table rebuild procedure.
• Back up with the online backup API or VACUUM INTO, replicate continuously with tools such as Litestream, and keep the file on local disk.

Quick Example

A connection setup, an explicit write transaction, versioned migrations, and an online backup in Python:

Core Concepts

Locking and Concurrency

SQLite locks the whole database for writes. Any number of connections can read, but only one can write at a time; others wait or receive SQLITE_BUSY ("database is locked"). This is rarely a bottleneck when transactions are short — SQLite can commit thousands of small transactions per second on SSDs — but long write transactions block all other writers.

Journal Modes

In WAL mode, changes accumulate in a -wal file and are copied back to the main database during checkpoints. Long-running read transactions prevent checkpoints from completing, letting the WAL grow; keep reads short and monitor WAL size.

Avoiding "Database Is Locked"

• Set busy_timeout so connections wait for locks instead of failing immediately.
• Start write transactions with BEGIN IMMEDIATE: a deferred transaction that reads first and then tries to write can fail when it needs to upgrade its lock while another writer is active.
• Keep write transactions short; never wait on network calls inside one.
• In web applications, use one writer connection (or a small pool) and separate readers, and avoid sharing connections across threads unsafely.

See Database Transactions.

Durability Settings

synchronous=FULL syncs on every commit; in WAL mode, synchronous=NORMAL is commonly used — commits are safe against application crashes, and the most recent transactions could be lost only on power failure or OS crash. Choose based on what the data is worth.

Schema and Integrity

• Foreign keys are not enforced unless PRAGMA foreign_keys=ON is set on every connection.
• STRICT tables enforce column types instead of SQLite's flexible type affinity.
• PRAGMA user_version stores an integer for migration tracking.
• ALTER TABLE supports adding and renaming columns and, in recent versions, dropping columns; other changes use the documented table rebuild: create a new table, copy data, drop the old, rename, recreate indexes and triggers — inside a transaction with foreign keys temporarily disabled. See Database Migrations.
• Run PRAGMA integrity_check (or quick_check) periodically.

Backups and Replication

Never back up by copying the database file while it is being written — especially in WAL mode, where the -wal file holds recent commits. Test restores regularly. See Database Backups.

Where SQLite Fits

Never place a live SQLite database on a network file system (NFS, SMB): file locking is often unreliable there and corruption can result. See Local-First Software and Mobile App Architecture.

Best Practices

Configure Every Connection

Apply WAL mode, busy timeout, foreign keys, and synchronous settings consistently in one connection factory.

Use BEGIN IMMEDIATE for Writes

Acquire the write lock up front to avoid lock-upgrade failures under concurrency.

Keep Transactions Short

Do slow work outside transactions and batch many small writes into one transaction when possible.

Back Up Online and Test Restores

Use the backup API, VACUUM INTO, or continuous replication, and verify restores.

Common Mistakes

Default Settings Under Concurrency

Copying the Live File

Assuming Foreign Keys Are Enforced

FAQ

Can SQLite handle concurrent users?

Yes for reads, and for writes when transactions are short: SQLite serializes writers, and in WAL mode readers continue during writes. Workloads with heavy, sustained concurrent writes across many processes are a poor fit.

What is WAL mode in SQLite?

Write-ahead logging, a journal mode where changes are appended to a separate log file so readers can continue reading a consistent snapshot while a writer commits.

Why do I get "database is locked" errors?

Another connection holds a lock and your connection did not wait, or a deferred transaction tried to upgrade to a write lock while another writer was active. Use a busy timeout, WAL mode, short transactions, and BEGIN IMMEDIATE.

How do I back up a SQLite database safely?

Use the online backup API, the CLI's .backup command, VACUUM INTO, or a continuous replication tool such as Litestream — not a raw file copy of a database in use.

Is SQLite suitable for production web applications?

For many single-server applications, yes, with proper configuration, backups, and an understanding of its single-writer model. Applications needing many servers writing to one database usually need a client–server database or a replicated SQLite variant.

Related Topics

• SQLite — The embedded SQL database
• Database Transactions — Isolation and locking
• Database Backups — Protecting data
• Database Migrations — Evolving schemas safely
• Local-First Software — Local databases that sync
• Mobile App Architecture — On-device persistence

References

• Write-Ahead Logging (SQLite documentation)
• Appropriate Uses For SQLite (SQLite documentation)
• SQLite Backup API (SQLite documentation)
• STRICT Tables (SQLite documentation)
• Litestream