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?
Short answer: Yes
Medium length answer:
- Be sure to recompile with -DTHREADSAFE=1
- Do not use the same database connection at the same time in more than one thread.
- On some operating systems, a database connection should always be used in the same thread in which it was originally created.
- There are a few features of SQLite that are not threadsafe. Avoid those features.
Longer answer:
The precompiled binaries for windows have traditionally been threadsafe. The precompiled binaries for unix have not been. That might change in the future, so always check. But in the past, if you wanted a thread-safe version of SQLite for unix, you'd need to get the sources and compile it yourself.
By "threadsafe" we mean that you can use different SQLite database connections in different threads at the same time. It has never been safe to use the same database connection simultaneously in multiple threads. If you use the sqlite3_prepare() API to create prepared statements, each prepared statement is considered to be a part of the database connection from which it was derived. So you cannot run two prepared statements originating from the same database connection in different threads at the same time.
There is a bug in some Linux implementations (RedHat9 is the canonical example) that prevents fcntl() locks created by one thread from being modified in a different thread. If you are running on one of those systems, then you should always use an SQLite database connection in the same thread in which it was originally created. It is not safe to open the database in one thread and then pass the connection off to a different thread for processing.
The restriction of the previous paragraph has been relaxed somewhat as of SQLite version 3.3.1. Beginning with version 3.3.1, you should be able to move database connections from one thread to another as long as there are no locks outstanding at the time you move the thread. If you are not running on one of the systems effected by the fcntl() locking bug, then you can move your database connections at any time you want. But for portability, you probably should assume your system has the bug.
So, beginning with version 3.3.1, the common paradigm of maintaining a pool of database connections and handing them off to worker threads for processing should work fine - as long as your worker threads are careful to finalize all of their prepared statements prior to exiting. For added safety, your worker threads would do well to call sqlite3_thread_cleanup() before exiting - though this is only a precaution against latent bugs and is not strictly necessary.
If you compile SQLite with the -DSQLITE_ENABLE_MEMORY_MANAGEMENT=1 option, then SQLite maintains a count of all outstanding memory allocations for each thread. In that case, you should only use an SQLite database connection in the same thread in which it was originally created - otherwise things will be malloc()-ed in one thread and free()-ed in another and the counts will get all out-of-whack. The -DSQLITE_ENABLE_MEMORY_MANAGEMENT feature is generally only useful for low-memory embedded devices. If you do not need it you are well advised to leave it turned off. It is turned off by default.
If you enable the shared cache feature of SQLite using the sqlite3_enable_shared_cache() API, then database connections that use the shared cache should only be used in the same thread in which they were originally created.
The PRAGMA temp_store_directory SQL statement is not thread-safe. If you need to change the directory in which SQLite is storing temporary files, do so once at program initialization and thereafter leave the setting alone.
The "localtime" modifier for the built-in date/time functions uses the localtime() C API, which is not threadsafe. The call to localtime() in SQLite is protected by a mutex, so the "localtime" modifier is safe to use as long as nothing else in your program calls localtime() independently of SQLite. Note: in Linux/GNU LIBC gmtime() shares the same global time structure used by localtime(), so your program may not be able to call gmtime() safely in a multithreaded environment with SQLite. In general if you can guarantee that your program exclusively uses the _r variants of all the POSIX date functions, you are probably okay.
Any aspect of SQLite that is not mentioned above is considered threadsafe. If you have doubts, ask on the mailing list.
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 ~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.
(comment) discussion about this at http://sqlite.phxsoftware.com/forums/2284/ShowThread.aspx#2284