[Twisted-Python] How to correctly run Sqlite with Twisted?

[ex vito]

On 2017-09-19, at 21:59, Goffi <goffi at goffi.org> wrote:

> I'm using Sqlite3 module through Twisted's enterpirse.adbapi, I create the 
> ConnectionPool instance like this:
> 
>   self.dbpool = ConnectionPool("sqlite3", db_filename, 
> check_same_thread=False)
> 
> You can see the code at https://repos.goffi.org/sat/file/tip/src/memory/sqlite.py
> 
> Sometime, the writing is failing with following exception:
> 
>  Failure instance: Traceback: <class 'sqlite3.OperationalError'>: database is 
> locked
> 
> So I wonder if the database is correctly used, did anybody experienced 
> something similar with Twisted and Sqlite ?
> 
> Should I just augment timeout as advised at https://stackoverflow.com/a/
> 8618328? Looks like more a workaround than a clean solution.
> 
> Python 2 documentation doesn't talk about check_same_thread argument, but 
> Python 3 at https://docs.python.org/3.5/library/sqlite3.html#sqlite3.connect 
> says that writing operation should be serialized by the user (I thought it was 
> the default as said in https://sqlite.org/threadsafe.html), how should I 
> achieve that?
> 
> Also PRAGMA are not working (specially "PRAGMA foreign_keys = ON"), I guess 
> because of multi-threading, what is the good way to activate foreign_keys for 
> all following request?


I would subscribe to Moshe's and Jean-Paul's suggestions of using higher-level tools to handle database interactions. If, however, you'd rather continue with the adbapi approach, here are a few tips that may help your diagnostic, given that behavior you are observing seems to result from multi-threaded/multi-process interactions:

- Confirm you have no other SQLite client at play, locking your data somehow.
- Correct serialization of independent SQL queries/transactions may be a complex topic.
- But you can try forcing the ConnectionPool thread count to 1 via the cp_min/cp_max init args.
- For your PRAGMA statements you should probably use the cp_openfun ConnectionPool init arg.
- Using the undocumented "check_same_thread" on Python 2 seems pointless.
  (not sure it is, though; I did not look at the underlying Python 2 stdlib code)

Lastly:

I have experienced issues with SQLite3 + Python on one occasion in the past: depending on the platform, SQLite itself is/is-not distributed with Python, IIRC; for diagnostics, confirm the exact version of the sqlite .SO / .DLL in use and, ideally, its compile time options. I recall my troubles were between CentOS 5 vs. 6, due to the specific SQLite versions included in each distribution.

A final thought, from the realm of intuition: if I were targeting heavily concurrent access to a database (subjective, yes), I'd consider replacing SQLite with some other engine. SQLite is a great tool for many purposes, but concurrent access is not its strong point (see https://www.sqlite.org/lockingv3.html).

Cheers,
--
exvito