Resolving SQLite Locks in Large MBTiles Generation

Enable PRAGMA journal_mode=WAL, route all writes through a single dedicated thread, and batch inserts inside BEGIN IMMEDIATE transactions — that combination eliminates sqlite3.OperationalError: database is locked in every high-throughput MBTiles SQLite container pipeline.

When this applies

Apply the techniques on this page when:

• Your tile generator spawns more than one process or thread that opens the same .mbtiles file for writing simultaneously.
• You are inserting more than ~100k tiles and see database is locked or disk I/O error in worker logs.
• Tippecanoe or a custom Python writer is being called in parallel with different zoom-level ranges, all targeting the same output file.
• Build time on a large dataset exceeds expectations because lock contention is serializing workers that should run in parallel.

If you are running a single-process, single-thread Tippecanoe invocation against one output file, lock errors point instead to stale -wal/-shm sidecar files from a previous crashed run — delete them and rebuild.

Specification detail

SQLite exposes locking behaviour through journal mode and a set of PRAGMA statements. The table below lists every relevant knob, its default, the recommended production value, and the minimum SQLite version required.

Set all of these on every new connection, before any writes. They are connection-scoped (except journal_mode, which is database-scoped and persists after the first connection sets it).

Production command

The class below is a drop-in writer for any pipeline that generates tiles across multiple threads or processes. Workers call add_tile() from any thread; a dedicated internal thread owns the single SQLite connection and drains the queue in batches.

import sqlite3
import time
import threading
import queue

class MBTilesWriter:
    """Thread-safe, WAL-enabled writer for high-throughput MBTiles generation."""

    SCHEMA = """
    CREATE TABLE IF NOT EXISTS metadata (name TEXT, value TEXT);
    CREATE TABLE IF NOT EXISTS tiles (
        zoom_level  INTEGER NOT NULL,
        tile_column INTEGER NOT NULL,
        tile_row    INTEGER NOT NULL,
        tile_data   BLOB    NOT NULL,
        PRIMARY KEY (zoom_level, tile_column, tile_row)
    );
    CREATE UNIQUE INDEX IF NOT EXISTS tiles_index
        ON tiles (zoom_level, tile_column, tile_row);
    """

    def __init__(self, db_path: str, batch_size: int = 500, timeout: int = 30):
        self.db_path    = db_path
        self.batch_size = batch_size
        self.timeout    = timeout
        self._queue        = queue.Queue(maxsize=4000)
        self._stop_event   = threading.Event()
        self._error: Exception | None = None
        self._writer = threading.Thread(target=self._writer_loop, daemon=True)
        self._writer.start()

    # ------------------------------------------------------------------ public

    def initialize_schema(self, metadata: dict[str, str]):
        """Call once, from the main thread, before add_tile()."""
        conn = self._open()
        conn.executescript(self.SCHEMA)
        conn.executemany(
            "INSERT OR REPLACE INTO metadata (name, value) VALUES (?, ?)",
            list(metadata.items()),
        )
        conn.commit()
        conn.close()

    def add_tile(self, zoom: int, col: int, row: int, data: bytes):
        if self._error:
            raise self._error
        self._queue.put((zoom, col, row, data))

    def flush_and_close(self):
        self._queue.join()
        self._stop_event.set()
        self._writer.join(timeout=60)
        if self._error:
            raise self._error

    # ----------------------------------------------------------------- private

    def _open(self) -> sqlite3.Connection:
        conn = sqlite3.connect(self.db_path, timeout=self.timeout)
        conn.execute("PRAGMA journal_mode=WAL;")
        conn.execute("PRAGMA synchronous=NORMAL;")
        conn.execute("PRAGMA cache_size=-64000;")    # ~64 MB page cache
        conn.execute("PRAGMA busy_timeout=5000;")    # 5 s internal wait
        conn.execute("PRAGMA mmap_size=268435456;")  # 256 MB memory map
        return conn

    def _writer_loop(self):
        conn  = self._open()
        batch = []
        try:
            while not self._stop_event.is_set() or not self._queue.empty():
                try:
                    batch.append(self._queue.get(timeout=0.5))
                except queue.Empty:
                    if batch:
                        self._commit_batch(conn, batch)
                        batch.clear()
                    continue

                if len(batch) >= self.batch_size:
                    self._commit_batch(conn, batch)
                    batch.clear()

            if batch:
                self._commit_batch(conn, batch)

            # Final WAL checkpoint: removes -wal and -shm sidecar files
            conn.execute("PRAGMA wal_checkpoint(TRUNCATE);")
            conn.commit()
        except Exception as exc:
            self._error = exc
        finally:
            conn.close()

    def _commit_batch(self, conn: sqlite3.Connection, batch: list):
        INSERT = (
            "INSERT OR REPLACE INTO tiles "
            "(zoom_level, tile_column, tile_row, tile_data) VALUES (?, ?, ?, ?)"
        )
        for attempt in range(7):
            try:
                conn.execute("BEGIN IMMEDIATE;")
                conn.executemany(INSERT, batch)
                conn.commit()
                for _ in batch:
                    self._queue.task_done()
                return
            except sqlite3.OperationalError as exc:
                conn.rollback()
                if "database is locked" not in str(exc) or attempt == 6:
                    raise
                time.sleep(0.1 * (2 ** attempt))   # 0.1 s → 6.4 s

