Using Fmdb multithreading to Access databases, and database is locked issues

Today finally solves the problem that the database is locked when the multi-thread accesses the database simultaneously, the error message is:

Unknown error finalizing or resetting statement (5:database is locked)

Finally, through Fmdatabasequeue solves this problem, this article summarizes:

Fmdatabase cannot use the same instance for multithreading

Multi-threaded access to the database, cannot use the same Fmdatabase instance, or an exception will occur. If a thread is allowed to use a separate fmdatabase instance, there is also the possibility that the database is locked problem occurs. This is due to the competition caused by multi-thread to SQLite

My app is multithreaded using a separate Fmdatabase instance to access the database, although not caused by crash, but there is a database is locked problem, resulting in a lot of data is not written as expected

Using Fmdatabasequeue, the problem remains

Later on Fmdb's official website read the document, confirm with fmdatabasequeue can solve this problem, API is relatively simple:

NSString *dbfilepath = [Pathresolver databasefilepath];queue = [Fmdatabasequeue Databasequeuewithpath:dbfilepath]; [Queue indatabase:^ (Fmdatabase *db) {    //access db}];

But the actual test, or the database is locked

Read the relevant source code, fmdatabasequeue the idea of solving this problem is: Create a queue, and then put into the queue block order execution, so as to avoid multi-threaded simultaneous access to the database

And my code is multi-threaded each create fmdatabasequeue instances, so there are many queues, so there is still a database competition problem, and with the same as when using Fmdatabase

Share the same instance of Fmdatabasequeue

Then I let each thread use the same queue instance, and the problem is resolved.

The way to do it, I wanted to add a singleton to fmdatabase at first, but it would be troublesome to upgrade Fmdb later, so I ended up creating a helper class

@implementation losdatabasehelper{    fmdatabasequeue* queue;} -(ID) init{self    = [super init];    if (self) {        NSString *dbfilepath = [Pathresolver Databasefilepath];        Queue = [Fmdatabasequeue Databasequeuewithpath:dbfilepath];    }    return self;} + (losdatabasehelper*) sharedinstance{    static dispatch_once_t pred = 0;    __strong static id _sharedobject = nil;    Dispatch_once (&pred, ^{        _sharedobject = [[Self alloc] init];    });    return _sharedobject;} -(void) Indatabase: (void (^) (fmdatabase*)) block{    [Queue indatabase:^ (Fmdatabase *db) {        block (db);    }];} @end

Other classes in the system Use this helper class singleton, which ensures that only fmdatabasequeue instances are available globally. Note that because the helper internally holds the Fmdatabasequeue, so you can do so, if the packaging is the Fmdatabase class, there will definitely be a problem. Because Fmdatabase instances cannot be shared in a multithreaded environment

After using Fmdatabasequeue, manage db

Originally using the Fmdatabase class, you need to manually invoke the open and close methods of the DB

But with Fmdatabasequeue, you don't need to call open, because the view code finds that the queue is open. As to whether to close, I am not sure, because the official sample code did not call close. In practical applications, I did not call, as if there were no problems. If I need close, I think I can add a call to the close queue in the helper class's public methods. Here is the source of the close:

-(void) Close {    fmdbretain (self);    Dispatch_sync (_queue, ^ () {         [_db close];        Fmdbrelease (_db);        _db = 0x00;    });    Fmdbrelease (self);}

So, using queue, it is not necessary to open and close the db yourself. But if you use fmresultset,rs you need to close, otherwise you will be reported warning:

if ([db hasopenresultsets]) {    NSLog (@ "Warning:there is at least one open result set around after performing [Fmdataba Sequeue indatabase:] ");

In order not to see warning, I have called in the block [Rs Close]

Refreshing the database file path

Specific to our application, there is a special problem to consider. Because our app can switch accounts, the DB file of the account is independent. So when the user logs back in, the helper queue needs to be refreshed

+ (void) refreshdatabasefile{    losdatabasehelper *instance = [self sharedinstance];    [Instance Dorefresh];} -(void) dorefresh{    nsstring *dbfilepath = [Pathresolver Databasefilepath];    Queue = [Fmdatabasequeue Databasequeuewithpath:dbfilepath];}

If you do not, because the helper is a singleton, then after switching accounts, User B will be accessing user A's database. Refreshed calls, typically placed after login, are available before entering the main page

Queues and Threads

During the debug process, you see a phenomenon in the way. Although multiple blocks are placed in the same queue, they are actually running in different thread

Do not confuse the concept of queues and threads, when using GCD, developers focus on putting blocks into the queue, but the same queue can actually correspond to multiple threads, assigning threads to blocks, is the GCD framework, and developers do not need to pay attention. As long as the operation is put into the appropriate queue, GCD will complete the thread creation, allocation and recycling