sqlite3.dylib: illegal multi-threaded access to database connection

Question

I have an iOS app that uses sqlite3 and I'm facing issues with multi-threading crashing the app with the illegal multi-threaded access to database connection message. Of course, it's because I'm using multi-threading; the problem is, my sqlite3 instance is configured to use multi-thread:

sqlite3_config(SQLITE_CONFIG_MULTITHREAD);

Even though I'm using multi-threading (sqlite3 build was also compiled with the multi-threading flag), it causes my app to crash when multiple threads write or read the database simultaneously.

Crash report

Application Specific Information:
BUG IN CLIENT OF sqlite3.dylib: illegal multi-threaded access to database connection

Exception Type:  EXC_BREAKPOINT (SIGTRAP)
Exception Codes: 0x0000000000000001, 0x00000001823ed2fc
Termination Signal: Trace/BPT trap: 5
Termination Reason: Namespace SIGNAL, Code 0x5
Terminating Process: exc handler [0]
Triggered by Thread:  12

Thread 12 Crashed:

0   libsqlite3.dylib                0x00000001823ed2fc sqlite3MutexMisuseAssert + 144 (sqlite3.c:23788)
1   libsqlite3.dylib                0x00000001823ed2ec sqlite3MutexMisuseAssert + 128 (once.h:84)
2   libsqlite3.dylib                0x000000018235248c sqlite3LockAndPrepare + 320 (sqlite3.c:23801)
3   MyCodeCall.m ...........

I've been struggling with this issue for a while and I couldn't find any reference to this on google unfortunately.

UPDATE

+(sqlite3*) getInstance {
  if (instance == NULL) {
    sqlite3_shutdown();
    sqlite3_config(SQLITE_CONFIG_MULTITHREAD);
    sqlite3_initialize();

    NSLog(@"isThreadSafe %d", sqlite3_threadsafe());

    const char *path = [@"./path/to/db/db.sqlite" cStringUsingEncoding:NSUTF8StringEncoding];

    if (sqlite3_open_v2(path, &database, SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, NULL) != SQLITE_OK) {
      NSLog(@"Database opening failed!");
    }
  }

  return instance;
}

Answer 1

It turns out that SQLITE_CONFIG_MULTITHREAD mode works well on a multi-threading environment as long as you don't use the same connection simultaneously; which happen to be the exact scenario that I had. Therefore, to solve this issue you can either open a new connection for each thread or use SQLITE_CONFIG_SERIALIZED in full mutex mode using SQLITE_OPEN_FULLMUTEX flag to open the connection.

The helper method ended up like so:

+(sqlite3*) getInstance {
  if (instance == NULL) {
    sqlite3_shutdown();
    sqlite3_config(SQLITE_CONFIG_SERIALIZED);
    sqlite3_initialize();

    NSLog(@"isThreadSafe %d", sqlite3_threadsafe());

    const char *path = [@"./path/to/db/db.sqlite" cStringUsingEncoding:NSUTF8StringEncoding];

    if (sqlite3_open_v2(path, &database, SQLITE_OPEN_READWRITE|SQLITE_OPEN_FULLMUTEX, NULL) != SQLITE_OK) {
      NSLog(@"Database opening failed!");
    }
  }

  return instance;
}