sqlite3.dylib: illegal multi-threaded access to da

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 sqlyte3 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 unfortunatelly.

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

From https://www.sqlite.org/threadsafe.html

SQLite supports three different threading modes:

Single-thread. In this mode, all mutexes are disabled and SQLite is unsafe to use in more than a single thread at once.

Multi-thread. In this mode, SQLite can be safely used by multiple threads provided that no single database connection is used simultaneously in two or more threads.

Serialized. In serialized mode, SQLite can be safely used by multiple threads with no restriction.

In iOS，SQLite default threading mode was SQLITE_OPEN_NOMUTEX(equal to Multi-thread),which was not safe when multiple threads write or read the database simultaneously use one connection. Change threading mode to serialized may helpful for you.You can change threading mode use sqlite3_config() or sqlite3_open_v2().

Answer 2

In case of any one faced this problem in Swift. Solution will be:

let dbName = "first.db"
static let shared = DatabaseManger()
var db: OpaquePointer?
let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)

private init(){
    print("singletone initialized")
    sqlite3_shutdown();
    let dbPath = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false)
        .appendingPathComponent(dbName)
    if sqlite3_open_v2(dbPath.path, &db, SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE | SQLITE_OPEN_FULLMUTEX, nil) == SQLITE_OK {
        print("Successfully opened database connection at \(dbPath.path)")
    }
    else {
        print("unable to open database connection")
    }
}

This code tested in swift version 4.0 and 4.2

Answer 3

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;
}