SQLITE_BUSY: database is locked

The database is locked error happens when SQLite can't acquire a lock on the database because another connection is using it. This is common in multi-threaded applications or when multiple processes access the same database.

Understanding the Error

SQLite uses file-level locking. When one connection is writing, others must wait. If the wait times out, you get this error.

SQLITE_BUSY: database is locked

Common Causes

1. Long-Running Write Transactions

A transaction that takes too long blocks other connections:

BEGIN TRANSACTION;
-- Long operation here
UPDATE large_table SET value = 'new' WHERE condition;
-- Other connections blocked until COMMIT
COMMIT;

2. Uncommitted Transactions

Forgetting to commit or rollback leaves locks open:

// Bad: Transaction never closed
db.run('BEGIN TRANSACTION');
db.run('INSERT INTO users VALUES (1, "Alice")');
// Forgot COMMIT - database stays locked!

3. Multiple Processes Accessing the Same File

Two applications or scripts accessing the same .db file simultaneously.

4. WAL Mode Not Enabled

The default journal mode is more restrictive than WAL mode.

How to Fix It

Solution 1: Enable WAL Mode

Write-Ahead Logging allows concurrent reads during writes:

PRAGMA journal_mode=WAL;

This is the single most effective fix for busy errors. WAL mode allows:

• Multiple readers simultaneously
• One writer without blocking readers
• Better performance overall

Solution 2: Set a Busy Timeout

Tell SQLite to wait instead of failing immediately:

PRAGMA busy_timeout = 5000; -- Wait up to 5 seconds

In application code:

// Node.js with better-sqlite3
const db = new Database('mydb.db');
db.pragma('busy_timeout = 5000');

// Python
conn = sqlite3.connect('mydb.db')
conn.execute('PRAGMA busy_timeout = 5000')

Solution 3: Keep Transactions Short

Minimize the time locks are held:

// Bad: Lock held during slow operation
db.run('BEGIN');
await slowNetworkCall(); // Database locked during this!
db.run('INSERT ...');
db.run('COMMIT');

// Good: Prepare data first, then quick transaction
const data = await slowNetworkCall();
db.run('BEGIN');
db.run('INSERT ...', data);
db.run('COMMIT');

Solution 4: Use Immediate Transactions

Start transactions with IMMEDIATE to fail fast if locked:

BEGIN IMMEDIATE TRANSACTION;
-- If database is locked, fails immediately instead of later
INSERT INTO users VALUES (1, 'Alice');
COMMIT;

Solution 5: Implement Retry Logic

Retry failed operations with exponential backoff:

async function withRetry(operation, maxRetries = 3) {
  for (let i = 0; i < maxRetries; i++) {
    try {
      return await operation();
    } catch (err) {
      if (err.code === 'SQLITE_BUSY' && i < maxRetries - 1) {
        await sleep(100 * Math.pow(2, i)); // 100ms, 200ms, 400ms
        continue;
      }
      throw err;
    }
  }
}

Solution 6: Use Connection Pooling Properly

For web applications, ensure proper connection management:

// Use a single connection or proper pooling
const db = new Database('mydb.db');

// Don't create new connections per request
app.get('/users', (req, res) => {
  // Reuse existing connection
  const users = db.prepare('SELECT * FROM users').all();
  res.json(users);
});

Best Practices

1. Always enable WAL mode for any multi-connection use case
2. Set a reasonable busy_timeout (3-10 seconds typically)
3. Keep transactions as short as possible
4. Always close transactions with COMMIT or ROLLBACK
5. Use a single connection when possible in simple applications
6. Consider a different database if you need heavy concurrent writes

Checking Current Settings

-- Check journal mode
PRAGMA journal_mode;

-- Check busy timeout
PRAGMA busy_timeout;

-- Check if WAL mode is working
PRAGMA wal_checkpoint;

Related Errors

• SQLITE_LOCKED - Table-level lock conflict (different from BUSY)
• SQLITE_READONLY - Can't write to the database file