Sqlite Pool does not need split

[markazmierczak]

Good news:

You might not need a split of readers and writers for SQLite as proposed in #459.

If you do that, you will hinder the life of developer greatly and there is a better way.

There are two errors people run into often with SQLite:

• SQLITE_BUSY
• SQLITE_BUSY_SNAPSHOT (especially in WAL mode)

And both of them can be easily avoided without the split.

SQLITE_BUSY can be avoided by:

• using busy_timeout
• resetting prepared statement after use

SQLITE_BUSY_SNAPSHOT happens when transaction starts in DEFERRED mode with simple BEGIN - becomes "reader" - and later tries to upgrade to "writer" while another thread already became "writer" but waits until "reader" finish.

And SQLite will detect this deadlock and will cause the first thread to error out. This is explained in detail by SQLite Isolation article.

Often the solution is to use BEGIN IMMEDIATE for such transactions to become "writer" from the beginning.

If you choose this path, you are free from locking problems.

Bad news:

SQLite support in SQLx has fundamental things to fix, unfortunately.

• prepared statements are not reset after use
• the use of SqliteRow is not constrained by any lifetime leading to SQLite: Spurrious SEGFAULTs while reading Strings #634

On the other hand, Diesel is doing things just right. Every prepared statement is reset just after the use ends and the lifetime of the row is constrained.
Refer to StatementUse struct.

I prepared two Gists to show the problem:

• Diesel Gist
• SQLx Gist

Diesel runs fine while SQLx struggles.

I prepared two pull-requests to make the situation better (but still not ideal).

• a fix for fetch_optional in PR852 (this PR also fixes Error "database table is locked" when using fetch_one with sqlite executor #662).
• a support for BEGIN IMMEDIATE and BEGIN EXCLUSIVE in PR851

I have also added support for synchronous setting in PR850 (it is common to set it to NORMAL with WAL which greatly improves performance).

PS: My personal opinion is that many would benefit from proper guidance on common defaults (journal_mode=WAL + BEGIN IMMEDIATE + synchronous=NORMAL). It is not understood widely, especially for people new to SQLite.

[abonander]

Closing as the SQLite driver has been rearchitected since this issue was opened.