←back to thread

SQLite async connection pool for high-performance

(github.com)
197 points slaily | 1 comments | 11 Jul 25 10:26 UTC | HN request time: 0s | source
Show context
bawolff ◴[15 Jul 25 03:01 UTC] No.44567487[source]▶
> 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.

replies(2): >>44569636 #>>44571524 #
1. slaily ◴[15 Jul 25 14:29 UTC] No.44571524[source]▶
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.