SQLite CVSTrac

Page History

I have been working on a Mac OS X project which uses SQLite. At some point I decided to test SQLite's multithreading capabilities, in case I need it someday. I found information scattered around the SQLite group list, but nothing comprehensive. This is the main reason in writing this paper, and of course, to share it with SQLite developers.

Keep in mind, however, that I will have left things out. If you happen to discover something about multithreading that isn't covered here, feel free to add it. Some of the references in this paper are Mac OS X related because this is the environment I've been working on.

Is SQLite thread-safe?

Yes, it is, but be careful.

SQLite multithreading settings

The setting named THREADSAFE turns multithreading on or off. It's turned on by default in the precompiled Windows binaries and it's off by default in the precompiled Linux binaries. Under Linux, Mac OS X and other Unix systems, you'll have to set it manually. If you're using Mac OS X's Project Builder, you can easily turn on multithreading by adding -DTHREADSAFE=1 to the Other C Compiler Flags field, in the following panel:

Project:Edit active target '<your project>':Settings:GCC Compiler Settings

Some messages in the SQLite group list refer to the following functions: sqliteOsEnterMutex() and sqliteOsLeaveMutex(). These functions set and clear the mutex lock, which is needed to guarantee a thread-safe environment. Under Mac OS X and Windows, the sqliteOsEnterMutex() and sqliteOsLeaveMutex() functions are already implemented in os.c.

Study case: multithreaded insert on the same database

If you're new to SQLite, take a quick look at this tutorial.

Spawn two or more threads. Each one, opens the db via sqlite_open() and keeps its own copy of sqlite structure.
Each thread then proceeds to insert a number of records, let's say 1000. The problem you will encounter is the following: one thread will get control over the database by setting a lock on the file. This is fine, but the rest of the threads will keep on failing for each attempted INSERT while the lock is active.

Solution

Test for SQLITE_BUSY, which I didn't do originally. Here's some pseudo-code to illustrate a solution:

  while (continueTrying) {
    retval = sqlite_exec(db, sqlQuery, callback, 0, &msg);
    switch (retval) {
      case SQLITE_BUSY:
        Log("[%s] SQLITE_BUSY: sleeping fow a while...", threadName);
        sleep a bit... (use something like sleep(), for example)
        break;
      case SQLITE_OK:
        continueTrying = NO; // We're done
        break;
      default:
        Log("[%s] Can't execute \"%s\": %s\n", threadName, sqlQuery, msg);
        continueTrying = NO;
        break;
    }
  }

  return retval;

An alternative approach is:

Establish a busy handler procedure with sqlite_busy_handler(). In the busy handler wait on a monitor or condition variable or event with appropriate timeout. Give up with error after the busy handler has been called some number of times.
In all places where sqlite_exec() et.al. is called, signal the monitor or condition variable or event after sqlite_exec() et.al. returned. This makes other threads waiting in the busy handler runnable and finally one of them getting out of the busy condition.

An example implementation of that pattern can be found in the Java SQLite wrapper on http://www.ch-werner.de/javasqlite in the SQLite.JDBC2.JDBCConnection class.

Optimization

Use transactions. I cannot stress enough how important they become to improve performance:

It speeds up batched operations, regardless of whether SQLite is running in single threaded, multithreaded, or multiprocess mode.
The number of collisions (or waits) that a thread suffers is reduced dramatically if we run the batched manipulation enclosed within a transaction.

Case in point: a benchmark application I've written for this purpose

Without transactions

2003-01-10 09:58:49.465 SQLiteThreadTest[14737] Begin multithreaded test...
2003-01-10 09:58:49.529 SQLiteThreadTest[14737] [Thread1]: starting without transaction...
2003-01-10 09:58:49.541 SQLiteThreadTest[14737] [Thread2]: starting without transaction...
2003-01-10 09:58:49.549 SQLiteThreadTest[14737] [Thread2] SQLITE_BUSY: sleeping fow a while...
2003-01-10 09:58:49.559 SQLiteThreadTest[14737] [Thread2] SQLITE_BUSY: sleeping fow a while...
2003-01-10 09:58:49.570 SQLiteThreadTest[14737] [Thread2] SQLITE_BUSY: sleeping fow a while...
...
...
...
2003-01-10 09:58:56.666 SQLiteThreadTest[14737] [Thread2] SQLITE_BUSY: sleeping fow a while...
2003-01-10 09:58:56.667 SQLiteThreadTest[14737] [Thread2] SQLITE_BUSY: sleeping fow a while...
2003-01-10 09:58:56.669 SQLiteThreadTest[14737] [Thread2] SQLITE_BUSY: sleeping fow a while...
2003-01-10 09:58:56.670 SQLiteThreadTest[14737] Thread Thread1 has finished: 1000 good inserts | 0 bad inserts | 470 collisions
2003-01-10 09:58:57.139 SQLiteThreadTest[14737] Thread Thread2 has finished: 1000 good inserts | 0 bad inserts | 552 collisions
2003-01-10 09:58:57.156 SQLiteThreadTest[14737] Finish multithreaded test...

With transactions

2003-01-10 09:52:38.806 SQLiteThreadTest[14714] Begin multithreaded test...
2003-01-10 09:52:38.887 SQLiteThreadTest[14714] [Thread1]: starting using a transaction...
2003-01-10 09:52:38.893 SQLiteThreadTest[14714] [Thread2]: starting using a transaction...
2003-01-10 09:52:38.894 SQLiteThreadTest[14714] [Thread2] SQLITE_BUSY: sleeping fow a while...
2003-01-10 09:52:38.895 SQLiteThreadTest[14714] [Thread2] SQLITE_BUSY: sleeping fow a while...
2003-01-10 09:52:38.898 SQLiteThreadTest[14714] [Thread2] SQLITE_BUSY: sleeping fow a while...
...
...
...
2003-01-10 09:52:39.258 SQLiteThreadTest[14714] [Thread2] SQLITE_BUSY: sleeping fow a while...
2003-01-10 09:52:39.259 SQLiteThreadTest[14714] [Thread2] SQLITE_BUSY: sleeping fow a while...
2003-01-10 09:52:39.261 SQLiteThreadTest[14714] [Thread2] SQLITE_BUSY: sleeping fow a while...
2003-01-10 09:52:39.262 SQLiteThreadTest[14714] Thread Thread1 has finished: 1000 good inserts | 0 bad inserts | 0 collisions
2003-01-10 09:52:41.445 SQLiteThreadTest[14714] Thread Thread2 has finished: 1000 good inserts | 0 bad inserts | 117 collisions
2003-01-10 09:52:41.466 SQLiteThreadTest[14714] Finish multithreaded test...

Summary

Without transactions: 470 + 552 = 1022 collisions in ~1 minute 8 seconds
With transactions: 0 + 117 = 117 collisions in ~3 seconds

Conclusion

Make sure you're compiling SQLite with -DTHREADSAFE=1.
Make sure that each thread opens the database file and keeps its own sqlite structure.
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.
Make sure you enclose within transactions the commands that modify the database file, like INSERT, UPDATE, DELETE, and others.

Multithreading and Temporary Tables

When you use temporary tables, 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.

sqlite - Multi Threading

Page History