SQLite offers 3 threading modes:

• Single threaded which is quite self explanatory and doesn't work at all for us
• Serialized, where the SQLite engine will handle serializing accesses to the database itself, meaning all requests will run one after another (which was not successful when tested, but that might be a problem at the time, or an implementation problem)
• Multithread where the application is responsible for orchestrating the requests and ensure that no concurrent write will occur.

The media library uses the later, and has a set of tools to acquire read or write contexts and simplify the accesses to the database from a multi threaded context.

Contexts

Read context

As the name imply, this is a context that must be used when reading from the database.

Multiple read context can exist on multiple threads at once, and multiple read can be executed concurrently.

Write context

Only a single write request can be executed at a given time, and no read requests are allowed during the execution of a write request.

This effectively means that while a write is executed, all other threads will wait for the completion of the request, and for the write context to be released.

Transaction

Transactions will acquire a write context, meaning that only one transaction can exist in a process at a given time. Instantiating a transaction causes a write context to be acquired immediately.

Priority accesses

Read and write contexts do not offer any kind of scheduling or prioritization. This means that a read could potentially wait for many queued writes to proceed, depending on who gets the lock first.

If the request is executed on behalf of the user, chances are that they are expecting to see something displayed, such as listing all known artists, and waiting for background operations to release their context would lead to a poor used experience.

In order to improve this, the media library offers Priority Contexts. If a thread hold a priority context, it will preempt other threads when acquiring a read or write context.

Beware that a priority context is only a way to fasten the acquisition of a context later on, and does not lock anything. Users are still mandated to acquire a read or write context after acquiring a priority context.

Per-thread instances

SQLite multithread mode mandates that all instances related to the database be per-thread.

This means that a connection handle, a statement, or any other type representing something coming from the database must be local to its thread.

In practice, this means that each thread has its own database connection, statement cache, hooks, ...

This complexity is hidden from the user through various utility classes, and one is expected to be able to use the database without this knowledge, however it you know that you'll execute multiple reads in a row, it is advisable to acquire a read context yourself instead of letting the helpers acquire a read context for each request.