A simple test of concurrency in SQLite

Makefile

TARGETS:= sqlite_concurrency

USRLOCAL?=/usr/local
SQLITE_INCDIR:=$(USRLOCAL)/include
SQLITE_LIBDIR:=$(USRLOCAL)/lib
SQLITE_LIBNAME:=sqlite3

CXXFLAGS+=-I$(SQLITE_INCDIR) -std=c++11
LDFLAGS+=-L$(SQLITE_LIBDIR)
LDFLAGS+=-Wl,-rpath=$(SQLITE_LIBDIR)
LDLIBS+=-l$(SQLITE_LIBNAME) -lstdc++ -lpthread -ldl

all: $(TARGETS)

clean:
	$(RM) $(TARGETS)

.PHONY: all clean

sqlite_concurrency.cc

#include "sqlite3.h"
#include <thread>
#include <cstdlib> // for rand()
#include <string>
#include <vector>
#include <iostream>

using std::string;
using std::cerr;
using std::endl;

const int NUM_THREADS = 3;
const int ROWS_PER_THREAD = 10;

#define TEST(x) do{cerr<<(x?"PASSED ":"FAILED ")<< #x <<endl;} while(0)

class connection {
public:
    explicit connection(string dbpath) : dbpath_(dbpath) { open(); }
    virtual ~connection() { sqlite3_close(db_); }
    sqlite3* get() { return db_; }
    operator sqlite3*() { return db_; }
    int exec(string sql) {return sqlite3_exec(db_, sql.c_str(), 0, 0, 0);}
private:
    int open() {
        int const flags = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE;
        return sqlite3_open_v2(dbpath_.c_str(), &db_, flags, 0);
    }
    string const dbpath_;
    sqlite3* db_;
};

class statement {
public:
    statement(sqlite3* db, string sql) : db_(db), sql_(sql) {prepare();}
    virtual ~statement() { sqlite3_finalize(stmt_); }
    operator sqlite3_stmt*() { return stmt_; }
    int column_int(int col) {return sqlite3_column_int(stmt_, col);}
    int step() { return sqlite3_step(stmt_); }
    int bind(int i, int v) { return sqlite3_bind_int(stmt_, i, v); }
    int bind(int i, string v) {
        sqlite3_bind_text(stmt_, i, v.c_str(), v.length(), 0);
    }
    int reset() { sqlite3_reset(stmt_); }
private:
    int prepare() {
        return sqlite3_prepare_v2(db_, sql_.c_str(), sql_.length(), &stmt_, 0);
    }
    sqlite3* db_;
    string sql_;
    sqlite3_stmt* stmt_;
};

int get_row_count(connection& conn) {
    statement select(conn, "SELECT count(id) FROM test;");
    if (SQLITE_ROW == select.step())
        return select.column_int(0);
    return 0;
}

int insert_random(sqlite3* db, string dbpath, int tno) {
    statement insert(db, "INSERT INTO test (n,name) VALUES (?,?);");
    string name = "Thread " + std::to_string(tno);
    for (int i=0; i<ROWS_PER_THREAD; ++i) {
        insert.reset();
        insert.bind(1, rand());
        insert.bind(2, name);
        if (insert.step() != SQLITE_DONE) {
            cerr << name << ": " << sqlite3_errmsg(db) << endl;
            break;
        }
    }
}

int new_connection_insert_random(sqlite3* db, string dbpath, int tno) {
    connection conn(dbpath);
    return insert_random(conn.get(), dbpath, tno);
}

template<class Function>
bool run(Function&& func, string dbpath) {
    connection conn(dbpath);
    conn.exec("DROP TABLE IF EXISTS test;");
    conn.exec("CREATE TABLE test ("
              "   id   INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
              "   n    INTEGER,"
              "   name TEXT );");

    std::vector<std::thread> threads;
    for (int i=1; i<=NUM_THREADS; ++i)
        threads.emplace_back(func, conn.get(), dbpath, i);
    for (auto t=threads.begin(); t!=threads.end(); ++t) t->join();
    return (get_row_count(conn) == ROWS_PER_THREAD*NUM_THREADS);
}

int main() {
    TEST(run(insert_random, "/var/tmp/testdb1.db"));
    TEST(run(new_connection_insert_random, "/var/tmp/testdb2.db"));
}

This is meant to be a simple example of how concurrency does and does not work in SQLite. The take-away: As long as all thread share a single connection to the database, it works. Opening up multiple connections to the database causes failure.

This code must be compiled with -std=c++11 and -lsqlite3, and assumes that the SQLite library in use was compiled to be thread-safe.

I added a GNU Makefile for convenience, and I fixed a bug that I caused right before posting the first time.