SQLite async connection pool for high-performance

> The primary challenge with SQLite in a concurrent environment (like an asyncio web application) is not connection time, but write contention. SQLite uses a database-level lock for writes. When multiple asynchronous tasks try to write to the database simultaneously through their own separate connections, they will collide. This contention leads to a cascade of SQLITE_BUSY or SQLITE_LOCKED errors.

I really don't get it. How would this help?

The benchmarks dont mention which journal mode sqlite is configured as, which is very suspicious as that makes a huge difference under concurrent load.

Sharing one SQLite connection across the process would necessarily serialize all writes from the process. It won't do anything for contention with external processes, the writes within the process wouldn't be concurrent any more.

Basically, it adds its own write lock outside of SQLite, because the pool can implement the lock in a less annoying way.

I don't understand, all writes to a single sqlite DB are going to be serialized no matter what you do.

> Basically, it adds its own write lock outside of SQLite, because the pool can implement the lock in a less annoying way.

Less annoying how? What is the difference?

When your program does heavy concurrent writing and opens/closes connections for each write, most of them will fail with SQLITE_BUSY or SQLITE_LOCKED errors.

This situation can be managed with a pool of small (5 connections or less) to prevent spawning too many connections. This will reduce racing between them and allow write operations to succeed.

SQLite's lock is blocking, with a timeout that aborts the transaction. An async runtime can have a non-blocking lock that allows other tasks to proceed in the meantime, and is able to wait indefinitely without breaking transactions.

What's the benefit of this over just doing PRAGMA busy_timeout = 0; to make it non-blocking ?

After all, as far as i understand, the busy timeout is only going to occur at the beginning of a write transaction, so its not like you have to redo a bunch of queries.