search

encode / databases

Async database support for Python. 🗄
https://www.encode.io/databases/
BSD 3-Clause "New" or "Revised" License
3.85k stars 262 forks source link

SQLite connection pool isn't #488

Open coderanger opened 2 years ago

coderanger commented 2 years ago

The SQLite dialect appears to open and close connections on every statement. This is causing sqlite3.OperationalError: unable to open database file during periods of heavy write activity (probably because there's a short window after opening a connection when another can't be opened?). Also this just seems wasteful as it throws away the statement cache and generates lots of unneeded I/O load.

MRuecklCC commented 2 years ago

Another issue with the current sqlite behaviour is that when an sqlite://:inmemory: is used, every connection uses a fresh in memory database. This essentially renders the whole sqlite inmemory functionality useless, as the persistent state that should be maintained during the application lifetime just gets truncated.

MRuecklCC commented 2 years ago

Maybe the way to go is to have a single instance of the connection behind an async mutex. Then this connection can be handed out to the users (locking the mutex) and once done put back (unlocking the mutex).

aminalaee commented 2 years ago

I'll try to take a look into this, if you find a solution feel free to create a PR for it though.