Ooops w/ SQLite & threading

[Emmanuel Blot]

I've upgraded from [1954] to [2011], and I got this error three times in a 10 minutes time frame. I've restarted the Apache server (2.0.54, worker) each time, but this problem randomly occurs ;-(

Server runs on a Linux Debian, and I had to upgrade the Trac SQLite DB version (sqlite2 .dump, sqlite3 .read) after the last aptitude upgrade - I don't know whether the problem is related to this upgrade, as I done the system upgrade and the Trac upgrade one after the other.

Oops...

Trac detected an internal error:

SQLite objects created in a thread can only be used in that same thread.The object was created in thread id 1101532080 and this is thread id 1269304240
Traceback (most recent call last):
  File "/local/engine/trac/trac/web/modpython_frontend.py", line 274, in handler
    dispatch_request(mpr.path_info, mpr, env)
  File "/local/engine/trac/trac/web/main.py", line 434, in dispatch_request
    db.close()
  File "/local/engine/trac/trac/db.py", line 89, in close
    self.__pool._return_cnx(self.cnx)
  File "/local/engine/trac/trac/db.py", line 134, in _return_cnx
    cnx.rollback()
ProgrammingError: SQLite objects created in a thread can only be used in that same thread.The object was created in thread id 1101532080 and this is thread id 1269304240

[Emmanuel Blot]

It seems that a temporary workaround is to replace the Apache2 threaded-model with the Apache2 traditional-model (perchild process instead of threads).

[Christian Boos]

Related to #1721, which was also "fixed" by avoiding the use of threads (fix that accidentally slipped in r1875)

[Jonas Borgström]

It is probably pysqlite2 that doesn't play very well with our new connection pool.

I'm not sure, but I believe pysqlite is being a bit too overprotective. I think it's perfectly alright to create a connection from one thread and then later use the same connection from another thread, as long as at most one thread is using a single connection at the same time. If this isn't the case than it would be impossible to use connection pools.

Please backup of your database and try this patch:

Index: trac/db.py
===================================================================
--- trac/db.py  (revision 1998)
+++ trac/db.py  (working copy)
@@ -189,7 +189,7 @@
             global sqlite
             sqlite.register_converter('text', str)
             cnx = sqlite.connect(path, detect_types=sqlite.PARSE_DECLTYPES,
-                                 timeout=timeout)
+                                 check_same_thread=False, timeout=timeout)
         else:
             import sqlite
             cnx = sqlite.connect(path, timeout=timeout)

[Emmanuel Blot]

As this DB is used by many people, and I'm concerned with its integrity: if the thread check is disabled, could the DB end corrupted at some point ?

[Christopher Lenz]

AFAICT, jonas is right that an SQLite connection can be used by multiple threads as long as no two threads use the same connection at the same time. See for example the ​SQLite FAQ or this ​thread on the CherryPy mailing list. Note however the SQLite must have been built with the THREADSAFE option enabled, which should be the case by default on any platform that uses a threaded MPM in Apache (i.e. Windows).

Anyway, I've been investigating this problem today and have come up with a couple of changes that fix it, at least for me. I've reenabled the ThreadingMixIn in tracd, and run ApacheBench with a concurrency level of 20 concurrent requests without seeing an error, at least. I'll attach a patch so that you can try it out.

[Christopher Lenz]

Per-thread DB connections

[Christopher Lenz]

This patch basically remembers which connections were handed out to which threads, and if a thread requests a connection more than once, it'll always get back the same connection.

Also, it sets the check_same_thread parameter to False as suggested by jonas above.

[Christopher Lenz]

Same patch as above, but with a huge amount of debugging output

[Christopher Lenz]

The second patch I attached contains the same changes as the first, but includes a ton of debugging output to stdout. It's intended for use with tracd. If you still encounter problems after applying the first patch, please try the second and check the output.

[Emmanuel Blot]

Ok Chris,

I'll try it out on Monday, as I'm on vacation tomorrow. Thanks for your help.

[Christian Boos]

I tried the first patch, on Windows, pysqlite1, tracd:

• I didn't get crashes (as in #1721) anymore
• I didn't even get my usual crashes related to SVN!!! Maybe I didn't try hard enough… (it's late) but how could this patch have increased the robustness on the SVN bindings side?

Anyway, it seems to be a good fix.

[PBruin]

I also tried the patch with ref to #1729.

• No more crashes!
• I was always able to get a crash without fail, now I can hit the reload button on 3 simultanious RSS feeds many times without problems.

Thank you very much, this is a big relief for me.

[Christopher Lenz]

Patch checked in in [2013].