Threading/Pooling Behavior
Pysqlite’s default behavior is to prohibit
the usage of a single connection in more than one thread. This is
originally intended to work with older versions of SQLite that did not
support multithreaded operation under various circumstances. In
particular, older SQLite versions did not allow a :memory: database to
be used in multiple threads under any circumstances.
Pysqlite does include a now-undocumented flag known as
check_same_thread
which will disable this check, however note that
pysqlite connections are still not safe to use in concurrently in
multiple threads. In particular, any statement execution calls would
need to be externally mutexed, as Pysqlite does not provide for
thread-safe propagation of error messages among other things. So while
even :memory:
databases can be shared among threads in modern SQLite,
Pysqlite doesn’t provide enough thread-safety to make this usage worth
it.
SQLAlchemy sets up pooling to work with Pysqlite’s default behavior:
When a :memory:
SQLite database is specified, the dialect by default
will use SingletonThreadPool
. This pool maintains a single connection
per thread, so that all access to the engine within the current thread
use the same :memory:
database - other threads would access a
different :memory:
database.
When a file-based database is specified, the dialect will use NullPool
as the source of connections. This pool closes and discards
connections which are returned to the pool immediately. SQLite
file-based connections have extremely low overhead, so pooling is not
necessary. The scheme also prevents a connection from being used again
in a different thread and works best with SQLite’s coarse-grained file
locking.
Using a Memory Database in Multiple Threads
To use a :memory:
database
in a multithreaded scenario, the same connection object must be shared
among threads, since the database exists only within the scope of that
connection. The StaticPool
implementation will maintain a single
connection globally, and the check_same_thread
flag can be passed to
Pysqlite as False
:
from sqlalchemy.pool import StaticPool
engine = create_engine('sqlite://',
connect_args={'check_same_thread':False},
poolclass=StaticPool)
Note that using a :memory:
database in multiple threads requires a recent version of SQLite.
本文介绍了SQLAlchemy在SQLite数据库中的线程池行为。在单线程模式下,所有连接将由同一个线程处理;在多线程模式下,每个线程都将有自己的连接。此外,当使用共享内存(例如::memory:)时,线程安全性会受到限制。