How to fix SQLITE_PROTOCOL: Database lock protocol error in SQLite

const Database = require('better-sqlite3');

async function executeWithRetry(db, sql, params, maxRetries = 3) {
  for (let attempt = 0; attempt < maxRetries; attempt++) {
    try {
      const stmt = db.prepare(sql);
      return params ? stmt.run(params) : stmt.run();
    } catch (error) {
      if (error.code === 'SQLITE_PROTOCOL' && attempt < maxRetries - 1) {
        // Exponential backoff: 10ms, 40ms, 90ms, etc.
        const delay = Math.pow(attempt + 1, 2) * 10;
        await new Promise(resolve => setTimeout(resolve, delay));
        continue;
      }
      throw error;
    }
  }
}

// Usage
const db = new Database('database.db');
await executeWithRetry(db, 'BEGIN TRANSACTION');
await executeWithRetry(db, 'INSERT INTO users (name) VALUES (?)', ['Alice']);
await executeWithRetry(db, 'COMMIT');

import sqlite3
import time

def execute_with_retry(conn, sql, params=None, max_retries=3):
    cursor = conn.cursor()
    for attempt in range(max_retries):
        try:
            if params:
                cursor.execute(sql, params)
            else:
                cursor.execute(sql)
            return cursor
        except sqlite3.OperationalError as e:
            if 'SQLITE_PROTOCOL' in str(e) and attempt < max_retries - 1:
                # Exponential backoff
                time.sleep((attempt + 1) ** 2 * 0.01)  # 0.01, 0.04, 0.09 seconds
                continue
            raise

# Usage
conn = sqlite3.connect('database.db')
execute_with_retry(conn, 'BEGIN TRANSACTION')
execute_with_retry(conn, 'INSERT INTO users (name) VALUES (?)', ('Alice',))
execute_with_retry(conn, 'COMMIT')

import java.sql.*;

public class SqliteRetry {
    public static void executeWithRetry(Connection conn, String sql, int maxRetries) throws SQLException {
        for (int attempt = 0; attempt < maxRetries; attempt++) {
            try (Statement stmt = conn.createStatement()) {
                stmt.execute(sql);
                return;
            } catch (SQLException e) {
                if (e.getMessage().contains("SQLITE_PROTOCOL") && attempt < maxRetries - 1) {
                    try {
                        Thread.sleep((long) Math.pow(attempt + 1, 2) * 10); // Exponential backoff
                    } catch (InterruptedException ie) {
                        Thread.currentThread().interrupt();
                        throw new SQLException("Interrupted during retry", ie);
                    }
                    continue;
                }
                throw e;
            }
        }
    }
}

class TransactionQueue {
  constructor() {
    this.queue = [];
    this.processing = false;
  }

  async enqueue(transactionFn) {
    return new Promise((resolve, reject) => {
      this.queue.push({ transactionFn, resolve, reject });
      this.processQueue();
    });
  }

  async processQueue() {
    if (this.processing || this.queue.length === 0) return;

    this.processing = true;
    const { transactionFn, resolve, reject } = this.queue.shift();

    try {
      const result = await transactionFn();
      resolve(result);
    } catch (error) {
      reject(error);
    } finally {
      this.processing = false;
      this.processQueue();
    }
  }
}

// Usage
const queue = new TransactionQueue();
await queue.enqueue(async () => {
  const db = new Database('database.db');
  db.exec('BEGIN TRANSACTION');
  // ... operations ...
  db.exec('COMMIT');
});

const Database = require('better-sqlite3');

class ConnectionPool {
  constructor(dbPath, maxConnections = 1) {
    this.dbPath = dbPath;
    this.maxConnections = maxConnections;
    this.available = [];
    this.inUse = new Set();
  }

  getConnection() {
    if (this.available.length > 0) {
      const conn = this.available.pop();
      this.inUse.add(conn);
      return conn;
    }

    if (this.inUse.size < this.maxConnections) {
      const conn = new Database(this.dbPath);
      this.inUse.add(conn);
      return conn;
    }

    throw new Error('No connections available');
  }

