SQLite “Database is Locked” Error on CIFS Network Mounts: Causes and Solutions

Understanding SQLite Locking Behavior on CIFS/SMB Network Filesystems

Network File System Locking Fundamentals

The "Runtime error: database is locked (5)" error during SQLite operations on CIFS-mounted directories stems from fundamental incompatibilities between SQLite’s locking requirements and network filesystem semantics. SQLite implements strict concurrency control through file locking primitives that assume POSIX-compliant filesystem behavior. When operating on Common Internet File System (CIFS) mounts – particularly in Linux environments – these assumptions frequently break due to differences in byte-range locking implementations and mandatory vs advisory lock handling.

CIFS/SMB implementations often enforce mandatory locking at the server level, whereas SQLite expects advisory locking semantics. This creates race conditions where the CIFS server rejects lock acquisition attempts that SQLite believes should succeed. The conflict manifests most acutely during database creation and schema modification operations like CREATE TABLE, as these require exclusive access to multiple database file regions simultaneously.

The locking protocol involves three distinct control mechanisms:

1. Shared lock (SHM): Acquired during read operations
2. Reserved lock: Obtained when preparing to write
3. Pending lock: Activated during lock escalation

CIFS implementations frequently mishandle these layered locks due to incomplete support for POSIX-style fcntl() locking commands. Network latency exacerbates the problem by extending lock hold times beyond SQLite’s default timeout thresholds.

CIFS Mount Configuration Pitfalls

Common misconfigurations in CIFS mounting parameters directly contribute to SQLite locking failures:

1. Missing or Incorrect nobrl Option:
The CIFS nobrl (no byte range locks) mount parameter disables client-side locking requests. While this superficially resolves immediate locking errors by bypassing lock coordination, it violates SQLite’s fundamental concurrency requirements. Databases modified without proper locking become vulnerable to corruption from concurrent writes.

2. UID/GID Mapping Mismatches:
Using numeric UID/GID values (via uid=/gid= mount options) that don’t match the server-side access control lists creates permission conflicts during lock file operations. SQLite creates several auxiliary files (.db-shm, .db-wal) with ownership derived from the mount parameters, which may not align with CIFS server permissions.

3. SMB Protocol Version Incompatibilities:
Older SMBv1 configurations lack proper support for persistent file handles and oplocks (opportunistic locks), causing unexpected connection drops during multi-step transactions. Modern SQLite versions require SMBv2+ for stable operation.

4. Case Sensitivity Conflicts:
CIFS mounts configured with case-insensitive file handling (common in Windows-oriented deployments) collide with SQLite’s case-sensitive file operations, particularly when WAL (Write-Ahead Logging) mode generates temporary files with mixed-case extensions.

5. Inode Caching Behaviors:
Aggressive client-side caching of file metadata (strictcache, cache=strict) leads to stale file size and lock state information. SQLite relies on accurate real-time file size reporting for WAL mode operation – cached values cause "database is locked" errors when the actual on-disk state diverges.

Resolving CIFS Mount Conflicts for SQLite Operations

Step 1: Validate CIFS Mount Configuration
Execute mount | grep cifs to verify current mount options. Critical parameters for SQLite compatibility:

• vers=3.0: Enforce SMBv3 protocol (or highest supported by server)
• cache=none: Disable client-side caching of file metadata
• nostrictsync: Bypass strict sync operations (use with caution)
• dir_mode=0775,file_mode=0775: Explicit permission settings
• nobrl: Temporary testing only – see warnings below

Example remediation mount command:

sudo mount -t cifs -o username=USERNAME,password=PASSWORD,vers=3.0,\
cache=none,dir_mode=0775,file_mode=0775,noserverino,nostrictsync \
//NAS_IP/sharename /mnt/mountpoint

Step 2: Diagnose Lock Acquisition Failures
Use strace to trace system calls during SQLite operations:

strace -e trace=file,fcntl -o sqlite_trace.log \
sqlite3 /mnt/cifs_mount/test.db \
"create table test(id integer); select * from test;"

Analyze the log for failed fcntl() operations:

fcntl(3, F_SETLK, {l_type=F_RDLCK, l_whence=SEEK_SET, l_start=1073741824, l_len=1}) = -1 EAGAIN (Resource temporarily unavailable)  

The l_start offset indicates which lock region failed:

• 1073741824 (0x40000000): SHARED_LOCK position
• 1073741825 (0x40000001): RESERVED_LOCK
• 1073741826 (0x40000002): PENDING_LOCK

Step 3: Implement Safe Workarounds

A. Local Creation with Delayed Transfer
Create and populate databases on local storage before transferring to CIFS mount:

# Create DB locally
sqlite3 /tmp/local.db "create table t(id integer); insert into t values(1);"

# Verify journal mode
sqlite3 /tmp/local.db "pragma journal_mode=delete;"

# Copy to CIFS mount (single atomic operation)
cp /tmp/local.db /mnt/cifs_mount/production.db

B. SQLite Configuration Tuning
Set aggressive retry parameters in sqlite3 shell:

.timeout 30000  -- Increase lock wait timeout to 30 seconds
.bail on        -- Stop on first error for clearer diagnostics

C. WAL Mode Considerations
While WAL mode reduces lock contention on local filesystems, it exacerbates problems on CIFS due to additional .db-wal file handling. Explicitly disable WAL:

pragma journal_mode=DELETE;  

D. Server-Side Configuration Changes
For environments with control over the CIFS/SMB server:

1. Enable SMBv3 leases (smb.conf):

[global]
smb2 leases = yes

2. Disable strict locking enforcement:

[share]
strict locking = no

E. Alternative Filesystem Layers
Consider using a FUSE-based filesystem wrapper like cifs-utils with -o directio to bypass kernel caching layers. Mount with:

sudo mount -t cifs -o username=USER,password=PASS,vers=3.0,directio //server/share /mnt/cifs

Step 4: Validate Locking Behavior
Use smbcacls to inspect CIFS lock status during SQLite operations:

smbcacls -N -U USER //server/share test.db  

Monitor real-time lock activity with smbstatus -L.

Critical Warning on nobrl Usage
While mounting with nobrl appears to resolve immediate errors by disabling byte-range lock requests, this creates race conditions that will corrupt databases under concurrent access. Only use nobrl when:

• The database is read-only
• Strict single-writer enforcement exists
• Regular integrity checks (pragma integrity_check) are implemented

For mission-critical systems, consider alternative storage backends like networked block devices (iSCSI) that provide proper POSIX locking semantics. SQLite performs reliably on iSCSI LUNs formatted with local filesystems (ext4, XFS), as locking occurs at the block device layer rather than through network file protocols.

Persistent "database is locked" errors on CIFS mounts ultimately indicate fundamental protocol incompatibilities. Production systems requiring concurrent SQLite access should migrate to proper database servers (PostgreSQL, MySQL) or adopt network-resistant SQLite wrappers like sqleet with alternative locking implementations.