The VERSION of FMDB discussed in this article is 2.7.5, and the test environment is Xcode 10.1 and iOS 12.1.
I. Problem record
Recently, when analyzing crash logs, we found that a FMDB crash occurred frequently, and the crash stack is as follows:
You can see an error on the console:
[logging] BUG IN CLIENT OF sqlite3.dylib: illegal multi-threaded access to database connection
Warning: there is at least one open result set around after performing [FMDatabaseQueue inDatabase:]
Copy the codeFrom the log can probably guess, this is multithreaded access to the database caused by the crash. FMDB provides THE FMDatabaseQueue to operate databases in a multithreaded environment, which maintains a serial queue internally to keep threads safe. I checked all the code to operate the database, which is executed in the FMDatabaseQueue queue. Why do I still report multithreading problems (🤔)?
I looked around online and found that some people on Github have encountered the same problem, Issue 724 and Issue 711, and there are relevant discussions on Stack Overflow.
The business in the project is too complex to troubleshoot problems, so I wrote a simplified version of Demo to reproduce the problems:
NSString *dbPath = [docPath stringByAppendingPathComponent:@"test.sqlite"];
_queue = [FMDatabaseQueue databaseQueueWithPath:dbPath];
// Create a new table test, inert some data
[_queue inDatabase:^(FMDatabase * _Nonnull db) {
[db executeUpdate:@"create table if not exists test (a text, b text, c text, d text, e text, f text, g text, h text, i text)"];
for (int i = 0; i < 10000; i++) {
[db executeUpdate:@"insert into test (a, b, c, d, e, f, g, h, i) values ('1', '1', '1','1', '1', '1','1', '1', '1')"]; }}];// multithread query database
for (int i = 0; i < 10; i++) {
dispatch_async(dispatch_get_global_queue(0.0), ^{
[_queue inDatabase:^(FMDatabase * _Nonnull db) {
FMResultSet *result = [db executeQuery:@"select * from test where a = '1'"];
// We can use "if" instead of "while"
if ([result next]) {
}
// Close is not called
// [result close];
}];
});
}
Copy the codeThe problem is perfectly reproduced, and then we can troubleshoot the problem. There are two problems to be solved:
- Is iOS’s built-in SQLite thread safe?
- Why are thread-safe queues used
FMDatabaseQueueOr is there a thread safety issue?
SQLite is thread safe
Let’s start with the first question: is iOS’s built-in SQLite thread-safe?
I googled and found the official documentation for SQLite – Using SQLite In Multi-threaded Applications. The document is very clear, and you’d better read it carefully if you have time. Here’s a brief summary.
SQLite has three threading modes:
- In single-thread mode, all mutex code is removed at compile time. It is unsafe in multi-threaded environment.
- Multi-thread: In most cases, multi-thread environment is safe. For example, the same database, open multiple threads, each thread open a connection to access the library at the same time, this situation is safe. But there are also unsafe situations where multiple threads are using the same database connection (or any prepared statement derived from that connection)
- Serialized, completely thread safe.
There are three points in time at which you can configure the threading mode: compile-time, start-time, and run-time. The configuration takes effect if run-time overwrites start-time and compile-time overwrites start-time. Some special cases are as follows:
- Compile-time Settings
Single-threadThe user can no longer enable multithreaded mode because thread-safe code has been optimized. - If multithreaded mode is set at compile time, it cannot be downgraded to single-threaded mode at run time, only in
Multi-threadandSerializedSwitch between.
Threading mode compilation option
The official documentation for the SQLite Threading Mode compilation option
At compile time, you can configure SQLite to be safe in multithreaded environments by using the SQLITE_THREADSAFE configuration item. There are three options:
- 0, for single-thread, all mutex code will be removed at compile time. SQLite is unsafe in multi-threaded environments.
- 1, corresponding to Serialized, safe in multi-threaded environment, if not specified manually, this is the default option.
- Prepared Statements that two threads are trying to use the same database connection at the same time (or any preprocessed Statements derived from that database connection)
In addition to specifying the threading mode at compile time, You can also use sqlite3_config() (start-time) to change the global threading mode or sqlite3_open_v2() (run-time) to change the threading mode of a database connection.
However, if SQLITE_THREADSAFE = 0 is configured at compile time, all thread-safe code is optimized out at compile time and you cannot switch to multithreaded mode.
With this knowledge, we can analyze problem number one. Sqlite3_threadsafe () can be used to obtain the compile-time configuration of SQLite, which is 2 (multi-thread).
That said, the system’s built-in SQLite is not completely thread-safe without doing any configuration. Of course, you can manually switch to Serialized mode to achieve full thread safety.
Sqlite3_config (SQLITE_CONFIG_SERIALIZED); // Option 2: Set the Connecting mode. Sqlite3_open_v2 SQLITE_OPEN_FULLMUTEX sqlite3_open_v2(path, &db, SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE | SQLITE_OPEN_FULLMUTEX, nil)Copy the codeAfter the test, the crash problem in the Demo was solved perfectly after the transformation of the above two schemes. But I don’t think this is the optimal solution, why doesn’t Apple directly set the compilation option to Serialized, this article will never appear 😂, let everyone agonize for a long time, to manually set the mode. I think performance is an important factor. The performance of multi-thread is better than that of Serialized. As long as users ensure that a connection is not accessed by multiple threads at the same time, there is no problem.
FMDB’s FMDatabaseQueue, for example, addresses this problem.
FMDatabaseQueue is not actually secure
FMDB’s official document reads:
FMDatabaseQueue will run the blocks on a serialized queue (hence the name of the class). So if you call FMDatabaseQueue’s methods from multiple threads at the same time, they will be executed in the order they are received. This way queries and updates won’t step on each other’s toes, and every one is happy.
Using FMDatabaseQueue in multiple threads is really safe, with serial queues in the GCD to ensure that all read and write operations are executed serially. Its core code is as follows:
_queue = dispatch_queue_create([[NSString stringWithFormat:@"fmdb.%@".self] UTF8String], NULL);
- (void)inDatabase:(__attribute__((noescape)) void (^)(FMDatabase *db))block {
/ /... Omit some code
dispatch_sync(_queue, ^() {
FMDatabase *db = [self database];
block(db);
});
/ /... Omit some code
}
Copy the codeHowever, if we look at the crash stack in the first Demo, we can see that the crash occurred in thread 3’s function [FMResultSet reset], which is defined as follows:
- (void)reset {
if (_statement) {
// Reset A Prepared Statement Object
sqlite3_reset(_statement);
}
_inUse = NO;
}
Copy the codeThe call stack for this function is as follows:
- [FMStatement reset]
- [FMResultSet close]
- [FMResultSet dealloc]
Copy the codeFollowing the call stack, we take a look at the DEALloc and close methods of FMResultSet:
- (void)dealloc {
[self close];
FMDBRelease(_query);
_query = nil;
FMDBRelease(_columnNameToIndexMap);
_columnNameToIndexMap = nil;
}
- (void)close {
[_statement reset];
FMDBRelease(_statement);
_statement = nil;
[_parentDB resultSetDidClose:self];
[self setParentDB:nil];
}
Copy the codeIt follows from this that the close method is called in FMResultSet dealloc to close the preprocessed statement. Back to the crash stack in the first section, it is not difficult to find that thread 7 is using the same database connection to read the database, combined with a paragraph in the official documentation, we can draw the conclusion.
When compiled with SQLITE_THREADSAFE=2, SQLite can be used in a multithreaded program so long as no two threads attempt to use the same database connection (or any prepared statements derived from that database connection) at the same time.
With FMDatabaseQueue, it still happened that multiple threads were using the same database connection, preprocessed statements, and crashed.
The solution
Problem identified, let’s talk about how to avoid it.
The correct way to open FMDB
This problem does not exist if you iterate through an FMResultSet with a while loop, because [FMResultSet Next] calls [FMResultSet close] at the end of the loop.
[_queue inDatabase:^(FMDatabase * _Nonnull db) {
FMResultSet *result = [db executeQuery:@"select * from test where a = '1'"];
/ / security
while ([result next]) {
}
/ / security
if ([result next]) {
}
[result close];
}];
Copy the codeIf you must use if ([result next]), add [FMResultSet close] manually.
Write in the last
Write in the last
I encountered this problem because I was misled by one sentence in the official document.
Typically, there’s no need to -close an FMResultSet yourself, since that happens when either the result set is deallocated, or the parent database is closed.
So I asked for a Pull request, and I suggested two solutions:
- Modify the document to emphasize that the user needs to call close manually.
- in
[FMDatabaseQueue inDatabase:]At the end of the function, call[FMDatabase closeOpenResultSets]Help the caller close all FMResultSets.
Ccgus, the author of FMDB, took the first approach, modifying the document in the latest COMMIT and adding relevant instructions.
Typically, there’s no need to -close an FMResultSet yourself, since that happens when either the result set is exhausted. However, if you only pull out a single request or any other number of requests which don’t exhaust the result set, you will need to call the -close method on the FMResultSet.
reference
- Using SQLite In Multi-Threaded Applications
- sqlite3.dylib: illegal multi-threaded access to database connection
- FMDB
- SQLite compilation options official documentation