{"id":354,"date":"2026-10-27T15:50:00","date_gmt":"2026-10-27T15:50:00","guid":{"rendered":"https:\/\/backendside.com\/blog\/?p=354"},"modified":"2026-11-21T11:25:01","modified_gmt":"2026-11-21T11:25:01","slug":"sqlite-wal-mode-locking-concurrency","status":"publish","type":"post","link":"https:\/\/backendside.com\/blog\/2026\/10\/27\/sqlite-wal-mode-locking-concurrency\/","title":{"rendered":"WAL Mode, Locking and Concurrency in SQLite"},"content":{"rendered":"<p class=\"lead\">Sooner or later every SQLite user meets the same error: <code>database is locked<\/code>. It usually appears the first time a second thread, process or tool touches the database while something else is writing. The fix is rarely &ldquo;use a different database&rdquo; &mdash; it is understanding how SQLite coordinates access to a single file. This tutorial walks through SQLite&rsquo;s locking model, the journal modes, what <strong>Write-Ahead Logging (WAL)<\/strong> changes, how checkpoints work, and the settings that make concurrent apps run smoothly.<\/p>\n<h2>The ground rule: many readers, one writer<\/h2>\n<p>SQLite lets any number of connections read at the same time, but only <strong>one connection can write at a time<\/strong>. There is no row-level or table-level locking; locks apply to the whole database file. Everything else in this article is about how SQLite enforces that rule, and how WAL mode lets readers and the writer stop getting in each other&rsquo;s way.<\/p>\n<h2>Lock states in rollback-journal mode<\/h2>\n<p>In the traditional (default) mode, a connection moves through a series of file locks as it reads and writes:<\/p>\n<table>\n<thead>\n<tr>\n<th>Lock<\/th>\n<th>Meaning<\/th>\n<th>Who else can proceed<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>UNLOCKED<\/td>\n<td>No access to the file<\/td>\n<td>Everyone<\/td>\n<\/tr>\n<tr>\n<td>SHARED<\/td>\n<td>Reading<\/td>\n<td>Other readers; nobody may write to the file<\/td>\n<\/tr>\n<tr>\n<td>RESERVED<\/td>\n<td>Planning to write; changes held in memory<\/td>\n<td>Existing and new readers; no other writer<\/td>\n<\/tr>\n<tr>\n<td>PENDING<\/td>\n<td>Ready to write, waiting for readers to finish<\/td>\n<td>Existing readers finish; no new readers<\/td>\n<\/tr>\n<tr>\n<td>EXCLUSIVE<\/td>\n<td>Writing changes to the database file<\/td>\n<td>Nobody<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>The key consequence: at commit time the writer needs EXCLUSIVE, which means <strong>readers block the writer, and the writer briefly blocks readers<\/strong>. With one app doing occasional writes you will never notice. With a background job writing while a UI reads, you will.<\/p>\n<h2>Journal modes<\/h2>\n<p>To make commits atomic, SQLite records how to undo or redo changes in a journal. The <code>journal_mode<\/code> PRAGMA selects how:<\/p>\n<table>\n<thead>\n<tr>\n<th>Mode<\/th>\n<th>What it does<\/th>\n<th>Use it when<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td><code>DELETE<\/code><\/td>\n<td>Default. Writes a rollback journal, deletes it at commit<\/td>\n<td>Simple single-user use<\/td>\n<\/tr>\n<tr>\n<td><code>TRUNCATE<\/code><\/td>\n<td>Like DELETE, but truncates the journal instead of deleting it<\/td>\n<td>File systems where delete is slow<\/td>\n<\/tr>\n<tr>\n<td><code>PERSIST<\/code><\/td>\n<td>Keeps the journal file, zeroes its header<\/td>\n<td>Same idea, avoids re-creating the file<\/td>\n<\/tr>\n<tr>\n<td><code>MEMORY<\/code><\/td>\n<td>Keeps the journal in RAM<\/td>\n<td>Speed over safety; a crash can corrupt<\/td>\n<\/tr>\n<tr>\n<td><code>WAL<\/code><\/td>\n<td>Appends changes to a separate write-ahead log<\/td>\n<td>Concurrent readers and a writer; most apps<\/td>\n<\/tr>\n<tr>\n<td><code>OFF<\/code><\/td>\n<td>No journal at all<\/td>\n<td>Throwaway data only &mdash; no safe rollback<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h2>How WAL mode works<\/h2>\n<p>WAL flips the model around. Instead of changing the database file and keeping an undo copy, the writer <strong>appends new versions of changed pages to a separate <code>-wal<\/code> file<\/strong>, and the main database file stays untouched until later. A small shared-memory index, the <code>-shm<\/code> file, tells each reader where the newest version of each page lives.<\/p>\n<p>When a read transaction starts, it notes how far the WAL goes at that moment and sees a consistent snapshot as of that point, even while the writer keeps appending. The result:<\/p>\n<ul>\n<li><strong>Readers do not block the writer, and the writer does not block readers.<\/strong><\/li>\n<li>There is still only <strong>one writer at a time<\/strong>.<\/li>\n<li>Commits are usually faster, because appending to one file needs fewer disk syncs.<\/li>\n<\/ul>\n<p>Turning it on is one statement, and the setting is stored in the database file, so it persists for every future connection:<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>PRAGMA journal_mode = WAL;     -- returns 'wal' on success\nPRAGMA journal_mode;           -- check the current mode\n\n-- A common pairing for WAL: still crash-safe (no corruption),\n-- but the last commits may roll back after a power loss\nPRAGMA synchronous = NORMAL;<\/code><\/pre>\n<div style=\"border:1px solid #c5d3f8;background:linear-gradient(135deg,#eef2fd 0%,#ffffff 72%);border-radius:14px;padding:1.5rem 1.65rem;margin:2rem 0;\">\n<div style=\"font-size:.7rem;font-weight:700;letter-spacing:.08em;text-transform:uppercase;color:#2d5be3;margin-bottom:.55rem;\">&#128295; BackendSide Tool<\/div>\n<h4 style=\"margin:0 0 .45rem;font-size:1.15rem;color:#1a1916;font-weight:700;\">SQLiteDesk &mdash; Check and Tune Your Database Settings<\/h4>\n<p style=\"margin:0 0 1.05rem;color:#3d3c38;font-size:.92rem;line-height:1.65;\">Want to see which journal mode a database is using, or try a checkpoint? Run the PRAGMAs above in the <strong>SQLiteDesk<\/strong> SQL workspace &mdash; syntax highlighting, schema-aware autocomplete, run the whole script or just the selection with F5, and cancel a long-running query with Stop. A &ldquo;Run against&rdquo; picker keeps each editor&rsquo;s target database explicit, so you always know which file you are changing.<\/p>\n<p>  <a href=\"https:\/\/backendside.com\/sqlitedesk.php\" style=\"display:inline-flex;align-items:center;gap:.4rem;background:#2d5be3;color:#ffffff;font-weight:600;font-size:.85rem;padding:.6rem 1.2rem;border-radius:6px;text-decoration:none;\">Explore SQLiteDesk &rarr;<\/a>\n<\/div>\n<h2>Checkpoints: keeping the WAL file in check<\/h2>\n<p>Because changes pile up in the <code>-wal<\/code> file, they must eventually be copied back into the main database. That is a <strong>checkpoint<\/strong>. By default SQLite runs one automatically when the WAL reaches about 1,000 pages (<code>PRAGMA wal_autocheckpoint<\/code>), and normally checkpoints and removes the WAL when the last connection closes.<\/p>\n<p>You can also run one yourself, in four strengths:<\/p>\n<table>\n<thead>\n<tr>\n<th>Mode<\/th>\n<th>Behaviour<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td><code>PASSIVE<\/code><\/td>\n<td>Copies as much as it can without waiting for anyone. This is what auto-checkpoints use.<\/td>\n<\/tr>\n<tr>\n<td><code>FULL<\/code><\/td>\n<td>Blocks new writers and waits for readers on older snapshots, then copies everything.<\/td>\n<\/tr>\n<tr>\n<td><code>RESTART<\/code><\/td>\n<td>Like FULL, then waits so the next writer can start the WAL from the beginning.<\/td>\n<\/tr>\n<tr>\n<td><code>TRUNCATE<\/code><\/td>\n<td>Like RESTART, and also truncates the <code>-wal<\/code> file to zero bytes.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>PRAGMA wal_checkpoint(TRUNCATE);\n-- returns three numbers: busy, log, checkpointed\n--   busy         = 1 if it could not finish (a reader or writer was in the way)\n--   log          = pages currently in the WAL\n--   checkpointed = pages copied back into the database\n\nPRAGMA wal_autocheckpoint = 1000;   -- the default threshold, in pages<\/code><\/pre>\n<p><strong>Checkpoint starvation<\/strong> is the WAL problem to watch for. A checkpoint cannot copy pages past the point that the oldest active reader still needs. If some connection always holds a read transaction open &mdash; a forgotten cursor, a reporting query that never finishes &mdash; the WAL can grow without limit. Keep read transactions short, and if the <code>-wal<\/code> file keeps growing, look for a connection that never lets go.<\/p>\n<h2>Why you still get &ldquo;database is locked&rdquo;<\/h2>\n<p>WAL removes reader\/writer conflicts, but two writers still collide. When a connection cannot get the lock it needs, SQLite returns <code>SQLITE_BUSY<\/code>. Three habits fix nearly all of these:<\/p>\n<ol>\n<li><strong>Set a busy timeout.<\/strong> By default a blocked connection fails immediately. With a timeout it retries for up to that long first.<\/li>\n<li><strong>Start write transactions with <code>BEGIN IMMEDIATE<\/code>.<\/strong> A plain <code>BEGIN<\/code> is deferred: it starts as a reader and tries to upgrade on the first write. If another connection wrote in the meantime, the upgrade can fail with <code>SQLITE_BUSY<\/code> straight away &mdash; waiting would not help, because the snapshot is already out of date. <code>BEGIN IMMEDIATE<\/code> takes the write lock up front, so it either waits politely or gets going.<\/li>\n<li><strong>Keep write transactions short.<\/strong> Do slow work (network calls, user prompts, heavy computation) before you open the transaction.<\/li>\n<\/ol>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>PRAGMA busy_timeout = 5000;    -- wait up to 5 seconds for locks (per connection)\n\nBEGIN IMMEDIATE;               -- take the write lock now\nUPDATE accounts SET balance = balance - 50 WHERE id = 1;\nUPDATE accounts SET balance = balance + 50 WHERE id = 2;\nCOMMIT;<\/code><\/pre>\n<p>Note that <code>busy_timeout<\/code> is a per-connection setting, so set it every time you open a connection. <code>journal_mode = WAL<\/code>, by contrast, only needs to be set once.<\/p>\n<h2>WAL caveats<\/h2>\n<ul>\n<li><strong>Same machine only.<\/strong> The <code>-shm<\/code> index uses shared memory, so every process using the database must be on the same host. WAL does not work over network file systems.<\/li>\n<li><strong>Three files, not one.<\/strong> The <code>-wal<\/code> and <code>-shm<\/code> files are part of the database while it is in use. Never delete the <code>-wal<\/code> file &mdash; it may contain committed transactions that have not been checkpointed yet.<\/li>\n<li><strong>Copying is riskier.<\/strong> Copying just the <code>.db<\/code> file of a busy WAL database can miss recent commits. Use a proper online backup instead.<\/li>\n<li><strong>Attached databases.<\/strong> A transaction that spans several attached databases is atomic per database, but not across all of them as a set, in WAL mode.<\/li>\n<\/ul>\n<div style=\"border:1px solid #c5d3f8;background:linear-gradient(135deg,#eef2fd 0%,#ffffff 72%);border-radius:14px;padding:1.5rem 1.65rem;margin:2rem 0;\">\n<div style=\"font-size:.7rem;font-weight:700;letter-spacing:.08em;text-transform:uppercase;color:#2d5be3;margin-bottom:.55rem;\">&#128295; BackendSide Tool<\/div>\n<h4 style=\"margin:0 0 .45rem;font-size:1.15rem;color:#1a1916;font-weight:700;\">SQLiteDesk &mdash; Back Up a Live Database Safely<\/h4>\n<p style=\"margin:0 0 1.05rem;color:#3d3c38;font-size:.92rem;line-height:1.65;\">Copying a WAL-mode database by hand is risky. <strong>SQLiteDesk<\/strong> uses an <strong>online backup that is safe on a live database while writes are happening<\/strong>, and restores from a backup file just as easily. Backing up, restoring, encrypting and decrypting all preserve the database&rsquo;s journal mode, <code>user_version<\/code> and <code>application_id<\/code> &mdash; a WAL database comes back as a WAL database.<\/p>\n<p>  <a href=\"https:\/\/backendside.com\/sqlitedesk.php\" style=\"display:inline-flex;align-items:center;gap:.4rem;background:#2d5be3;color:#ffffff;font-weight:600;font-size:.85rem;padding:.6rem 1.2rem;border-radius:6px;text-decoration:none;\">Explore SQLiteDesk &rarr;<\/a>\n<\/div>\n<h2>A sensible default setup<\/h2>\n<p>For a typical desktop app or single-server web app with some concurrency, this combination works well:<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>-- once, when the database is created\nPRAGMA journal_mode = WAL;\n\n-- on every new connection\nPRAGMA busy_timeout = 5000;\nPRAGMA synchronous = NORMAL;   -- or FULL if every commit must survive power loss\nPRAGMA foreign_keys = ON;      -- unrelated to locking, but also per-connection<\/code><\/pre>\n<p>Then write with <code>BEGIN IMMEDIATE<\/code>, batch inserts into a single transaction, and keep read transactions short so checkpoints can do their job.<\/p>\n<div style=\"border:1px solid #c5d3f8;background:linear-gradient(135deg,#eef2fd 0%,#ffffff 72%);border-radius:14px;padding:1.5rem 1.65rem;margin:2rem 0;\">\n<div style=\"font-size:.7rem;font-weight:700;letter-spacing:.08em;text-transform:uppercase;color:#2d5be3;margin-bottom:.55rem;\">&#128295; BackendSide Tool<\/div>\n<h4 style=\"margin:0 0 .45rem;font-size:1.15rem;color:#1a1916;font-weight:700;\">SQLiteDesk &mdash; A SQLite Manager for Windows<\/h4>\n<p style=\"margin:0 0 1.05rem;color:#3d3c38;font-size:.92rem;line-height:1.65;\"><strong>SQLiteDesk<\/strong> brings browsing, editing, SQL, visual schema design, ER diagrams, import\/export, backup and maintenance (VACUUM, ANALYZE, REINDEX, integrity check) into one window &mdash; including SQLCipher-encrypted databases. Runs 100% offline, with no telemetry and no accounts. Available on the Microsoft Store with a free trial.<\/p>\n<p>  <a href=\"https:\/\/backendside.com\/sqlitedesk.php\" style=\"display:inline-flex;align-items:center;gap:.4rem;background:#2d5be3;color:#ffffff;font-weight:600;font-size:.85rem;padding:.6rem 1.2rem;border-radius:6px;text-decoration:none;\">Explore SQLiteDesk &rarr;<\/a>\n<\/div>\n<h2>Key takeaways<\/h2>\n<ul>\n<li>SQLite allows <strong>many readers but only one writer<\/strong>, and locks the whole file, not rows or tables.<\/li>\n<li>In the default rollback-journal mode readers and the writer block each other at commit; <strong>WAL mode lets them run side by side<\/strong>.<\/li>\n<li><code>PRAGMA journal_mode = WAL<\/code> is <strong>persistent<\/strong>; <code>busy_timeout<\/code> and <code>synchronous<\/code> are <strong>per connection<\/strong>.<\/li>\n<li>Checkpoints copy the WAL back into the database; <strong>long-lived readers cause checkpoint starvation<\/strong> and an ever-growing <code>-wal<\/code> file.<\/li>\n<li>Use a busy timeout, <code>BEGIN IMMEDIATE<\/code> for writes and short transactions to make &ldquo;database is locked&rdquo; rare.<\/li>\n<li>Never delete the <code>-wal<\/code> file or hand-copy a live database; use an online backup such as the one built into <strong>SQLiteDesk<\/strong>.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Why SQLite says &#8220;database is locked&#8221;, how its lock states and journal modes work, what Write-Ahead Logging changes, how checkpoints keep the WAL file in check, and the handful of settings and habits that make concurrent SQLite apps behave.<\/p>\n","protected":false},"author":1,"featured_media":409,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,8],"tags":[32,33,35],"class_list":["post-354","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sqlite","category-tutorials","tag-sqlite","tag-sqlitedesk","tag-wal-mode"],"_links":{"self":[{"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/posts\/354","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/comments?post=354"}],"version-history":[{"count":1,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/posts\/354\/revisions"}],"predecessor-version":[{"id":410,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/posts\/354\/revisions\/410"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/media\/409"}],"wp:attachment":[{"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/media?parent=354"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/categories?post=354"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/tags?post=354"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}