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 {link: http://www.hwaci.com/sw/sqlite/faq.html#q8 careful}. **SQLite multithreading settings** The setting named {link: http://groups.yahoo.com/group/sqlite/message/440 THREADSAFE} turns multithreading on or off. {link: http://groups.yahoo.com/group/sqlite/message/527 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 {link: http://developer.apple.com/tools/projectbuilder/index.html 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 '':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 {link: http://www.hwaci.com/sw/sqlite/quickstart.html 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 {link: http://www.hwaci.com/sw/sqlite/faq.html#q7 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 {link: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: 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 ~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. **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.