SQLite’s shared-cache mode is the surprising exception to the rule that database connections are strictly isolated, allowing them to pool resources and reduce overhead.
Let’s see it in action. Imagine two separate Python scripts, each with its own connection to the same SQLite database file, my_database.db.
Script 1 (Writer):
import sqlite3
import time
conn1 = sqlite3.connect('my_database.db')
conn1.execute("PRAGMA shared_cache = ON;") # Enable shared cache
print("Writer: Connected and shared cache enabled.")
try:
conn1.execute("CREATE TABLE IF NOT EXISTS items (id INTEGER PRIMARY KEY, name TEXT)")
conn1.execute("INSERT INTO items (name) VALUES (?)", ("widget",))
conn1.commit()
print("Writer: Inserted 'widget'.")
time.sleep(5) # Hold the write lock
except sqlite3.Error as e:
print(f"Writer Error: {e}")
finally:
conn1.close()
print("Writer: Connection closed.")
Script 2 (Reader):
import sqlite3
import time
conn2 = sqlite3.connect('my_database.db')
conn2.execute("PRAGMA shared_cache = ON;") # Enable shared cache
print("Reader: Connected and shared cache enabled.")
time.sleep(1) # Give writer a head start
try:
cursor = conn2.cursor()
cursor.execute("SELECT name FROM items WHERE id = 1")
row = cursor.fetchone()
if row:
print(f"Reader: Found '{row[0]}'.")
else:
print("Reader: 'widget' not found yet.")
except sqlite3.Error as e:
print(f"Reader Error: {e}")
finally:
conn2.close()
print("Reader: Connection closed.")
When you run these scripts concurrently (e.g., open two terminals and execute them), you’ll observe that Script 2, the reader, will wait until Script 1, the writer, releases its lock after 5 seconds. Without shared_cache = ON, Script 2 might be able to read stale data or even encounter errors depending on the exact timing and SQLite version, as it wouldn’t be aware of the ongoing write operation. With shared_cache = ON, both connections are aware of each other’s operations on the same database file.
The core problem shared-cache solves is the overhead associated with multiple independent SQLite connections to the same database file. Each connection, by default, maintains its own cache of database pages. When one connection modifies a page, it might invalidate that page in the cache of other connections. This leads to:
- Increased I/O: Other connections might have to re-read pages from disk if their cache is invalidated, even if the data they need hasn’t actually changed from their perspective.
- Cache Inconsistency Issues: While SQLite has mechanisms to prevent corrupted reads, managing separate caches for the same data can be complex and less efficient.
- Higher Memory Usage: Each connection holding its own cache consumes more RAM.
shared-cache addresses this by enabling a single, shared cache for all connections that have it enabled for a particular database file. When one connection modifies a page, that change is immediately reflected in the shared cache, and other connections accessing that same page will see the updated data without needing to re-read from disk. This also means that locks are managed more cohesively across connections.
The primary lever you control is the PRAGMA shared_cache = ON; statement. This pragma must be executed by every connection that intends to participate in the shared cache for a given database file. If even one connection to a database file does not have shared_cache enabled, then no sharing occurs for that file. The connections are typically established within the same process, though it is possible to use shared memory to achieve this across processes (requiring additional setup).
Crucially, shared-cache mode is not just about sharing data pages; it’s also about sharing the database locking mechanism. When shared-cache is enabled, all connections participating in the shared cache will contend for a single set of database-level locks. This means that a write operation by one connection will block all other connections (whether read or write) that are also using shared-cache for that database file, until the write is committed or rolled back. This is in contrast to the default mode where write locks are typically at the table or row level and might not block readers as aggressively.
The most surprising aspect for many is that shared-cache is not enabled by default, even when multiple connections are opened from the same process. You must explicitly enable it for each connection. Furthermore, enabling shared-cache can sometimes lead to increased contention if you have many connections performing frequent, short-lived writes, as they will all be waiting for the single database-level write lock.
The next logical step in optimizing SQLite performance is understanding how to manage transactions effectively to minimize lock contention.