sqlite thread issue causes fatal error for authentications

[federicoceratto]

sqlite thread issue causes fatal error for authentications

[FedericoCeratto]

You are running your application with Paste, which is creating a new thread each time a request comes in:

@bottle.route('/') 
def root(): 
    t = threading.current_thread() 
    return "Current thread: %s" % t.ident 

[FedericoCeratto]

Related to https://sqlite.org/compile.html#threadsafe
Assuming sqlite is compiled with thread safety "1":

assert sqlite3.threadsafety == 1

...the connection should be set up with:

self._connection = sqlite3.connect(self._filename, check_same_thread=False)

[FedericoCeratto]

Stress-testing this solution with multiple workers under gunicorn led to DB access contention errors: OperationalError: database is locked
I wonder if it's worth implementing code to use sqlite3 in a multithreaded fashion, or rather switch to bigger databases.

[cscortes]

Ouch. Here is the deal though. Bottle is suppose to be an "easy" micro-framework. At some time someone might just try the path, from json to sqlite cause it is a step up in complexity without getting hard.

Let me think out-loud for a minute:

I am disappoint not that it gave an error, but that after a little googling, I still didn't have a clue of what the answer could be. It seems illogical to me that "just changing the server" would cause such a fatal error.

Thinking about this issue though, is there anything in the big dbs that would medigate this issue better? Is it worth the complex setup of mysql, mariasql, postgres, etc for someone that has a simple site?

DB lockup means that 2 threads are accessing the same table, that is a problem you would have in any db. Correct me if I am wrong, but this is normally handled by commits, and rollback actions -- right ? whether is is sqlite, postgres, mysql, etc.

If so, the class will have this problem with any db.

[FedericoCeratto]

To clarify: by default, the sqlite3 library refuses to share the same connection across different threads. This is not an issue as long as the application is served from a single-thread, which is usually the case for small sites, e.g. if you use the Bottle built-in server.
Paste, on the other hand, starts multiple threads. Since sqlite3 is thread-safe, it is possible to disable the "check_same_thread" safety measure, and the application worked in my tests.

The "OperationalError: database is locked" was triggered by heavy load testing done with "ab" using 10 concurrent workers and after running few hundreds queries. (This is not representative of the usage pattern of a "small" website.)
The problem is related to sqlite3, not Bottle nor Cork, and could be due to the way a backlog of queries in handled.

Maybe it could be possible to mitigate the issue by running explicit transactions (but it might not be worth the effort)
This could be relevant: http://beets.radbox.org/blog/sqlite-nightmare.html

[cscortes]

Okay, so you are saying that for small dbs, your answer is fine (and my problem is solved).

But, for some heavy traffic sites, it might require transactions. Well, could this be a place to create a transaction proxy class?

Some of the code is written on the beats site and open sourced. Is it possible just to wrap the api of each db in a proxy class with a tranaction??

Will this solve some of the other issues with non-syncronized backends like json?
+

[cscortes]

Sorry, didn't see this issue was still opened. The answer is use a thread safe database with bottle, gotcha. Thanks for the feedback.