March 23, 2006

CherryPy, multi-threading, SQLite

Quick tip: I’m using SQLite, at least for development, on a Python web application. I wanted to use a CHECK constraint, which is only supported in SQLite 3.3 or newer. My box is CentOS 4, which has SQLite 3.2.x.

Now, FC5 has an SQLite 3.3.3 (I think) RPM. I rebuilt this, installed it. Now my server won’t serve a page that queries the database; it just locks up. I find that pysqlite (RH/FC package python-sqlite) says you really need 1.1.7 to use SQLite 3.3.x. So I try to rebuild the python-sqlite package with pysqlite 1.1.7 (there’s a new major version available, but I’m not that daring by far). When the package build gets to the tests, it locks up too.

Then I read that there are threading problems with SQLite 3.3 that are fixed in SQLite 3.3.4. So I take the FC5 SQLite 3.3.3 SRPM and rebuild it with SQLite 3.3.4 without complications. Now I try the python-sqlite 1.1.7 rebuild again. Same lock up.

Finally I see that, although there is a bug and a line in the %changelog section saying that --enable-threadsafe is now passed to configure, SQLite is not being compiled with -DTHREADSAFE=0. --enable-threadsafe only appears in the RPM change log. I put --enable-threadsafe after %configure, rebuild and install SQLite 3.3.4 RPM, then rebuild python-sqlite 1.1.7 and it works swimmingly.

Moral of the story: to do anything multi-threaded with SQLite,

  • Use SQLite 3.3.4 or newer
  • Use pysqlite 1.1.7 or newer (I think there may have been a release of the 2.0.x and 2.1.x branches around the same time, presumably to fix similar problems with SQLite 3.3.x)
  • Make sure SQLite is being built with --enable-threadsafe