  releaseConnection(conn) {
    this.inUse.delete(conn);
    this.available.push(conn);
  }
}

// Usage with single connection (serializes all operations)
const pool = new ConnectionPool('database.db', 1);
const conn = pool.getConnection();
try {
  conn.exec('BEGIN TRANSACTION');
  // ... operations ...
  conn.exec('COMMIT');
} finally {
  pool.releaseConnection(conn);
}

import threading
import sqlite3

class DatabaseLock:
    def __init__(self):
        self.lock = threading.Lock()

    def execute_transaction(self, conn, operations):
        with self.lock:  # Only one transaction at a time
            cursor = conn.cursor()
            cursor.execute('BEGIN TRANSACTION')
            for sql, params in operations:
                cursor.execute(sql, params)
            cursor.execute('COMMIT')

# Usage
lock = DatabaseLock()
conn = sqlite3.connect('database.db')
operations = [
    ('INSERT INTO users (name) VALUES (?)', ('Alice',)),
    ('UPDATE stats SET count = count + 1', None)
]
lock.execute_transaction(conn, operations)

-- Set a longer busy timeout (default is 0ms)
PRAGMA busy_timeout = 5000;  -- Wait up to 5 seconds for locks

-- Enable WAL mode if not already enabled
PRAGMA journal_mode = WAL;

-- Adjust WAL checkpoint settings
PRAGMA wal_autocheckpoint = 1000;  -- Checkpoint after 1000 pages
PRAGMA synchronous = NORMAL;        -- Balanced durability vs performance

-- Increase shared memory cache
PRAGMA cache_size = -20000;         -- 20MB cache

-- If SQLITE_PROTOCOL errors persist, try different journal modes
PRAGMA journal_mode = DELETE;  -- Default rollback journal (no WAL)

-- Or for single-process applications:
PRAGMA locking_mode = EXCLUSIVE;  -- Database locked to single process

// Node.js better-sqlite3 with timeout
const db = new Database('database.db', {
  timeout: 10000,  // 10 second timeout
  verbose: console.log  // Enable logging for debugging
});

// Python with timeout
import sqlite3
conn = sqlite3.connect('database.db', timeout=10.0)  // 10 second timeout

import sqlite3
import logging

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

def execute_with_monitoring(conn, sql, params=None):
    try:
        cursor = conn.cursor()
        if params:
            cursor.execute(sql, params)
        else:
            cursor.execute(sql)
        return cursor
    except sqlite3.OperationalError as e:
        if 'SQLITE_PROTOCOL' in str(e):
            logger.warning(f'SQLITE_PROTOCOL error detected: {e}')
            logger.info(f'SQL: {sql}, Params: {params}')
            # Log additional context for debugging
        raise

# Usage with monitoring
conn = sqlite3.connect('database.db')
execute_with_monitoring(conn, 'BEGIN TRANSACTION')

// Add logging to identify where transactions are started
const originalBegin = db.exec;
db.exec = function(sql) {
  if (sql.toUpperCase().includes('BEGIN TRANSACTION')) {
    console.log('Transaction started at: ' + new Date().toISOString());
    console.trace('Transaction start stack trace');
  }
  return originalBegin.call(this, sql);
};

// Or use a wrapper function
function beginTransaction(db) {
  console.log('Transaction started by: ' + getCallerInfo());
  return db.exec('BEGIN TRANSACTION');
}

function getCallerInfo() {
  const stack = new Error().stack.split('\n');
  // Skip first two lines (Error and getCallerInfo)
  return stack.slice(2, 4).join(' -> ');
}

# Pattern 1: Single writer with multiple readers
import threading
from queue import Queue

