Module deltachat::sql::pool

source ·
Expand description

§SQLite connection pool.

The connection pool holds a number of SQLite connections and allows to allocate them. When allocated connection is dropped, underlying connection is returned back to the pool.

The pool is organized as a stack. It always allocates the most recently used connection. Each SQLite connection has its own page cache, so allocating recently used connections improves the performance compared to, for example, organizing the pool as a queue and returning the least recently used connection each time.

Pool returns at most one write connection (with PRAGMA query_only=0). This ensures that there never are multiple write transactions at once.

Doing the locking ourselves instead of relying on SQLite has these reasons:

  • SQLite’s locking mechanism is non-async, blocking a thread
  • SQLite’s locking mechanism just sleeps in a loop, which is really inefficient

More considerations on alternatives to the current approach:

We use DEFERRED transactions.

In order to never get concurrency issues, we could make all transactions IMMEDIATE, but this would mean that there can never be two simultaneous transactions.

Read transactions can simply be made DEFERRED to run in parallel w/o any drawbacks.

DEFERRED write transactions without doing the locking ourselves would have these drawbacks:

  1. As mentioned above, SQLite’s locking mechanism is non-async and sleeps in a loop.
  2. If there are other write transactions, we block the db connection until upgraded. If some reader comes then, it has to get the next, less used connection with a worse per-connection page cache (SQLite allows one write and any number of reads in parallel).
  3. If a transaction is blocked for more than busy_timeout, it fails with SQLITE_BUSY.
  4. If upon a successful upgrade to a write transaction the db has been modified, the transaction has to be rolled back and retried, which means extra work in terms of CPU/battery.

The only pro of making write transactions DEFERRED w/o the external locking would be some parallelism between them.

Another option would be to make write transactions IMMEDIATE, also w/o the external locking. But then cons 1. - 3. above would still be valid.

Structs§