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.

**SQLite multithreading settings**

The setting named *DTHREADSAFE* turns multithreading on or off. In Windows, this setting is turned on by default. Under Mac OS X, you'll have to set it manually. In Project Builder, you can easily turn on multithreading by adding *-DTHREADSAFE=1* to the *Other C Compiler Flags* field, in *Targets*:

_<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**

*:  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;

**Optimization**

*Use transactions*. I cannot stress enough how important they become to improve performance:
1: It speeds up batched operations, regardless of whether SQLite is running in single threaded, multithreaded, or multiprocess mode.
2: 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...{linebreak} 
2003-01-10 09:58:49.529 SQLiteThreadTest[14737] [Thread1]: starting without transaction...{linebreak} 
2003-01-10 09:58:49.541 SQLiteThreadTest[14737] [Thread2]: starting without transaction...{linebreak} 
2003-01-10 09:58:49.549 SQLiteThreadTest[14737] [Thread2] SQLITE_BUSY: sleeping fow a while...{linebreak} 
2003-01-10 09:58:49.559 SQLiteThreadTest[14737] [Thread2] SQLITE_BUSY: sleeping fow a while...{linebreak} 
2003-01-10 09:58:49.570 SQLiteThreadTest[14737] [Thread2] SQLITE_BUSY: sleeping fow a while...{linebreak} 
...{linebreak} 
...{linebreak} 
...{linebreak} 
2003-01-10 09:58:56.666 SQLiteThreadTest[14737] [Thread2] SQLITE_BUSY: sleeping fow a while...{linebreak} 
2003-01-10 09:58:56.667 SQLiteThreadTest[14737] [Thread2] SQLITE_BUSY: sleeping fow a while...{linebreak} 
2003-01-10 09:58:56.669 SQLiteThreadTest[14737] [Thread2] SQLITE_BUSY: sleeping fow a while...{linebreak} 
2003-01-10 09:58:56.670 SQLiteThreadTest[14737] Thread Thread1 has finished: 1000 good inserts | 0 bad inserts | 470 collisions{linebreak} 
2003-01-10 09:58:57.139 SQLiteThreadTest[14737] Thread Thread2 has finished: 1000 good inserts | 0 bad inserts | 552 collisions{linebreak} 
2003-01-10 09:58:57.156 SQLiteThreadTest[14737] Finish multithreaded test...{linebreak} 

*With transactions*

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

**Summary**

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

**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.