Limits and Gotchas

One writer at a time

This is the defining constraint of SQLite, and every concurrency question reduces to it. There is a single write lock per database file. Readers are unlimited.

PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;

With those two settings a read-heavy application scales to as many concurrent readers as the machine allows, and writes serialize. That is enough for a great many real workloads, because a small write transaction on an SSD takes well under a millisecond.

What it is not enough for: many independent processes writing continuously. Two writers racing gives the second one this, immediately, with no busy_timeout:

Error: database is locked

The architectural fix is to funnel writes through one writer. A single process, or a single connection behind a queue, removes contention entirely and is the standard shape for a SQLite-backed server.

SQLITE_BUSY, and how to actually handle it

SQLITE_BUSY is not an error in your SQL. It means another connection holds a lock you needed.

PRAGMA busy_timeout = 5000;    -- retry internally for up to 5 seconds
BEGIN IMMEDIATE;               -- take the write lock now, not later
  UPDATE accounts SET balance = balance - 50 WHERE id = 1;
  UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;

The deferred-upgrade case is the one that surprises people. BEGIN takes only a read lock, and if another connection writes before your first UPDATE, the upgrade fails and cannot wait, because waiting would deadlock two readers each trying to upgrade.

Error: database is locked (SQLITE_BUSY_SNAPSHOT)

Every transaction that will write should start with BEGIN IMMEDIATE. It takes the write lock up front, where busy_timeout applies, so contention becomes a wait instead of an error. This is the single highest-value habit for SQLite concurrency.

Still retry at the application level with a small backoff, since a timeout can expire. And never hold a transaction open across network I/O or user think time.

Hard limits

sqlite3 :memory: ".limits"                    # every limit for this binary
sqlite3 :memory: ".limits variable_number"
sqlite3 :memory: ".limits length attached"

PRAGMA page_size;        -- 4096
PRAGMA max_page_count;   -- 1073741823
PRAGMA page_count;       -- times page_size is the current file size

The 64-table join limit and the 1 GB blob limit are the two you can realistically hit. A 1 GB value also means the whole thing is read into memory, so store large files on disk and keep a path in the database.

SQLITE_MAX_VARIABLE_NUMBER, the one that varies

The number of ? parameters allowed in one statement is a compile-time value, and different builds pick very different numbers.

sqlite3 :memory: ".limits variable_number"
#      variable_number 32766

Error: too many SQL variables

Never assume a number, and never generate a query with one ? per row. A batch insert of 1000 rows with 10 columns needs 10000 parameters, which fails on any pre-3.32 build and on a conservatively compiled one. Two portable answers.

-- 1. For a long IN list, bind one JSON array instead of N parameters
SELECT * FROM users WHERE id IN (SELECT value FROM json_each(?));

-- 2. For a bulk insert, chunk it and reuse one prepared statement in a transaction
BEGIN IMMEDIATE;
  -- execute the same 'INSERT INTO t(a,b) VALUES(?,?)' N times
COMMIT;

The second form is faster than a giant multi-row VALUES anyway, because the statement is prepared once and the transaction pays one fsync for the whole batch.

Dynamic typing surprises

The affinity system means a column's declared type is a preference, not a rule, unless the table is STRICT.

SELECT 42 = '42';                  -- 0, an integer never equals a text
SELECT '42' < 5;                   -- 0, every text sorts after every number
SELECT CAST('abc' AS INTEGER);     -- 0, not an error
SELECT 1/0;                        -- (NULL), not an error
SELECT 7/2;                        -- 3, integer division
SELECT 0.1 + 0.2 = 0.3;            -- 0
SELECT typeof(x) FROM t;           -- audit what is actually stored
CREATE TABLE u(name VARCHAR(3));
INSERT INTO u VALUES('much longer than three');   -- accepted, no truncation

The full list of what does not raise where you might expect it to:

Defenses: declare every new table STRICT, add CHECK constraints, use NOT NULL liberally, and read a pragma back after setting it.