Usage in a multiprocessing pipeline:

from multiprocessing import Pool
from pathlib import Path

writer = MBTilesWriter("output/world-z0-z8.mbtiles", batch_size=500)
writer.initialize_schema({
    "name":    "world-z0-z8",
    "format":  "pbf",
    "minzoom": "0",
    "maxzoom": "8",
    "type":    "overlay",
})

def generate_tile(args):
    zoom, col, row = args
    data = encode_tile(zoom, col, row)   # your pbf encoder
    return zoom, col, row, data

with Pool(processes=8) as pool:
    for zoom, col, row, data in pool.imap_unordered(generate_tile, tile_coords):
        writer.add_tile(zoom, col, row, data)

writer.flush_and_close()

Worker processes run in parallel; only the main process writes to SQLite, so there is never more than one active writer.

How WAL mode eliminates lock contention

The diagram below shows why DELETE mode causes cascading failures in parallel pipelines and how WAL mode resolves them.

In DELETE mode, Writer A holds a file-level exclusive lock for the entire duration of its transaction. Writer B and any tile server or inspector trying to read the file both receive SQLITE_BUSY immediately. With WAL mode, Writer A appends new pages to the .mbtiles-wal sidecar; the reader continues reading the original database file at the snapshot it opened. Only the brief commit-lock window at the end of each batch is exclusive.

Interaction effects

With multiprocessing tile encoders. The MBTilesWriter class above assumes a single process owns the SQLite connection. If your architecture spawns multiprocessing.Pool workers that each open the file directly, WAL mode alone is insufficient — you still need a single-writer process. In that case, use multiprocessing.Queue to funnel encoded tiles from workers to one collector process that runs MBTilesWriter. This same producer-consumer split is the recommended approach when wrapping Tippecanoe CLI fundamentals with a Python subprocess orchestrator.

With attribute filtering. Pipelines that apply attribute filtering rules before insertion produce leaner tile payloads. Smaller tile_data blobs reduce the time each BEGIN IMMEDIATE lock is held, directly lowering contention. If you are stripping unused properties to reduce tile size, apply the filter before queuing tiles into MBTilesWriter — do not filter inside the locked transaction.

With zoom-level optimization. When following a zoom-level optimization strategy that generates separate zoom-range runs (e.g. z0–z5 in one pass, z6–z12 in another), you can safely run those passes against the same output file if each pass uses MBTilesWriter sequentially. Never run two zoom-range passes concurrently against the same file even with WAL mode — WAL permits only one simultaneous writer.

Performance impact

The two-phase architecture described in the Pipeline Architecture section below is faster but requires ~2× peak disk space for intermediate files. The single-file WAL approach is simpler and adequate for most datasets up to ~50 M tiles.

Common mistakes

Mistake 1 — Opening multiple connections without check_same_thread=False and believing that prevents contention.

sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that same thread.

Setting check_same_thread=False suppresses this exception but does not add any locking. Two threads can still collide on BEGIN IMMEDIATE. Fix: keep exactly one connection on one thread as shown above.

Mistake 2 — Forgetting to delete stale -wal and -shm files before restarting a failed build.

sqlite3.DatabaseError: database disk image is malformed

