SQLite's zero-configuration, single-file nature makes it a popular database choice for deploying lightweight server applications or edge computing services. While it can run without a dedicated server process, managing SQLite connections in multi-threaded or highly concurrent server environments presents a significant practical challenge. To improve performance and control concurrency, developers often use connection pooling to centrally manage SQLite access. However, in actual deployments, combining SQLite with connection pooling is far from as smooth as expected.

Pitfall 1: SQLite does not support concurrent multi-threaded writes by default.

SQLite was originally designed for embedded devices and desktop applications. Its default threading mode is serialized. While multi-threaded access is supported, write operations can still result in lock contention. When using a connection pool, multiple threads attempting to write to the database simultaneously often encounter the following error:

sqlite3.OperationalError: database is locked

This issue may be due to the fact that SQLite is a single-file database, which locks the entire database file during writes. If multiple threads obtain connections from the connection pool and attempt to write simultaneously, write conflicts will occur.

To avoid concurrent writes, design a queue or single-threaded write mechanism. Try configuring WAL (Write-Ahead Logging) mode to improve concurrency.

conn = sqlite3.connect('db.sqlite')
conn.execute('PRAGMA journal_mode=WAL;')

Set the maximum number of concurrent connections (write operations) in the connection pool to 1.

Pitfall 2: The connection pool reuse mechanism can easily reuse closed connections.

In some lightweight frameworks (such as Flask + SQLite + a custom connection pool), developers maintain their own connection pool list, making it easy for connections to be closed but not cleared from the pool. For example, during a query, you might suddenly receive the error:

sqlite3.ProgrammingError: Cannot operate on a closed database.

This is primarily because once a SQLite connection is closed, the object remains in the pool, causing an exception when it is next used to trigger an operation. The solution is to perform a liveness check on each connection retrieved from the connection pool:

try:
conn.execute('SELECT 1')
except sqlite3.ProgrammingError:
conn = sqlite3.connect('db.sqlite')

Use a third-party connection pool library, such as DBUtils, to automatically detect and reestablish connections.

Pitfall 3: Using a global connection object in multiple threads leads to state confusion

To save resources, many developers set SQLite connection objects as global variables and share them across servers. This works fine in a single-threaded model, but can cause confusion in multi-threaded concurrent servers (such as those using ThreadingHTTPServer or FastAPI). Data can be easily overwritten or queries can return unexpected data. SQLite connection objects are not thread-safe unless check_same_thread=False is enabled, which can also lead to unpredictable side effects. In this case, a separate connection is allocated for each thread or request and closed after the request completes.

conn = sqlite3.connect('db.sqlite', check_same_thread=False)

Or use thread-local storage (threading.local()) to isolate connections.

Pitfall 4: Improperly closing connections leads to handle leaks

In high-concurrency servers, connection pools need to control the number of connections. However, if used improperly or exceptions are not caught, connection objects will not be closed, which can lead to a backlog of system resources and ultimately cause "Too many open files" or "Unable to establish another connection."

sqlite3.OperationalError: Unable to open database file

Each connection opened occupies a file handle in the system. If not closed or reused without limit in the pool, resources will be exhausted. It is recommended to use Python context managers:

with sqlite3.connect('db.sqlite') as conn:
# Automatically close after use
conn.execute('SELECT * FROM test')

If using a connection pool wrapper, add a try-finally or automatic cleanup mechanism.

Pitfall 5: Connection pool strategies fail to differentiate between read and write operations

In most SQLite use cases, read operations are frequent and write operations are rare. However, many connection pool implementations fail to differentiate between read and write operations, resulting in frequent write blockage. This can lead to slow query responses and data write failures. Using a single connection pool for all operations doesn't optimize resource allocation. Clearly distinguish between read and write connections, or even create two pools:

read_conn = sqlite3.connect('db.sqlite', uri=True)
write_conn = sqlite3.connect('db.sqlite', uri=True)

Or, depending on the application, implement a read-only replication strategy, periodically synchronizing the write database to a read-only database at the file system level for query services.

Recommended Connection Pool Practices

When deploying SQLite on a server, the connection pool should be configured based on the principles of lightweight, controllable, and isolated. Below is a deployable SQLite connection pool wrapper template:

import sqlite3
from queue import Queue
class SQLitePool:
def __init__(self, db_path, pool_size=5):
self.db_path = db_path
self.pool = Queue(maxsize=pool_size)
for _ in range(pool_size):
conn = sqlite3.connect(db_path, check_same_thread=False)
conn.execute('PRAGMA journal_mode=WAL;')
self.pool.put(conn)
def get_conn(self):
return self.pool.get()
def return_conn(self, conn):
self.pool.put(conn)
def close_all(self):
while not self.pool.empty():
conn = self.pool.get()
conn.close()

Use in server code:

pool = SQLitePool('db.sqlite')
conn = pool.get_conn()
try:
conn.execute('INSERT INTO user VALUES (?, ?)', (1, 'admin'))
conn.commit()
finally:
pool.return_conn(conn)

Although SQLite performs reliably in lightweight server architectures, it is not designed for high-concurrency connection pooling. When implementing a connection pool, developers must pay attention to resource release, connection status determination, and thread isolation.