使用iPhone SDK进行sqlite和线程处理 [英] sqlite and threading with iPhone SDK

问题描述

我有一个使用sqlite 3.6（不带FMDB）的iPhone应用程序来存储和加载数据。我在应用程序加载时加载数据库并在整个应用程序中使用相同的数据库连接。

I have an iPhone app that is using sqlite 3.6 (not with FMDB) to store and load data. I load the database when the app loads and uses the same database connection through the whole app.

在后台线程中，应用程序从Web服务器下载一些数据并写入数据库。同时主线程也可能需要写入同一个数据库。这有时会导致EXC_BAD_ACCESS，因为两个线程都试图访问数据库。

In a background thread the app downloads some data from a webserver and writes to the database. At the same time the main thread also might need to write to the same database. This sometimes leads to EXC_BAD_ACCESS as both threads are trying to access the database.

从不同的线程中使用数据库的最佳和最简单的方法是什么？

What is the best and easiest way to be able to use the database from different threads?

这是一个显示问题的例子：

This is an example that shows the problem:

sqlite3 *database;   

- (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions {   

    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *path = [documentsDirectory stringByAppendingPathComponent:@"database.db"];

    if (sqlite3_open([path UTF8String], &database) != SQLITE_OK) {
        sqlite3_close(database);
        return YES;
    }

    [NSThread detachNewThreadSelector:@selector(test) toTarget:self withObject:nil];
    [self test];
    return YES;
}

-(void)test {
    for (int i = 0; i < 2000; i++) {
        NSLog(@"%i",i);
        sqlite3_exec([self getDb],"UPDATE mytable SET test=''", 0, 0, 0);
    }
}

编辑：

在下面的willcodejavaforfood的答案后，我试图改变我的代码，为每个单独的线程使用一个单独的数据库对象（连接），并添加了sqlite3_busy_timeout（），以便sqlite将重试写入数据库是否繁忙。现在我不再获得EXC_BAD_ACCESS，但我注意到并非所有数据都被插入。所以这也不是一个稳定的解决方案。似乎很难让sqlite使用线程..

After willcodejavaforfood's answer below I've tried to change my code to use a separate database object (connection) for each separate thread and also added sqlite3_busy_timeout() so that sqlite will retry to write if the database is busy. Now I don't get EXC_BAD_ACCESS anymore but I've noticed that not all data get inserted. So this is not a stable solution either. It seems to be really hard to get sqlite working with threading..

我的新解决方案有单独的连接：

My new solution with separate connections:

-(void)test {
    sqlite3 *db = [self getNewDb];
    for (int i = 0; i < 2000; i++) {
        NSLog(@"%i",i);
        sqlite3_exec(db,"UPDATE mytable SET test=''", 0, 0, 0);
    }
}

- (sqlite3 *)getNewDb {
    sqlite3 *newDb = nil;
    if (sqlite3_open([[self getDbPath] UTF8String], &newDb) == SQLITE_OK) {
        sqlite3_busy_timeout(newDb, 1000);
    } else {
        sqlite3_close(newDb);
    }
    return newDb;
}

推荐答案

我通过使用解决了这个问题一个线程和一个 NSOperationQueue 来插入数据。我会考虑一下。我从来没有能够获得一个具有多线程的稳定系统，并且大多数写入并不重要，排队确实有帮助。

I solved this problem by using one thread and an NSOperationQueue to insert the Data. I would give it some thought. I've never been able to get a stable System with mutliple threads, and most writes aren't that important that queuing really helps.

根据请求，更多信息：

我有一个 NSOperation 的子类，我用我想要存储的模型对象进行实例化。
这些操作提交到NSOperationsQueue的扩展，该扩展在一个单独的线程中运行。此自定义队列只是添加了一个指向数据库实例的指针。执行操作时，它使用 [NSOperationsQueue currentQueue] 属性来访问队列而不是数据库。有意地，我使用非并发操作（maxOperations设置为1）

因此，只有一个查询（或更新）一次性执行，完全在后台执行。

I have a subclass of NSOperation that I instantiate with the model object I want to store. These operations are than submitted to an extension of NSOperationsQueue that runs in a seperate thread. This custom Queue just adds a pointer to the database instance. When the operation is executed, it uses the [NSOperationsQueue currentQueue] property to access the queue and than the database. On purpose, i used non-concurrent operations (maxOperations was set to 1)
Hence, only one query (or update) is executed at a time consecutivly, completely in the background.

显然你完成后需要某种回调。

Obviously you need some kind of callback after you're finished.

这可能不是我能找到的快速，但最稳定，最干净的解决方案。

It is possibly not the fast, but the most stable and cleanest solution i could find.

文档：

http://developer.apple.com/library/ios/documentation/General/Conceptual/ConcurrencyProgrammingGuide/OperationObjects/OperationObjects.html

http://www.cimgf.com/2008/02/16/cocoa-tutorial-nsoperation -and-nsoperationqueue /

http://icodeblog.com/2010/03/04/iphone-coding-turbo-charging-your-apps-with-nsoperation/

这篇关于使用iPhone SDK进行sqlite和线程处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！