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

sqlite multithreading python
sqlite multithreading c#
sqlite multiple connections
sqlite multiple threads transactions
sqlite3 connection thread safe
sqlite_open_fullmutex
sqlite pooling=true
api call with null database connection pointer

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

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

[logging] BUG IN CLIENT OF sqlite3.dylib: illegal multi-threaded , IN CLIENT OF libsqlite3.dylib: illegal multi-threaded access to database connection #657. Closed. archerx3 opened this issue on Nov 27, 2019 · 6 comments. This doesn't mean all that much, unfortunately: The only thing thread safe means in this context is that you can use SQLite3 in multiple threads, but a single database connection should still stay within a single thread. Since SQLite 3.3.1, which was released in early 2006, it is possible to move a single database connection along multiple threads.

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

[logging] BUG IN CLIENT OF libsqlite3.dylib: illegal multi-threaded , sqlite3.dylib: illegal multi-threaded access to database connection" error on line if(sqlite3_prepare_v2(_contactDB, query_stmt, -1, &statment,  33723615: Repetitive calaccessd crashes: illegal multi-threaded access to database connection #18119 Open openradar-mirror opened this issue Aug 4, 2017 · 28 comments

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().

ios: Xcode, 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  code:11 msg:database disk image is malformed rowID:0 #729 opened Feb 1, 2019 by hubjf [logging] BUG IN CLIENT OF sqlite3.dylib: illegal multi-threaded access to database connection

Use please SQLite Shared-Cache Mode https://www.sqlite.org/sharedcache.html

sqlite3_open_v2(path.path, &db, SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE | SQLITE_OPEN_SHAREDCACHE, nil) != SQLITE_OK

SQLITE_OPEN_SHAREDCACHE

Reliable 100% :)

Using SQLite In Multi-Threaded Applications, OF sqlite3.dylib:illegal multi-threaded access to database connection BOOL result = [db executeUpdate:sql withArgumentsInArray:nil];. 17 sqlite3.dylib: illegal multi-threaded access to database connection Mar 12 '18 14 Load files from one CDN or multiple CDNS Sep 4 '17 12 Angular form validation but without <form>?

Application Specific Information: BUG IN CLIENT OF sqlite3.dylib: illegal multi-​threaded access to database connection Exception Type: EXC_BREAKPOINT  17 sqlite3.dylib: illegal multi-threaded access to database connection 14 Load files from one CDN or multiple CDNS 12 Angular form validation but without <form>?

[logging] BUG IN CLIENT OF sqlite3.dylib: illegal multi-threaded access to database connection Warning: there is at least one open result set  Repair the Jet database: Some Jet database errors can be repaired by using NTDSUTIL and ESENTUTL. Some Jet database errors cannot be repaired, and any attempt to repair them will fail. In such cases, your only recourse may be to restore a system state backup that predates the corruption, or build a new server.

With processes pool implementation celery crashes with segfault on sqlite database when task access SQLite database. With for example solo pool it works OK. Python 2.7.1 (r271:86832, Aug 5 2011, 03:30:24) Django==1.4 PyYAML==3.10 South==

Comments
  • Did you open the database using the SQLITE_OPEN_NOMUTEX flag as part of the sqlite3_open_v2 call?
  • @rmaddy no, I'm using the sqlite3 api sqlite3_open.
  • You really should use sqlite3_open_v2. Always use the newer APIs.
  • @rmaddy I've changed to sqlite3_open_v2 but still, no difference made same exceptions crashed the app. I've updated the question with the db helper method.
  • So adding | SQLITE_OPEN_NOMUTEX to the other open flags doesn't help?
  • for me not exists sqlite3_config, I also have the same problem.
  • @Augusto It did not exist for me either, but just adding SQLITE_OPEN_FULLMUTEX seems to fix this issue.