This SQLite error occurs when a write operation is blocked by a conflict on the same database connection or shared cache. It typically happens when multiple threads or processes attempt conflicting operations on the same table, or when using shared cache mode without proper synchronization.
SQLITE_LOCKED (error code 6) is a concurrency error that occurs when SQLite cannot acquire the necessary locks for a write operation due to conflicts within the same database connection or shared cache. Unlike SQLITE_BUSY which indicates conflicts between different connections, SQLITE_LOCKED specifically indicates conflicts within a single connection or shared cache environment. This can happen when: 1. **Multiple statements on the same connection** attempt conflicting operations 2. **Shared cache mode** is enabled and multiple database connections within the same process conflict 3. **A prepared statement** is trying to execute while another operation on the same connection holds incompatible locks 4. **DROP TABLE or ALTER TABLE** operations conflict with active queries on the same connection SQLite uses a hierarchical locking system (UNLOCKED → SHARED → RESERVED → PENDING → EXCLUSIVE) to manage concurrency. SQLITE_LOCKED occurs when a connection tries to escalate its lock level but encounters a conflict from its own previously acquired locks or from other connections in the same shared cache.
First, determine what operations are causing the lock conflict. Check your application code for:
Common conflict patterns:
1. Multiple active statements on same connection:
// 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 active2. Shared cache conflicts:
// 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 errorDebugging approach:
- Add logging to track which statements are executed and when
- Use SQLite trace to see lock acquisition:
export SQLITE_TRACE=file:/tmp/sqlite.log- Check for unfinalized statements with connection status
Ensure prepared statements are finalized when no longer needed:
Node.js with better-sqlite3:
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();Python with sqlite3:
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')Java with SQLite JDBC:
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");
}If using shared cache mode, configure it correctly:
Enable/disable shared cache:
-- 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;Best practices for shared cache:
1. Use connection pooling with proper isolation
2. Implement application-level locking for critical sections
3. Avoid long-running transactions in shared cache mode
4. Use WAL mode to reduce lock contention:
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;Connection configuration examples:
Node.js (better-sqlite3):
// 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
});Python:
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')Use appropriate transaction types and ensure proper cleanup:
Transaction types in SQLite:
-- DEFERRED (default): Acquires locks as needed
BEGIN DEFERRED;
-- IMMEDIATE: Acquires RESERVED lock immediately
BEGIN IMMEDIATE;
-- EXCLUSIVE: Acquires EXCLUSIVE lock immediately
BEGIN EXCLUSIVE;Safe transaction patterns:
Node.js:
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 automaticallyPython with context managers:
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',))These operations require exclusive access and often cause SQLITE_LOCKED:
Safe DROP TABLE pattern:
-- 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;Application-level implementation:
Node.js:
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();
}
}Alternative: Use PRAGMA to check lock status
-- Check if database is locked (SQLite 3.32.0+)
SELECT * FROM pragma_lock_status;
-- Check active connections (requires custom VFS)For ALTER TABLE operations, consider:
1. Create new table with desired schema
2. Copy data from old table
3. Drop old table using safe pattern above
4. Rename new table to original name
Use SQLite debugging features to identify lock problems:
Enable SQLite trace logging:
# 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)
});Check lock status (SQLite 3.32.0+):
-- 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;Common debugging queries:
-- 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;Application-level monitoring:
// 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;
}
}### SQLITE_LOCKED vs SQLITE_BUSY
SQLITE_LOCKED indicates conflicts *within* a single connection or shared cache:
- Multiple statements on same connection
- Shared cache mode conflicts
- Connection attempting to escalate its own lock
SQLITE_BUSY indicates conflicts *between* different connections:
- Multiple processes/threads with separate connections
- File-level locking conflicts
- Different applications accessing same database file
### Shared Cache Mode Deep Dive
Shared cache mode allows multiple database connections within the same process to share a single cache. This can improve performance but introduces SQLITE_LOCKED risks:
When to use shared cache:
- Multiple read-only connections to same database
- Connection pooling within single application
- Temporary databases with short-lived connections
When to avoid shared cache:
- Mixed read/write workloads
- Long-running transactions
- Multiple threads with independent write operations
- DROP TABLE/ALTER TABLE operations
### Virtual Tables and Extensions
Some SQLite extensions and virtual tables may hold locks unexpectedly:
Common culprits:
- Full-text search (FTS) tables
- JSON1 and RTREE extensions
- Custom virtual tables with improper lock handling
- Extensions that don't properly finalize prepared statements
Mitigation:
- Test extensions in isolation
- Check extension documentation for lock behavior
- Consider disabling extensions if lock issues persist
### Connection Pooling Strategies
For high-concurrency applications:
Option 1: Dedicated connections per thread
- Each thread gets its own connection
- No shared cache, no SQLITE_LOCKED between threads
- Higher memory usage
Option 2: Connection pool with isolation
- Pool manages connection lifecycle
- Each checkout gets exclusive access
- Statements properly finalized before returning to pool
Option 3: Serialized access pattern
- Single writer thread for all modifications
- Multiple reader threads for queries
- Message queue for write operations
### Recovery from Stuck Locks
If SQLITE_LOCKED persists despite fixes:
1. Graceful shutdown:
- Close all database connections
- Wait for all threads to complete
- Reopen connections fresh
2. Force cleanup (risky):
# Remove lock files (WAL mode)
rm -f database.db-wal database.db-shm
# Backup and restore
sqlite3 database.db ".backup recovery.db"
mv database.db corrupted.db
mv recovery.db database.db3. SQLite emergency mode:
PRAGMA writable_schema = ON;
-- Manual schema repair
PRAGMA writable_schema = OFF;Warning: Force cleanup can cause data loss. Always backup first.
### Performance Considerations
- WAL mode reduces lock contention but has its own considerations
- Proper indexing reduces query time and lock duration
- Smaller transactions release locks faster
- Connection pooling with proper isolation prevents lock buildup
### Testing Strategies
1. Concurrency tests: Simulate multiple threads/processes
2. Stress tests: High load with mixed operations
3. Recovery tests: Application crash during transactions
4. Long-running tests: Memory leaks and lock accumulation
SQLITE_BUSY: The database file is locked
How to fix 'SQLITE_BUSY: The database file is locked' in SQLite
better-sqlite3: This statement has already been finalized
How to fix "better-sqlite3: This statement has already been finalized" in SQLite
SQLITE_AUTH: Authorization denied
SQLITE_AUTH: Authorization denied
SQLITE_CONSTRAINT_CHECK: CHECK constraint failed
CHECK constraint failed in SQLite
SQLITE_READONLY_RECOVERY: WAL mode database cannot be modified
How to fix "SQLITE_READONLY_RECOVERY: WAL mode database cannot be modified" in SQLite