Missing SQL features

SELECT sqlite_version();       -- 3.51.0, check before using anything above
SELECT sqlite_source_id();
PRAGMA compile_options;        -- what this build actually enabled

Print the version before relying on a feature. Libraries embed their own SQLite, so Python's sqlite3, a Node driver, and the sqlite3 CLI on the same machine routinely report three different versions.

AUTOINCREMENT and rowid reuse

Without AUTOINCREMENT, a rowid is max(rowid) + 1, so deleting the highest row makes the next insert reuse its number.

CREATE TABLE a(id INTEGER PRIMARY KEY, v TEXT);
INSERT INTO a(v) VALUES('x'),('y');
DELETE FROM a WHERE id = 2;
INSERT INTO a(v) VALUES('z');
SELECT id, v FROM a;      -- 1|x and 2|z   <- id 2 came back

That matters when an id has leaked outside the database, into a URL, a cache key, an external system, or an audit log. A reused id makes a stale reference point at the wrong row rather than at nothing.

CREATE TABLE b(id INTEGER PRIMARY KEY AUTOINCREMENT, v TEXT);
-- ids are never reused, at the cost of a sqlite_sequence write per insert
SELECT * FROM sqlite_sequence;

Related surprises: VACUUM renumbers rowids in a table with no INTEGER PRIMARY KEY, so always declare one. last_insert_rowid() is per connection, is not reset by a rollback, and is not updated by an INSERT OR IGNORE that skipped. And with AUTOINCREMENT, hitting 9223372036854775807 gives SQLITE_FULL rather than wrapping.

Where SQLite is the wrong tool

Be honest about these, because working around them costs more than choosing another database.

- Many concurrent writers. One write lock. A queue in front of a single writer fixes it inside one host, and nothing fixes it across hosts. - Network filesystems. NFS, SMB, and most cluster filesystems implement locking incompletely, and WAL needs real shared memory. The official documentation warns against it, and corruption from this is common. Keep the file on local disk. - Multiple machines sharing one file. There is no coordination protocol. Use replication (Litestream, LiteFS, rqlite, Turso) or a client-server database. - Very large single values. The 1 GB blob limit, and a blob is read fully into memory. - Fine-grained access control. There are no users, roles, or GRANT. Whoever can read the file can read everything. - Heavy analytics over hundreds of gigabytes. It works, but a columnar engine like DuckDB is built for it. - Also: no built-in replication, no failover, no wire protocol, and no connection pooling in the usual sense, since a "connection" is a file handle in your own process.

Where it genuinely shines

The claim SQLite makes about itself is worth taking seriously: it does not compete with client-server databases, it competes with fopen(). When the alternative is a bespoke file format, a directory of JSON, or a pile of CSVs, it wins on every axis.

The one-page checklist

-- On every connection, before any transaction
PRAGMA journal_mode = WAL;      -- persistent, a no-op after the first time
PRAGMA synchronous = NORMAL;    -- safe with WAL
PRAGMA foreign_keys = ON;       -- OFF by default, per connection, every time
PRAGMA busy_timeout = 5000;     -- turn contention into a wait
PRAGMA cache_size = -64000;     -- 64 MiB, and it is per connection

-- On every table
CREATE TABLE t(id INTEGER PRIMARY KEY, ...) STRICT;

-- On every writing transaction
BEGIN IMMEDIATE; ... COMMIT;

-- On a schedule
PRAGMA optimize;                -- before closing a long-lived connection
VACUUM INTO '/backups/app.db';  -- the backup, never cp
PRAGMA integrity_check;         -- on the backup, to verify it

The five mistakes that account for most SQLite pain, in order: forgetting PRAGMA foreign_keys = ON, leaving the default rollback journal instead of WAL, using BEGIN where BEGIN IMMEDIATE was needed, cping a live database as a backup, and trusting a declared column type on a table that is not STRICT.