class DatabaseWriter:
    def __init__(self, db_path):
        self.db_path = db_path
        self.write_queue = Queue()
        self.writer_thread = threading.Thread(target=self._writer_loop)
        self.writer_thread.start()

    def _writer_loop(self):
        conn = sqlite3.connect(self.db_path)
        while True:
            operation = self.write_queue.get()
            if operation is None:  # Shutdown signal
                break
            try:
                conn.execute('BEGIN TRANSACTION')
                operation(conn)
                conn.execute('COMMIT')
            except Exception as e:
                conn.execute('ROLLBACK')
                print(f"Write failed: {e}")

    def execute_write(self, operation):
        """Queue a write operation for serial execution"""
        self.write_queue.put(operation)

    def shutdown(self):
        self.write_queue.put(None)
        self.writer_thread.join()

# Pattern 2: Use read replicas for high read concurrency
class ReadReplicaManager:
    def __init__(self, master_db, replica_count=2):
        self.master = master_db
        self.replicas = [sqlite3.connect(f'{master_db}_replica{i}')
                        for i in range(replica_count)]
        self.replica_index = 0

    def get_read_connection(self):
        """Round-robin load balancing for reads"""
        conn = self.replicas[self.replica_index]
        self.replica_index = (self.replica_index + 1) % len(self.replicas)
        return conn

    def sync_replicas(self):
        """Periodically sync replicas from master"""
        # Implementation depends on your sync strategy
        pass

// Node.js graceful shutdown
process.on('SIGTERM', async () => {
  console.log('SIGTERM received, shutting down gracefully...');

  // Close all database connections
  await closeAllDatabaseConnections();

  // Wait for pending transactions to complete
  await waitForPendingTransactions();

  process.exit(0);
});

// Python graceful shutdown
import signal
import sys

def signal_handler(signum, frame):
    print(f"Signal {signum} received, shutting down...")
    # Close database connections
    close_all_connections()
    sys.exit(0)

signal.signal(signal.SIGINT, signal_handler)
signal.signal(signal.SIGTERM, signal_handler)

# Check for and clean up stale WAL files
# First, ensure no processes are using the database
fuser database.db  # Linux/macOS
# or
lsof database.db

# If no processes are using it, clean up
rm -f database.db-wal
rm -f database.db-shm

# On Windows (PowerShell):
# Stop all processes using the database first
Get-Process | Where-Object { $_.Path -like "*database.db*" } | Stop-Process
Remove-Item "database.db-wal" -ErrorAction SilentlyContinue
Remove-Item "database.db-shm" -ErrorAction SilentlyContinue

import sqlite3
import time

def check_database_health(db_path):
    """Perform health checks on SQLite database"""
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()

        # Check if database is accessible
        cursor.execute('SELECT 1')

        # Check WAL mode status
        cursor.execute('PRAGMA journal_mode')
        journal_mode = cursor.fetchone()[0]
        print(f'Journal mode: {journal_mode}')

        # Check for pending WAL checkpoint
        cursor.execute('PRAGMA wal_checkpoint(PASSIVE)')
        checkpoint = cursor.fetchone()
        print(f'WAL checkpoint: {checkpoint}')

        # Verify integrity
        cursor.execute('PRAGMA quick_check')
        integrity = cursor.fetchone()[0]
        print(f'Database integrity: {integrity}')

        conn.close()
        return True
    except sqlite3.Error as e:
        print(f'Database health check failed: {e}')
        return False

# Schedule regular health checks
import schedule

schedule.every(5).minutes.do(check_database_health, 'database.db')

while True:
    schedule.run_pending()
    time.sleep(1)

# Regular backups to prevent data loss
sqlite3 database.db ".backup backup_$(date +%Y%m%d_%H%M%S).db"

# Or use VACUUM to optimize and backup
sqlite3 database.db "VACUUM INTO 'backup_$(date +%Y%m%d).db'"

# Automate with cron (Linux/macOS)
# Add to crontab: 0 2 * * * /path/to/backup_script.sh