SqliteQ

The playhouse.sqliteq module provides a subclass ofSqliteExtDatabase, that will serialize concurrent writes to aSQLite database. SqliteQueueDatabase can be used as a drop-inreplacement for the regular SqliteDatabase if you want simpleread and write access to a SQLite database from multiple threads.

SQLite only allows one connection to write to the database at any given time.As a result, if you have a multi-threaded application (like a web-server, forexample) that needs to write to the database, you may see occasional errorswhen one or more of the threads attempting to write cannot acquire the lock.

SqliteQueueDatabase is designed to simplify things by sending allwrite queries through a single, long-lived connection. The benefit is that youget the appearance of multiple threads writing to the database withoutconflicts or timeouts. The downside, however, is that you cannot issuewrite transactions that encompass multiple queries – all writes run inautocommit mode, essentially.

Note

The module gets its name from the fact that all write queries get put intoa thread-safe queue. A single worker thread listens to the queue andexecutes all queries that are sent to it.

Transactions

Because all queries are serialized and executed by a single worker thread, itis possible for transactional SQL from separate threads to be executedout-of-order. In the example below, the transaction started by thread “B” isrolled back by thread “A” (with bad consequences!):

• Thread A: UPDATE transplants SET organ=’liver’, …;
• Thread B: BEGIN TRANSACTION;
• Thread B: UPDATE life_support_system SET timer += 60 …;
• Thread A: ROLLBACK; – Oh no….

Since there is a potential for queries from separate transactions to beinterleaved, the transaction() andatomic() methods are disabled on SqliteQueueDatabase.

For cases when you wish to temporarily write to the database from a differentthread, you can use the pause() andunpause() methods. These methods block thecaller until the writer thread is finished with its current workload. Thewriter then disconnects and the caller takes over until unpause is called.

The stop(), start(),and is_stopped() methods can also be used tocontrol the writer thread.

Note

Take a look at SQLite’s isolationdocumentation for more information about how SQLite handles concurrentconnections.

Code sample

Creating a database instance does not require any special handling. TheSqliteQueueDatabase accepts some special parameters which youshould be aware of, though. If you are using gevent, youmust specify use_gevent=True when instantiating your database – this wayPeewee will know to use the appropriate objects for handling queueing, threadcreation, and locking.

from playhouse.sqliteq import SqliteQueueDatabase
 
db = SqliteQueueDatabase(
    'my_app.db',
    use_gevent=False,  # Use the standard library "threading" module.
    autostart=False,  # The worker thread now must be started manually.
    queue_max_size=64,  # Max. # of pending writes that can accumulate.
    results_timeout=5.0)  # Max. time to wait for query to be executed.

If autostart=False, as in the above example, you will need to callstart() to bring up the worker threads that willdo the actual write query execution.

@app.before_first_requestdef _start_worker_threads():    db.start()

If you plan on performing SELECT queries or generally wanting to access thedatabase, you will need to call connect() andclose() as you would with any other database instance.

When your application is ready to terminate, use the stop()method to shut down the worker thread. If there was a backlog of work, thenthis method will block until all pending work is finished (though no new workis allowed).

import atexit
 
@atexit.register
def _stop_worker_threads():
    db.stop()

Lastly, the is_stopped() method can be used todetermine whether the database writer is up and running.