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

*Short answer:* Yes

*Medium length answer:*

1: Be sure to recompile with -DTHREADSAFE=1
2: Do not use the same database connection at the same time in more than
   one thread.
3: On some operating systems, a database connection should always be used
   in the same thread in which it was originally created.
4: 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 {link: http://www.sqlite.org/capi3ref.html#sqlite3_prepare 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
{link: http://www.sqlite.org/capi3ref.html#sqlite3_finalize finalize}
all of their prepared statements prior to exiting.
For added safety, your worker threads would do well to call
{link: http://www.sqlite.org/capi3ref.html#sqlite3_thread_cleanup 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
{link: http://www.sqlite.org/capi3ref.html#sqlite3_enable_shared_cache 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 {link: http://www.sqlite.org/pragma.html#pragma_temp_store_directory 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 {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 '<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 {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.

(comment) discussion about this at http://sqlite.phxsoftware.com/forums/2284/ShowThread.aspx#2284