On 2017-09-19, at 21:59, Goffi <go...@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 _______________________________________________ Twisted-Python mailing list Twisted-Python@twistedmatrix.com https://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python