ios - 如何避免 'database is locked' 错误？

Question

我正在解决最常见的数据库锁定问题。

当我的应用程序运行时，会调用一个 API 来获取数据。然后将此数据插入到数据库表中。我在带有串行队列的后台线程中执行此操作。同时如果用户按下按钮切换到下一个屏幕，选择查询工作(在主线程上)以获取要显示的数据。这就是我遇到这个问题的意义所在。甚至应用程序有时也会崩溃。

我搜索过这个问题，但每次我看到有关“同步”或使用 FMDB 的教程的链接。

由于我的时间不多了，有人可以帮我提供一个简单而详细的解决方案吗？我还发布了用于插入和检索数据的代码。请注意检查我是否在某处出错。

-(void)insertIntoTable_VENUES_WithData:(NSArray*)dataArray{
sqlite3_stmt *statement = nil;

const char *dbpath = [_databasePath UTF8String];
if (sqlite3_open(dbpath, &_database) == SQLITE_OK) {

    const char *sql = "INSERT OR REPLACE INTO venues (gig_date, start_time, doors_open, end_time, gig_title, main_artist, gig_type, promoter, band_members, venue, tel_no, postcode, img_url, ticket_price, booking_url, sold_out, venue_url, venue_biog, sync_flag) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

    NSDateFormatter *dateFormat = [[NSDateFormatter alloc] init];
    [dateFormat setLocale:[[StaticHelper sharedObject] usLocale]];

    if (sqlite3_prepare_v2(_database, sql, -1, &statement, NULL) == SQLITE_OK) {

        for (int i=0; i<dataArray.count; i++) {

            NSDictionary *dataDic = [NSDictionary dictionaryWithDictionary:[dataArray objectAtIndex:i]];


            if (![[dataDic objectForKey:kGigDate] isEqual:[NSNull null]]){

                NSString *dateStr = [dataDic objectForKey:kGigDate];
                [dateFormat setDateFormat:@"dd/MM/yyyy"];
                NSDate *dateFromString = [[NSDate alloc] init];
                dateFromString = [dateFormat dateFromString:dateStr];
                NSDate *dateWithoutTime = [[StaticHelper sharedObject] getDateWithOutTime:dateFromString];

                float dateToStore = [dateWithoutTime timeIntervalSince1970];
                sqlite3_bind_double(statement, 1, dateToStore);

            }

            else
                sqlite3_bind_null(statement,1);


            if (![[dataDic objectForKey:kStartTime] isEqual:[NSNull null]])
                sqlite3_bind_text(statement,2,[[dataDic objectForKey:kStartTime] UTF8String],-1,SQLITE_TRANSIENT);
            else
                sqlite3_bind_null(statement,2);

            if (![[dataDic objectForKey:kDoorsOpen] isEqual:[NSNull null]])
                sqlite3_bind_text(statement,3,[[dataDic objectForKey:kDoorsOpen] UTF8String],-1,SQLITE_TRANSIENT);
            else
                sqlite3_bind_null(statement,3);

            if (![[dataDic objectForKey:kEndTime] isEqual:[NSNull null]])
                sqlite3_bind_text(statement,4,[[dataDic objectForKey:kEndTime] UTF8String],-1,SQLITE_TRANSIENT);
            else
                sqlite3_bind_null(statement,4);

            if (![[dataDic objectForKey:kGigTitle] isEqual:[NSNull null]])
                sqlite3_bind_text(statement,5,[[dataDic objectForKey:kGigTitle] UTF8String],-1,SQLITE_TRANSIENT);
            else
                sqlite3_bind_null(statement,5);

            if (![[dataDic objectForKey:kMainArtist] isEqual:[NSNull null]])
                sqlite3_bind_text(statement,6,[[dataDic objectForKey:kMainArtist] UTF8String],-1,SQLITE_TRANSIENT);
            else
                sqlite3_bind_null(statement,6);

            if (![[dataDic objectForKey:kGigType] isEqual:[NSNull null]])
                sqlite3_bind_text(statement,7,[[dataDic objectForKey:kGigType] UTF8String],-1,SQLITE_TRANSIENT);
            else
                sqlite3_bind_null(statement,7);

            if (![[dataDic objectForKey:kPromoter] isEqual:[NSNull null]])
                sqlite3_bind_text(statement,8,[[dataDic objectForKey:kPromoter] UTF8String],-1,SQLITE_TRANSIENT);
            else
                sqlite3_bind_null(statement,8);

            if (![[dataDic objectForKey:kBandMembers] isEqual:[NSNull null]])
                sqlite3_bind_text(statement,9,[[dataDic objectForKey:kBandMembers] UTF8String],-1,SQLITE_TRANSIENT);
            else
                sqlite3_bind_null(statement,9);

            if (![[dataDic objectForKey:kVenue] isEqual:[NSNull null]])
                sqlite3_bind_text(statement,10,[[dataDic objectForKey:kVenue] UTF8String],-1,SQLITE_TRANSIENT);
            else
                sqlite3_bind_null(statement,10);

            if (![[dataDic objectForKey:kTelNo] isEqual:[NSNull null]])
                sqlite3_bind_text(statement,11,[[dataDic objectForKey:kTelNo] UTF8String],-1,SQLITE_TRANSIENT);
            else
                sqlite3_bind_null(statement,11);

            if (![[dataDic objectForKey:kPostCode] isEqual:[NSNull null]])
                sqlite3_bind_text(statement,12,[[dataDic objectForKey:kPostCode] UTF8String],-1,SQLITE_TRANSIENT);
            else
                sqlite3_bind_null(statement,12);

            if (![[dataDic objectForKey:kImgUrl] isEqual:[NSNull null]])
                sqlite3_bind_text(statement,13,[[dataDic objectForKey:kImgUrl] UTF8String],-1,SQLITE_TRANSIENT);
            else
                sqlite3_bind_null(statement,13);

            if (![[dataDic objectForKey:kTicketPrice] isEqual:[NSNull null]])
                sqlite3_bind_text(statement,14,[[dataDic objectForKey:kTicketPrice] UTF8String],-1,SQLITE_TRANSIENT);
            else
                sqlite3_bind_null(statement,14);

            if (![[dataDic objectForKey:kBookingUrl] isEqual:[NSNull null]])
                sqlite3_bind_text(statement,15,[[dataDic objectForKey:kBookingUrl] UTF8String],-1,SQLITE_TRANSIENT);
            else
                sqlite3_bind_null(statement,15);

            if (![[dataDic objectForKey:kSoldOut] isEqual:[NSNull null]])
                sqlite3_bind_text(statement,16,[[dataDic objectForKey:kSoldOut] UTF8String],-1,SQLITE_TRANSIENT);
            else
                sqlite3_bind_null(statement,16);

            if (![[dataDic objectForKey:kVenueUrl] isEqual:[NSNull null]])
                sqlite3_bind_text(statement,17,[[dataDic objectForKey:kVenueUrl] UTF8String],-1,SQLITE_TRANSIENT);
            else
                sqlite3_bind_null(statement,17);

            if (![[dataDic objectForKey:kVenueBlog] isEqual:[NSNull null]])
                sqlite3_bind_text(statement,18,[[dataDic objectForKey:kVenueBlog] UTF8String],-1,SQLITE_TRANSIENT);
            else
                sqlite3_bind_null(statement,18);

            sqlite3_bind_int(statement, 19, 0);////

            if (sqlite3_step(statement) != SQLITE_DONE) {
                NSLog(@"SQL execution failed in ZADDED_FOOD_ITEMS: %s", sqlite3_errmsg(_database));
            }


            if (sqlite3_step(statement) == SQLITE_DONE) {
                if (i == (dataArray.count - 1))
                    sqlite3_finalize(statement);

            }else{
                sqlite3_reset(statement);
                NSLog(@"SQL execution failed: %s", sqlite3_errmsg(_database));
            }
        }//for
        sqlite3_finalize(statement);

    }else{
        NSLog(@"Row insertion error(error in prepare!)");
    }

sqlite3_finalize(statement);//finalize is to sqlite3_prepare_v2 as close is to sqlite3_open
sqlite3_close(_database);;

}else{
    NSLog(@"error in opening db");
}

-(NSMutableArray *)getDataFromTable_VENUES_WhereGigDateIsEqualTo:(NSDate*)gigDate

{

NSDate *dateWithoutTime = [[StaticHelper sharedObject] getDateWithOutTime:gigDate];
NSTimeInterval dateInterval  = [dateWithoutTime timeIntervalSince1970] ;
NSInteger intDateInterval = dateInterval;

const char *dbpath = [_databasePath UTF8String];
NSMutableArray *queryResult = [NSMutableArray array];
sqlite3_stmt *statement = nil;
NSMutableDictionary *dic;

if (sqlite3_open(dbpath, &_database) == SQLITE_OK)
{
    NSString *query = [NSString stringWithFormat:@" SELECT * FROM %@ WHERE %@='%ld' AND %@=1 ORDER BY %@ ASC ",kVenues, kGigDate, intDateInterval, kSyncFlag, kStartTime];
    if(sqlite3_prepare_v2(_database,[query UTF8String],-1,&statement,NULL)==SQLITE_OK)
    {
        while (sqlite3_step(statement)<=SQLITE_ROW)
        {
            dic = [NSMutableDictionary dictionary];

            float dbDataAsDouble0 = sqlite3_column_double(statement, 0);
            NSDate *dateRetrieved = [NSDate dateWithTimeIntervalSince1970:dbDataAsDouble0];
            NSString *strDate = [[StaticHelper sharedObject] getStringFromDate:dateRetrieved];
            //char *dbDataAsChars0 = (char*)sqlite3_column_text(statement, 0);

            if (strDate != nil)
                [dic setObject:strDate forKey:kGigDate];
            else{
                [dic setObject:@"" forKey:kGigDate];
            }


            char *dbDataAsChars1 = (char*)sqlite3_column_text(statement, 1);
            if (dbDataAsChars1 != NULL)
                [dic setObject:[NSString  stringWithUTF8String:dbDataAsChars1] forKey:kStartTime];
            else{
                [dic setObject:@"" forKey:kStartTime];
            }


            char *dbDataAsChars2 = (char*)sqlite3_column_text(statement, 2);
            if (dbDataAsChars2 != NULL)
                [dic setObject:[NSString  stringWithUTF8String:dbDataAsChars2] forKey:kDoorsOpen];
            else{
                [dic setObject:@"" forKey:kDoorsOpen];
            }


            char *dbDataAsChars3 = (char*)sqlite3_column_text(statement, 3);
            if (dbDataAsChars3 != NULL)
                [dic setObject:[NSString  stringWithUTF8String:dbDataAsChars3] forKey:kEndTime];
            else{
                [dic setObject:@"" forKey:kEndTime];
            }


            char *dbDataAsChars4 = (char*)sqlite3_column_text(statement, 4);
            if (dbDataAsChars4 != NULL)
                [dic setObject:[NSString  stringWithUTF8String:dbDataAsChars4] forKey:kGigTitle];
            else{
                [dic setObject:@"" forKey:kGigTitle];
            }


            char *dbDataAsChars5 = (char*)sqlite3_column_text(statement, 5);
            if (dbDataAsChars5 != NULL)
                [dic setObject:[NSString  stringWithUTF8String:dbDataAsChars5] forKey:kMainArtist];
            else{
                [dic setObject:@"" forKey:kMainArtist];
            }


            char *dbDataAsChars6 = (char*)sqlite3_column_text(statement, 6);
            if (dbDataAsChars6 != NULL)
                [dic setObject:[NSString  stringWithUTF8String:dbDataAsChars6] forKey:kGigType];
            else{
                [dic setObject:@"" forKey:kGigType];
            }


            char *dbDataAsChars7 = (char*)sqlite3_column_text(statement, 7);
            if (dbDataAsChars7 != NULL)
                [dic setObject:[NSString  stringWithUTF8String:dbDataAsChars7] forKey:kPromoter];
            else{
                [dic setObject:@"" forKey:kPromoter];
            }


            char *dbDataAsChars8 = (char*)sqlite3_column_text(statement, 8);
            if (dbDataAsChars8 != NULL)
                [dic setObject:[NSString  stringWithUTF8String:dbDataAsChars8] forKey:kBandMembers];
            else{
                [dic setObject:@"" forKey:kBandMembers];
            }


            char *dbDataAsChars9 = (char*)sqlite3_column_text(statement, 9);
            if (dbDataAsChars9 != NULL)
                [dic setObject:[NSString  stringWithUTF8String:dbDataAsChars9] forKey:kVenue];
            else{
                [dic setObject:@"" forKey:kVenue];
            }


            char *dbDataAsChars10 = (char*)sqlite3_column_text(statement, 10);
            if (dbDataAsChars10 != NULL)
                [dic setObject:[NSString  stringWithUTF8String:dbDataAsChars10] forKey:kTelNo];
            else{
                [dic setObject:@"" forKey:kTelNo];
            }


            char *dbDataAsChars11 = (char*)sqlite3_column_text(statement, 11);
            if (dbDataAsChars11 != NULL)
                [dic setObject:[NSString  stringWithUTF8String:dbDataAsChars11] forKey:kPostCode];
            else{
                [dic setObject:@"" forKey:kPostCode];
            }


            char *dbDataAsChars12 = (char*)sqlite3_column_text(statement, 12);
            if (dbDataAsChars12 != NULL)
                [dic setObject:[NSString  stringWithUTF8String:dbDataAsChars12] forKey:kImgUrl];
            else{
                [dic setObject:@"" forKey:kImgUrl];
            }


            char *dbDataAsChars13 = (char*)sqlite3_column_text(statement, 13);
            if (dbDataAsChars13 != NULL)
                [dic setObject:[NSString  stringWithUTF8String:dbDataAsChars13] forKey:kTicketPrice];
            else{
                [dic setObject:@"" forKey:kTicketPrice];
            }


            char *dbDataAsChars14 = (char*)sqlite3_column_text(statement, 14);
            if (dbDataAsChars14 != NULL)
                [dic setObject:[NSString  stringWithUTF8String:dbDataAsChars14] forKey:kBookingUrl];
            else{
                [dic setObject:@"" forKey:kBookingUrl];
            }


            char *dbDataAsChars15 = (char*)sqlite3_column_text(statement, 15);
            if (dbDataAsChars15 != NULL)
                [dic setObject:[NSString  stringWithUTF8String:dbDataAsChars15] forKey:kSoldOut];
            else{
                [dic setObject:@"" forKey:kSoldOut];
            }


            char *dbDataAsChars16 = (char*)sqlite3_column_text(statement, 16);
            if (dbDataAsChars16 != NULL)
                [dic setObject:[NSString  stringWithUTF8String:dbDataAsChars16] forKey:kVenueUrl];
            else{
                [dic setObject:@"" forKey:kVenueUrl];
            }


            char *dbDataAsChars17 = (char*)sqlite3_column_text(statement, 17);
            if (dbDataAsChars17 != NULL)
                [dic setObject:[NSString  stringWithUTF8String:dbDataAsChars17] forKey:kVenueBlog];
            else{
                [dic setObject:@"" forKey:kVenueBlog];
            }


            [queryResult addObject:dic];

        }
        sqlite3_finalize(statement);

    }
    else{
        ////NSLog(@"Error msg : %s ", sqlite3_errmsg(_database));
        return nil;

    }
    sqlite3_close(_database);
}
else{
    NSLog(@"database not open : %s",sqlite3_errmsg(_database));
}
return queryResult;

Answer 1

如果数据库已经打开并被某个线程进程锁定，当您尝试打开它时会出现此错误(Try to open locked database)

在您的 getDataFromTable_VENUES_WhereGigDateIsEqualTo 方法中，检查以下内容:

  else{
           ////NSLog(@"Error msg : %s ", sqlite3_errmsg(_database));
        return nil;

    }

在这里，您无需使用

解锁打开的数据库即可返回

sqlite3_close(_database);

返回这里之前关闭数据库。还要检查代码中其他地方是否存在类似问题。

     else{
                   sqlite3_close(_database);
  ////NSLog(@"Error msg : %s ", sqlite3_errmsg(_database));
            return nil;

        }

为避免类似错误，创建用于处理数据库函数的类并创建它的共享实例。

#import <Foundation/Foundation.h>
#import <sqlite3.h>

@interface DBManager : NSObject
{
    NSString *databasePath;
}

+(DBManager*)getSharedInstance;
@end


#import "DBManager.h"
static DBManager *sharedInstance = nil;
static sqlite3 *database = nil;
static sqlite3_stmt *statement = nil;

@implementation DBManager

+(DBManager*)getSharedInstance{
    if (!sharedInstance) {
        sharedInstance = [[super allocWithZone:NULL]init];
        [sharedInstance openDB];
    }
    return sharedInstance;
}

-(BOOL)openDB{

//打开数据库的代码，赋值给database，返回状态

        }