How to Troubleshoot Database Locking and Concurrency Issues in SpiderFoot

To resolve database locking and concurrency issues in SpiderFoot, verify WAL mode is active, implement retry loops around database calls, reduce thread pool size if needed, and ensure no external processes hold locks on the SQLite file.

SpiderFoot's modular reconnaissance engine runs dozens of scanning modules in parallel threads, all writing results to a local SQLite database. This architecture makes database locking and concurrency issues a common operational challenge when running large-scale scans. This guide walks through SpiderFoot's built-in mitigation strategies, diagnostic techniques, and practical fixes grounded in the actual source code implementation.

How SpiderFoot Handles Database Concurrency

The project implements several protective mechanisms in spiderfoot/db.py to minimize lock contention.

Thread-Safe Access with RLock

All database operations are serialized through a re-entrant lock defined at spiderfoot/db.py#L34-L36:

import threading
self.dbhLock = threading.RLock()

Every public method in the SpiderFootDb class acquires this lock before accessing the SQLite connection or cursor, ensuring only one thread can execute database operations at a time.

Write-Ahead Logging (WAL) Mode

The database initialization code sets WAL journal mode at spiderfoot/db.py#L39:

 cursor.execute("PRAGMA journal_mode=WAL")

WAL allows concurrent reads during write transactions, dramatically reducing the most common source of "database locked" errors in read-heavy workloads.

Graceful Error Detection and Handling

When SQLite operations fail, SpiderFoot checks exception messages for lock-related strings. At spiderfoot/db.py#L590-L637, functions like scanLogEvent and scanLogEvents handle lock errors by returning False or skipping inserts rather than crashing:

# Simplified excerpt from error handling logic

if "locked" in str(e) or "thread" in str(e):
    return False  # Caller can retry later

This design lets the scanner continue operating even when transient locks occur.

Common Causes of Database Locks

Situation	Root Cause	Diagnostic Approach
Multiple modules writing simultaneously	SQLite allows only one writer at a time; WAL serializes writes internally	Search logs for sqlite3.Error with "locked" in the message
Long-running transactions	Uncommitted reads or bulk operations hold locks indefinitely	Review custom plugins for manual transactions missing timely commit() calls
External process accessing the database	Second SpiderFoot instance or sfcli run holds exclusive lock	Use lsof to check file handles on the .sqlite file
Filesystem permission issues	SQLite cannot create WAL or lock files	Verify opts['__database'] directory is writable (see spiderfoot/db.py#L11-L17)

Step-by-Step Troubleshooting Procedure

1. Verify WAL Mode Is Active

Connect to your SpiderFoot database and confirm the journal mode:

import sqlite3

conn = sqlite3.connect("/path/to/spiderfoot.db")
cur = conn.cursor()
cur.execute("PRAGMA journal_mode;")
print("Journal mode:", cur.fetchone()[0])  # Expected: "wal"

If this returns delete instead of wal, the database was created without WAL mode. Delete and recreate the database, or execute PRAGMA journal_mode=WAL; manually.

2. Enable Detailed Logging

Increase log verbosity in spiderfoot/logger.py and monitor for lock-related messages. This reveals which operations are failing and how frequently.

3. Reproduce and Isolate the Lock

Run a scan with high concurrency to stress-test the database:

python sf.py -m all -t 50 -f target.com

Monitor the database file size—rapid growth without corresponding commits often indicates pending writes blocked by locks.

4. Implement Retry Wrappers

The core library lacks automatic exponential backoff. Wrap problematic calls with a retry loop:

import time

def safe_log_event(sfdb, instance_id, classification, message, retries=3, delay=0.2):
    """Retry scanLogEvent with exponential backoff on lock errors."""
    for attempt in range(retries):
        try:
            sfdb.scanLogEvent(instance_id, classification, message)
            return True
        except IOError as e:
            if "locked" in str(e).lower():
                time.sleep(delay * (2 ** attempt))  # Exponential backoff

                continue
            raise
    return False

For plugin development, apply this pattern to any database interaction:

def log_event_with_retry(sfdb, instance_id, classification, message):
    max_tries = 5
    for i in range(max_tries):
        try:
            sfdb.scanLogEvent(instance_id, classification, message)
            return True
        except IOError as err:
            if "locked" in str(err):
                time.sleep(0.1 * (i + 1))  # Linear backoff

                continue
            raise
    return False

5. Reduce Parallelism If Retries Fail

Lower the thread pool size using the -t flag in sfcli.py:

python sf.py -m all -t 10 -f target.com  # Reduced from default 50

Fewer concurrent writers reduce lock contention at the cost of scan duration.

6. Check for Orphaned Processes

Ensure no previous SpiderFoot processes hold stale locks:

lsof | grep spiderfoot.db

Kill any orphaned processes before restarting your scan.

Key Source Files for Reference

• spiderfoot/db.py – Core database class implementing RLock, WAL pragma, and all CRUD operations. The thread lock resides at lines 34-36; WAL activation at line 39; error handling at lines 590-637.
• spiderfoot/logger.py – Custom SQLite log handler that routes scan logs through the database class.
• sfcli.py – Command-line entry point exposing the -t (thread count) option that directly impacts concurrency levels.

Summary

• SpiderFoot uses RLock serialization and WAL mode as primary defenses against database locking
• Lock errors surface as IOError or sqlite3.Error with "locked" in the message, handled gracefully in spiderfoot/db.py
• Always verify WAL mode is active before troubleshooting deeper
• Implement retry loops with backoff around critical database calls when running high-concurrency scans
• Reduce -t thread count and eliminate orphaned processes when locks persist

Frequently Asked Questions

Why does SpiderFoot use SQLite instead of a server database?

SpiderFoot prioritizes zero-configuration deployment. SQLite requires no separate server process, works across all platforms, and ships with Python. For single-instance reconnaissance, SQLite's performance is sufficient when WAL mode and proper concurrency controls are applied.

Can I switch SpiderFoot to PostgreSQL or MySQL to avoid locking?

The codebase is tightly coupled to SQLite. While theoretically possible with significant refactoring of spiderfoot/db.py, no native migration path exists. For production scaling, consider running multiple SpiderFoot instances with separate databases rather than modifying the database layer.

What is the recommended thread count to avoid database locks?

Start with 10-20 threads for large scans. Monitor for lock errors and adjust downward if needed. The default behavior depends on your specific hardware and scan profile—there is no universal safe value.

How do I identify which plugin is causing long-running transactions?

Enable SQL query logging and look for slow or uncommitted transactions. Custom plugins that override SpiderFootPlugin methods and manually call db.fetchAll() without subsequent commit() are common culprits. Review any plugin code that uses dbh connections directly rather than the thread-safe wrapper methods.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}