Using SQLite and asyncio effectively¶
When using Piccolo with SQLite, there are some best practices to follow.
asyncio => lots of connections¶
With asyncio, we can potentially open lots of database connections, and attempt to perform concurrent database writes.
SQLite doesn’t support such concurrent behavior as effectively as Postgres, so we need to be careful.
One write at a time¶
SQLite can easily support lots of transactions concurrently if they are reading, but only one write can be performed at a time.
SQLite has several transaction types, as specified by Piccolo’s
- class piccolo.engine.sqlite.TransactionType(value, names=None, *, module=None, qualname=None, type=None, start=1, boundary=None)¶
See the SQLite docs for more info.
- deferred = 'DEFERRED'¶
- exclusive = 'EXCLUSIVE'¶
- immediate = 'IMMEDIATE'¶
Which to use?¶
When creating a transaction, Piccolo uses
DEFERRED by default (to be
consistent with SQLite).
This means that the first SQL query executed within the transaction determines whether it’s a READ or WRITE:
READ - if the first query is a
WRITE - if the first query is something like an
If a transaction starts off with a
SELECT, but then tries to perform an
SQLite tries to ‘promote’ the transaction so it can write.
The problem is, if multiple concurrent connections try doing this at the same time, SQLite will return a database locked error.
So if you’re creating a transaction which you know will perform writes, then
from piccolo.engine.sqlite import TransactionType async with Band._meta.db.transaction( transaction_type=TransactionType.immediate ): # We perform a SELECT first, but as it's an IMMEDIATE transaction, # we can later perform writes without getting a database locked # error. if not await Band.exists().where(Band.name == 'Pythonistas'): await Band.objects().create(name="Pythonistas")
IMMEDIATE transactions can exist concurrently - SQLite uses a lock
to make sure only one of them writes at a time.
If your transaction will just be performing
SELECT queries, then just use
DEFERRED transactions - you will get improved performance, as
no locking is involved:
async with Band._meta.db.transaction(): bands = await Band.select() managers = await Manager.select()
It’s recommended to specify the
timeout argument in
DB = SQLiteEngine(timeout=60)
Imagine you have a web app, and each endpoint creates a transaction which runs multiple queries. With SQLite, only a single write operation can happen at a time, so if several connections are open, they may be queued for a while.
timeout it means that queries are less likely to timeout.
To find out more about
timeout see the Python