When the developer of a justifiably famous SQL engine whacks himself on the head and tells you to get the fix, you’d be wise to do what you’re told.

SQLite is well-known for its small size, SQL92 compatibility, ease of use, licensing… as in whatever… and price… as in free. It’s used in many commercial products, like the X Multimedia System (XMMS), DragonDB, and Core Data (which Apple ships with its Tiger OS).

The lite in SQLite is a bit of a misnomer. Vivisimo, the producers of Clusty the search engine, and a company that services sites that manage millions of documents, is a user. In fact, SQLite version 3 can grapple with databases as large as 2 terabytes. That’s pretty heavy in most folks’ books.

So what’s the problem?

In a word, multithreading. It hasn’t been SQLite’s strong suit.

Last March, Kervin Pierre, project manager for Open Connector, the open-source Microsoft Outlook connector project, expressed the frustration of many SQLite multithreading implementors, when he wrote:

I’ve been pulling my hair out over concurrency problems with SQLite 3. SQLITE_BUSY, SQLITE_SCHEMA, SQLITE_MISUSE, I get them all.

Over the last few weeks, I’ve been strictly debugging SQLite problems in my application. No program logic besides fixing ( and apparently creating in the process ) SQLite errors.

Vivisimo’s Chris Palmer gave a more detailed description of his multithreading challenges, outlining a problem with lock contention:

I am running version 3.2.1 of sqlite and I have applied the patch to fix a previously reporting multithreading problem (to do with the global recover) …

I have a multithreaded (2 threads) application that does a large number of tiny updates (each update is a seperate transaction). After running for awhile the application ends up getting stuck with both threads unable to acquire a lock on the database.

That is, one thread acquires a shared lock, another thread piggy backs on this shared lock and then eventually the second thread (which did not itself do the file locking for the shared lock) tries to change the lock which is not valid.

Until now, SQLite’s creator, D. Richard Hipp, has provided scholarly descriptions of how to work with locking and multithreading properly. To safely multithread, Hipp wrote:

  1. Make sure you’re compiling SQLite with -DTHREADSAFE=1.
  2. Make sure that each thread opens the database file and keeps its own sqlite structure.
  3. Make sure you handle the likely possibility that one or more threads collide when they access the db file at the same time: handle SQLITE_BUSY appropriately.
  4. Make sure you enclose within transactions the commands that modify the database file, like INSERT, UPDATE, DELETE, and others.

In addition, when multithreading while using temporary tables, Hipp noted, “The main database is not locked, so, for instance, one thread can do read operations on the temporary table at the same time as another thread is doing write operations on a table in the main database. This feature can often be used to great advantage when having multithreaded access to the database. By creating a temporary table containing the results of a large query for processing, rather than processing it directly out of the main database, you greatly reduce lock contentions.”

When the whole backend was re-written for SQLite version 3.3.0 (released 11 January 2006), many hoped the multithreading problems were behind them. They were wrong.

Exactly one month later, the author issued SQLite version 3.3.4, along with a terse, but telling statement: “This release fixes several bugs, including a blunder that might cause a deadlock on multithreaded systems. Anyone using SQLite in a multithreaded environment should probably upgrade.”

So what are you waiting for? Do what the man says.

fyi: There are versions of SQLite version 3 for most operating systems, including BSD, Linux, OSX, Windows, etc. In addition, wrappers are available to interface with Java, Perl, PHP, Python, Ruby on Rails and many other languages. If you prefer working from the command prompt, you can do that, too. Right out of the box… But of course, there is no box.