How to fix SQLITE_LOCKED: A table in the database is locked in SQLite

// Problem: Two statements active simultaneously
const stmt1 = db.prepare('SELECT * FROM users');
const stmt2 = db.prepare('DROP TABLE users'); // LOCKED error

const rows = stmt1.all(); // Statement 1 active
stmt2.run(); // Tries to drop table while select is active

// When using shared cache mode
const db1 = new Database('test.db', { cached: true });
const db2 = new Database('test.db', { cached: true });

db1.prepare('BEGIN IMMEDIATE').run();
db2.prepare('DROP TABLE users').run(); // LOCKED error

export SQLITE_TRACE=file:/tmp/sqlite.log

const db = require('better-sqlite3')('database.db');

// CORRECT: Finalize old statement before creating conflicting one
const selectStmt = db.prepare('SELECT * FROM users');
const rows = selectStmt.all();
selectStmt.finalize(); // Explicitly finalize

// Now safe to drop table
const dropStmt = db.prepare('DROP TABLE users');
dropStmt.run();
dropStmt.finalize();

import sqlite3

conn = sqlite3.connect('database.db')
cursor = conn.cursor()

# CORRECT: Use context managers
with conn:
    cursor.execute('SELECT * FROM users')
    rows = cursor.fetchall()

# Cursor automatically closed, now safe for DROP
with conn:
    cursor.execute('DROP TABLE users')

try (Connection conn = DriverManager.getConnection("jdbc:sqlite:database.db");
     Statement stmt1 = conn.createStatement();
     ResultSet rs = stmt1.executeQuery("SELECT * FROM users")) {

    // Process results...
}

// Statements automatically closed, now safe for DROP
try (Connection conn = DriverManager.getConnection("jdbc:sqlite:database.db");
     Statement stmt2 = conn.createStatement()) {
    stmt2.executeUpdate("DROP TABLE users");
}

-- Check current shared cache setting
PRAGMA shared_cache;

-- Enable shared cache (requires reconnection)
PRAGMA shared_cache = ON;

-- Disable shared cache to isolate connections
PRAGMA shared_cache = OFF;

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;

// Disable shared cache for isolation
const db = require('better-sqlite3')('database.db', {
  cached: false  // Default is false, but explicit is good
});

// Or enable with caution
const sharedDb = require('better-sqlite3')('database.db', {
  cached: true,
  verbose: console.log  // Log lock activity
});

import sqlite3

# Disable shared cache
conn1 = sqlite3.connect('database.db', check_same_thread=False)
conn1.execute('PRAGMA shared_cache = OFF')

# Or enable with isolation levels
conn2 = sqlite3.connect('database.db', isolation_level='IMMEDIATE')

-- DEFERRED (default): Acquires locks as needed
BEGIN DEFERRED;

-- IMMEDIATE: Acquires RESERVED lock immediately
BEGIN IMMEDIATE;

-- EXCLUSIVE: Acquires EXCLUSIVE lock immediately
BEGIN EXCLUSIVE;

const db = require('better-sqlite3')('database.db');

// Use transaction helper
function runInTransaction(callback) {
  const transaction = db.transaction(callback);
  return transaction();
}

// Usage
runInTransaction(() => {
  db.prepare('INSERT INTO users (name) VALUES (?)').run('Alice');
  db.prepare('UPDATE stats SET count = count + 1').run();
});

// All statements finalized automatically

import sqlite3
from contextlib import contextmanager

@contextmanager
def transaction(conn):
    try:
        conn.execute('BEGIN IMMEDIATE')
        yield
        conn.commit()
    except:
        conn.rollback()
        raise
    finally:
        # Ensure all statements are finalized
        for stmt in conn._statements:
            stmt.finalize()

# Usage
with transaction(conn):
    conn.execute('INSERT INTO users (name) VALUES (?)', ('Bob',))

-- First, ensure no active queries on the table
-- Check for active statements (application-specific)

-- Then acquire exclusive lock
BEGIN EXCLUSIVE;

-- Drop the table
DROP TABLE users;

-- Commit immediately
COMMIT;

async function safeDropTable(tableName) {
  // 1. Finalize all statements using this table
  db.prepare('SELECT * FROM sqlite_master WHERE type="table"').all();

  // 2. Close any open cursors (application-specific)

  // 3. Execute with exclusive transaction
  const dropStmt = db.prepare(`DROP TABLE ${tableName}`);

  try {
    db.prepare('BEGIN EXCLUSIVE').run();
    dropStmt.run();
    db.prepare('COMMIT').run();
  } catch (error) {
    db.prepare('ROLLBACK').run();
    throw error;
  } finally {
    dropStmt.finalize();
  }
}

-- Check if database is locked (SQLite 3.32.0+)
SELECT * FROM pragma_lock_status;

-- Check active connections (requires custom VFS)

# Environment variable approach
export SQLITE_TRACE=file:/tmp/sqlite.log
export SQLITE_DEBUG=1

# Or programmatically in Node.js
const db = require('better-sqlite3')('database.db', {
  verbose: (sql, params) => console.log('SQL:', sql, 'Params:', params)
});

-- View current locks
SELECT * FROM pragma_lock_status;

-- Check WAL mode status
PRAGMA wal_checkpoint(TRUNCATE);
PRAGMA wal_autocheckpoint;

-- Monitor database connections
PRAGMA database_list;
PRAGMA cache_size;

-- Find tables with pending operations
SELECT name FROM sqlite_master
WHERE type='table'
AND sql LIKE '%WITHOUT ROWID%';

-- Check for unfinalized statements (application-level)
-- This requires custom tracking in your application

-- Monitor performance schema (if available)
SELECT * FROM sqlite_stat1;

// Track statement lifecycle
class MonitoredDatabase {
  constructor(dbPath) {
    this.db = require('better-sqlite3')(dbPath);
    this.activeStatements = new Set();

    // Wrap prepare to track statements
    const originalPrepare = this.db.prepare.bind(this.db);
    this.db.prepare = (sql) => {
      const stmt = originalPrepare(sql);
      this.activeStatements.add(stmt);

      // Wrap finalize
      const originalFinalize = stmt.finalize.bind(stmt);
      stmt.finalize = () => {
        this.activeStatements.delete(stmt);
        return originalFinalize();
      };

      return stmt;
    };
  }

  getActiveStatementCount() {
    return this.activeStatements.size;
  }
}