Before start using an RDMS we should always evaluate which are its constraints and how we are planning to use it.

We often implement multithread processes that access database. One simple example is acessing a database on a multithreaded application server, such as Puma, or on a multithreaded background processing framework, such as Sidekiq.

When running code that access a database on such multithreaded environments, we need to verify if the database is ready to accept connections from multiple threads and, sometimes, if a single connection can be shared with multiple threads.

A real scenario

Recently, I was working on an application that uses in-memory SQLite and the team decided to migrate from Resque - a process-based background processing framework - to Sidekiq, which is thread-based. This change begs the question:

Is SQLite thread-safe?

SQLite was built to work on three diferent modes in this regard and the mode is defined by a compilation flag named THREADSAFE. It can have three values:

  • THREADSAFE=0: It’s unsafe to use SQLite in a multithreaded application - it ommits all mutexing code on the compiled code;
  • THREADSAFE=1: It’s safe to use SQLite in a multithreaded application and multiple threads can use the same connection. This is the default option when compiling SQLite;
  • THREADSAFE=2: It’s safe to use SQLite in a multithreaded application but each connection must be used by a single thread at a time.

Checking thread-safety level of SQLite installation

In Ruby

Open interactive Ruby console: $ irb and execute:

SQLite3::Database.new(":memory:").
  execute("PRAGMA compile_options").
  map(&:first).find { |option| option =~ /THREADSAFE/ }

# This will produce an output like "THREADSAFE=2"

On a C program

Create a file named sqlite_threadsafe.c with following content:

#include <sqlite3.h>

int main() {
  return sqlite3_threadsafe();
}

Then compile with:

$ gcc sqlite3_threadsafe.c -lsqlite3 -o sqlite3_threadsafe

And finally see the thread-safety level executing:

$ ./sqlite_threadsafe
2 # this is the value used on THREADSAFE compilation flag

On SQLite console

Execute PRAGMA compile_options on SQLite console to see all the compile options, including the THREADSAFE option. E.g.:

$ sqlite3

SQLite version 3.8.5 2014-08-15 22:37:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> PRAGMA compile_options;
ENABLE_FTS3
ENABLE_FTS3_PARENTHESIS
ENABLE_LOCKING_STYLE=1
ENABLE_RTREE
OMIT_AUTORESET
OMIT_BUILTIN_TEST
OMIT_LOAD_EXTENSION
SYSTEM_MALLOC
THREADSAFE=2

References:

  • https://www.sqlite.org/compile.html#threadsafe
  • http://www.sqlite.org/cvstrac/wiki?p=MultiThreading