How to avoid 'database is locked' error?

Question

I am going through the most common problem of database getting locked.

When my app runs an api is hit to fetch data. This data is then inserted into database table. I am doing this in a background thread with a serial queue. In the meantime if user presses the button to switch to the next screen, select query works(on main thread) to get the data to be displayed. That's the point when I get this issue. Even the app crashes at times.

I have searched this problem but everytime I get to see either a link to the tutorial about 'synchronisation' or using FMDB.

As I am running out of time, can somebody plz help me to provide a simple and detailed solution. Also I am posting my code meant to insert and retrieve data. Plz care to check if I am going wrong somewhere.

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



}

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


}

PlusInfosys · Accepted Answer

If database already open and locked with some thread -process, it gives this error when you try to open it (Try to open locked database)

In you getDataFromTable_VENUES_WhereGigDateIsEqualTo method,Check following:

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

    }

Here, you are returing without unlocking open database with

sqlite3_close(_database);

close database before return here. also check for similar problem everywhere else in your code.

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

        }

to avoid similar error, create class for handling database functions and create shared instance of it.

#import 
#import 

@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{

//Code to open database, assign that to database , return status

How to avoid 'database is locked' error?

Answers (2)

Related Questions

How to avoid &#39;database is locked&#39; error?

Answers (2)

Related Questions

How to avoid 'database is locked' error?