Concurrency: locks, isolation levels, phantom reads

Understand SQLite's locking model, its transactions, and the isolation anomalies described by the SQL standard.

12 min · 3 questions

SQLite locks at the level of the entire database file, not the row. In the default mode (rollback journal), readers share a SHARED lock, but only one writer at a time can obtain the EXCLUSIVE lock. Consequence: writes are serialized. Let's create a table to work with transactions.

SQL

CREATE TABLE accounts (
  id INTEGER PRIMARY KEY,
  balance INTEGER NOT NULL
);

INSERT INTO accounts (id, balance)
VALUES (1, 100), (2, 50);

-- Read: deferred transaction (default)
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
COMMIT;

An explicit transaction wrapped by BEGIN/COMMIT.

The lock evolves in stages: UNLOCKED, then SHARED (read), RESERVED (intent to write), PENDING, and finally EXCLUSIVE (write). If a transaction already holds the write lock, another receives the SQLITE_BUSY error. Rather than failing immediately, set a wait delay with busy_timeout, and enable WAL mode to let readers and the writer progress in parallel.

SQL

-- A single writer, concurrent readers
PRAGMA journal_mode = WAL;

-- Wait up to 5 s if the database is busy
PRAGMA busy_timeout = 5000;

WAL unblocks concurrent reads; busy_timeout waits.

The SQL standard describes three anomalies: the dirty read (reading uncommitted data), the non-repeatable read (re-reading a row and seeing a different value), and the phantom read (replaying a range query and seeing new rows appear). Because SQLite serializes writers, it effectively behaves as SERIALIZABLE: these anomalies don't appear (outside shared-cache mode with read_uncommitted). Elsewhere, you choose the level explicitly.

SQL

-- Atomic transfer: immediate write lock
BEGIN IMMEDIATE;
UPDATE accounts
SET balance = balance - 10
WHERE id = 1;
UPDATE accounts
SET balance = balance + 10
WHERE id = 2;
COMMIT;

BEGIN IMMEDIATE reserves the lock before any write.

A transaction opened by BEGIN alone is deferred: it first takes a read lock, then tries to promote it to a write lock at the first UPDATE. If another transaction writes in the meantime, the promotion fails with SQLITE_BUSY and you lose the work already read. For any transaction meant to write, open it with BEGIN IMMEDIATE to take the lock right away.

Other engines expose explicit levels. PostgreSQL uses MVCC and starts at READ COMMITTED, but accepts SET TRANSACTION ISOLATION LEVEL SERIALIZABLE. MySQL/InnoDB is at REPEATABLE READ by default, and its *gap locks* neutralize phantom reads. Remember: the same SQL code can behave differently depending on the configured isolation.

Knowledge check

Make sure you remember the key points of this lesson.

Which isolation level does SQLite offer in practice?
- READ UNCOMMITTED, dirty reads are possible by default
- READ COMMITTED, like PostgreSQL
- SERIALIZABLE, because a single writer acts at a time
- None, there are no transactions
What is the difference between BEGIN and BEGIN IMMEDIATE?
- BEGIN IMMEDIATE takes the write lock right away; BEGIN waits for the first write access
- BEGIN IMMEDIATE disables the rollback journal
- BEGIN IMMEDIATE creates a new database
- There is no difference in behavior
What is a phantom read?
- Reading a value that another transaction hasn't committed yet
- Re-reading the same row and getting a different value
- Replaying a range query and seeing new rows inserted in the meantime appear
- Reading a row that was just deleted