A crashed writer may leave an uncommitted WAL. SQLite recovers automatically on the next OPEN, but if the file was copied mid-write, the copy is corrupt. Always checkpoint and close cleanly; if you must copy a live .mbtiles, use VACUUM INTO 'copy.mbtiles' which produces a single-file snapshot with no sidecar.

Mistake 3 — Calling PRAGMA journal_mode=WAL on a read-only connection opened on a network filesystem (NFS, SMB).

sqlite3.OperationalError: unable to open database file

WAL requires shared-memory (-shm) file creation in the same directory. Network filesystems frequently deny this. Run tile generation on local disk (NVMe preferred) and copy the finished .mbtiles to network storage afterward.

Pipeline architecture for 50 M+ tiles

When scaling beyond 50 M tiles, a two-phase architecture eliminates lock contention entirely:

Phase 1 — parallel isolated writes. Each worker writes to its own temporary .mbtiles file (one per zoom level or geographic shard). No shared file, no locking. Each worker still uses PRAGMA journal_mode=WAL for its own file.

# Tippecanoe example: separate files per zoom range
tippecanoe -o /tmp/z0-z5.mbtiles  -Z 0 -z 5  input.geojson
tippecanoe -o /tmp/z6-z10.mbtiles -Z 6 -z 10 input.geojson
tippecanoe -o /tmp/z11-z14.mbtiles -Z 11 -z 14 input.geojson

Phase 2 — sequential merge. A single process combines shards using ATTACH DATABASE and a bulk INSERT INTO … SELECT. This runs sequentially, so no contention.

import sqlite3

def merge_mbtiles(output: str, shards: list[str]):
    conn = sqlite3.connect(output)
    conn.execute("PRAGMA journal_mode=WAL;")
    conn.execute("PRAGMA synchronous=NORMAL;")
    conn.execute("PRAGMA cache_size=-128000;")

    # Create schema in output
    conn.executescript(MBTilesWriter.SCHEMA)

    for i, shard in enumerate(shards):
        alias = f"shard{i}"
        conn.execute(f"ATTACH DATABASE '{shard}' AS {alias};")
        conn.execute("BEGIN;")
        conn.execute(
            f"INSERT OR REPLACE INTO tiles SELECT * FROM {alias}.tiles;"
        )
        conn.commit()
        conn.execute(f"DETACH DATABASE {alias};")

    conn.execute("PRAGMA wal_checkpoint(TRUNCATE);")
    conn.close()

merge_mbtiles("world.mbtiles", ["/tmp/z0-z5.mbtiles", "/tmp/z6-z10.mbtiles", "/tmp/z11-z14.mbtiles"])

After the merge, validate the MBTiles architecture limits on tile count and file size before serving from a tile server or converting to PMTiles for CDN delivery.

Deployment notes

• SQLite version: WAL mode requires SQLite 3.7.0+. Verify with python3 -c "import sqlite3; print(sqlite3.sqlite_version)". Alpine Linux and some Ubuntu LTS base images ship 3.31 — upgrade via apk add sqlite or apt-get install sqlite3.
• WAL sidecar files: Always run PRAGMA wal_checkpoint(TRUNCATE); as the final step before distributing the file. The -wal and -shm sidecars are required for crash recovery but must not be shipped alongside the .mbtiles to end users or tile servers.
• Docker volume mounts (macOS): gRPC-FUSE and VirtioFS mounts introduce latency spikes of 20–200 ms per fsync, which synchronous=FULL multiplies across every commit. Use synchronous=NORMAL and run on the Linux host filesystem inside the container when possible.
• Windows mandatory locking: Unlike Linux advisory locks, Windows uses mandatory file locking. The busy_timeout PRAGMA is especially important on Windows — set it to at least 10 000 ms in CI environments where disk I/O latency is unpredictable.

Up: MBTiles Architecture & Limits

Related

• MBTiles Architecture & Limits — the container format’s schema, tile-count ceiling, and file-size constraints that bound how large a single .mbtiles build can grow.
• PMTiles Specification Deep Dive — the cloud-native alternative that avoids SQLite entirely and serves tiles directly from an object-store with HTTP range requests.
• Essential Tippecanoe Flags for Production Builds — Tippecanoe’s own MBTiles output options including --temporary-directory for controlling where it writes intermediate tile data.
• Dropping Unused Attributes to Reduce Tile Size — smaller tile_data blobs reduce lock-hold time per batch commit, complementing the WAL strategy on this page.