Hey there! Let’s tackle this SQLITE_BUSY: database is locked error head-on—sounds like those bulk inserts from your 1000-1500 row JSON file are causing way more trouble than they’re worth. I’ve run into this exact scenario dozens of times with apps that try to cram too many rows into SQLite all at once, so let’s break down some practical, proven fixes that’ll stop those daily crashes.

SQLite locks the database for every individual insert by default if you don’t use a transaction. That means 1500 inserts = 1500 separate lock attempts, which is a recipe for busy errors and glacial performance. Instead, wrap all your inserts in one transaction to minimize lock contention:

BEGIN TRANSACTION;
-- All your INSERT statements go here
INSERT INTO your_table (col1, col2) VALUES ('val1', 'val2');
INSERT INTO your_table (col1, col2) VALUES ('val3', 'val4');
-- ... repeat for all JSON rows
COMMIT;

This cuts lock operations down to just two (one for BEGIN, one for COMMIT) and drastically reduces the time the database spends in a locked state. Most programming languages simplify this too—for example, in Python’s sqlite3 library, you can use context managers to handle transactions automatically.

Sometimes even with transactions, SQLite might hit a brief lock conflict. Instead of immediately throwing an error, tell it to wait a bit for the lock to release. Run this PRAGMA statement once when you open the database:

PRAGMA busy_timeout = 5000; -- Waits 5 seconds (5000 milliseconds)

This gives SQLite room to retry the operation instead of bailing out right away. Adjust the number based on your needs—3-10 seconds is usually enough for bulk insert jobs.

Parameterized queries let SQLite reuse the same query plan for multiple rows, which speeds up inserts and reduces the time the database stays locked. Instead of building a new INSERT string for each row, use placeholders:

For example, in Python:

import sqlite3
import json

with open('your_data.json') as f:
    data = json.load(f)

conn = sqlite3.connect('your_db.db')
conn.execute('PRAGMA busy_timeout = 5000')
conn.execute('PRAGMA journal_mode = WAL') # We’ll cover this next

cursor = conn.cursor()
# Use ? as placeholders for your columns
insert_query = "INSERT INTO your_table (col1, col2, col3) VALUES (?, ?, ?)"

# Start transaction
conn.execute('BEGIN TRANSACTION')
try:
    for row in data:
        cursor.execute(insert_query, (row['key1'], row['key2'], row['key3']))
    conn.commit()
except Exception as e:
    conn.rollback()
    print(f"Error inserting data: {e}")
finally:
    cursor.close()
    conn.close()

This is not only more efficient but also prevents SQL injection—always a smart practice!

SQLite’s default journal mode (DELETE) uses exclusive locks during writes, which can trigger busy errors even with transactions. Switching to Write-Ahead Logging (WAL) mode allows concurrent reads during writes, which cuts down lock conflicts dramatically. Enable it with:

PRAGMA journal_mode = WAL;

This setting persists across database connections, so you only need to run it once (it saves to the database file automatically). WAL is a game-changer for bulk write scenarios.

Even if you’re sure no other program is accessing the database, your own code might be leaving connections or cursors open accidentally. For example, if an error hits before you close a cursor, it could hold a lock indefinitely. Always use try-finally blocks or context managers (like Python’s with statement) to ensure connections are closed properly—just like in the code example above.

Quick Recap of the Best Fixes #

• Top priority: Wrap all bulk inserts in a single transaction. This will eliminate 90% of your busy errors.
• Add WAL mode: It reduces lock conflicts and boosts overall performance.
• Set a busy timeout: Gives SQLite room to retry instead of crashing immediately.

These changes should make those daily crashes a thing of the past!

内容的提问来源于stack exchange，提问作者Hedva