SQLITE_LOCKED: table is locked

This error occurs when a table-level lock prevents your operation. Different from SQLITE_BUSY, this is about internal table locks within a connection.

The table is locked error (SQLITE_LOCKED) is different from database is locked (SQLITE_BUSY). It's about table-level locks within SQLite.

Understanding the Error

SQLITE_LOCKED: database table is locked

This usually happens within a single connection when operations conflict.

Common Causes

1. Reading While Writing Same Table

JAVASCRIPT

// Iterating through results while trying to modify
const stmt = db.prepare('SELECT * FROM users');
for (const row of stmt.iterate()) {
  // Trying to modify the table we're reading
  db.run('DELETE FROM users WHERE id = ?', [row.id]);  // Error!
}

2. Multiple Statements on Same Table

JAVASCRIPT

// Two active statements on same table
const stmt1 = db.prepare('SELECT * FROM users');
const stmt2 = db.prepare('UPDATE users SET active = 1');

stmt1.get();  // Start reading
stmt2.run();  // Try to write - locked!

3. Recursive Triggers

SQL

-- Trigger that modifies same table
CREATE TRIGGER after_insert AFTER INSERT ON logs
BEGIN
  INSERT INTO logs (message) VALUES ('triggered');  -- Recursive!
END;

4. Shared Cache Mode Conflicts

When using shared cache, connections can lock each other's tables.

How to Fix It

Solution 1: Collect Then Modify

JAVASCRIPT

// First, collect all IDs
const ids = db.prepare('SELECT id FROM users WHERE active = 0')
  .all()
  .map(row => row.id);

// Then modify (statement closed)
for (const id of ids) {
  db.run('DELETE FROM users WHERE id = ?', [id]);
}

Solution 2: Use Transactions

JAVASCRIPT

db.transaction(() => {
  const users = db.prepare('SELECT id FROM users WHERE active = 0').all();
  const deleteStmt = db.prepare('DELETE FROM users WHERE id = ?');

  for (const user of users) {
    deleteStmt.run(user.id);
  }
})();

Solution 3: Finalize Statements

JAVASCRIPT

const stmt = db.prepare('SELECT * FROM users');
const results = stmt.all();  // Fetch all, statement auto-finalizes

// Now safe to modify
db.run('UPDATE users SET processed = 1');

Solution 4: Use Single Statement

SQL

-- Instead of SELECT then DELETE
DELETE FROM users WHERE active = 0;

-- Instead of SELECT then UPDATE
UPDATE users SET processed = 1 WHERE active = 0;

Solution 5: Disable Shared Cache

JAVASCRIPT

// Open without shared cache
const db = new Database('mydb.db', {
  // shared: false is usually default
});

Best Practices

Finish reading before writing to same table
Use .all() instead of iterating with active cursor
Use single SQL statements when possible
Avoid shared cache unless specifically needed
Keep transactions short