sqlite error database is locked

Question

I am have a sqlite database in the iPhone application I am writing. I get an error with following code that I am running in a background thread. In the background thread, I call this method:

 - (BOOL) songIsInDatabase:(NSString *)songTitle
{
NSString *docsDir;
NSArray *dirPaths;

dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
docsDir = [dirPaths objectAtIndex:0];


//Build the path to the database file
NSString *databasePath = [[NSString alloc] initWithString:[docsDir stringByAppendingPathComponent:@"Database.db"]];

const char *dbpath = [databasePath UTF8String];

sqlite3_stmt *statement;

if(sqlite3_open(dbpath, &DB) == SQLITE_OK){

    NSString *insertSQL = [NSString stringWithFormat:@"select * from Bpm_Table where song_title = '%@'", songTitle];

    const char *insert_stmt = [insertSQL UTF8String];
    if(sqlite3_prepare_v2(DB, insert_stmt, -1, &statement, NULL) == SQLITE_OK)
    {
        while (sqlite3_step(statement) == SQLITE_ROW)
        {
            return YES;
            break;
        }
    }else{
        NSLog(@"the error is %s", sqlite3_errmsg(DB));
    }
    sqlite3_finalize(statement); 
}

[databasePath release];
return NO;
}

Then I call this method:

- (void) addSongToDatabase: (NSString *) songTitle andBPM: (int)bpm andGenre: (NSString *) genre
{
NSString *docsDir;
NSArray *dirPaths;

dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
docsDir = [dirPaths objectAtIndex:0];


//Build the path to the database file
NSString *databasePath = [[NSString alloc] initWithString:[docsDir stringByAppendingPathComponent:@"Database.db"]];

const char *dbpath = [databasePath UTF8String];

sqlite3_stmt *statement;

if(sqlite3_open(dbpath, &DB) == SQLITE_OK){

    NSString *insertSQL = [NSString stringWithFormat:@"insert or replace into Bpm_Table (song_title, bpm, genre) values (\"%@\", \"%d\", \"%@\")", songTitle, bpm, genre];

    const char *insert_stmt = [insertSQL UTF8String];
    if(sqlite3_prepare(DB, insert_stmt, -1, &statement, NULL) == SQLITE_OK){
        if(sqlite3_step(statement) == SQLITE_DONE)
        {

        } else {
            NSLog(@"error: %s", sqlite3_errmsg(DB));
        }
    }sqlite3_finalize(statement);
}

[databasePath release];
}

If I run both these methods, one right after the other, I get an error that says database is locked. I added the sqlite3_finalize statements after poking around google in hopes that would fix the issue. If I comment out either of the methods, I don't get this error.

Does anyone know what's wrong?

Saurabh · Accepted Answer · 2012-01-13T17:47:48.120

you must always close the sqlite database after using it ... so add this line sqlite3_close(DB); just after sqlite3_finalize(statement);

Update -

You are returning YES in one of your while loop -

        while (sqlite3_step(statement) == SQLITE_ROW)
        {
            return YES;
            break;
        }

so here you are nither finalizing nor closing the database.. you need to close if everytime you open it

score 6 · Answer 2 · answered Jan 13 '12 at 18:00

6

But note that if you access the same SQLite DB from multiple threads, without some sort of separate synchronization (some way to know that you'll never have near-simultaneous accesses from multiple threads) then you're likely to get "database locked" errors even if you close everything correctly on each access.

SQLite does not provide any sort of "lock wait" mechanism.

Your basic options are:

Do all accesses from one thread.
Use a separate lock protocol around all accesses.
If you get a "database locked" error wait a brief period of time and retry.

answered Jan 13 '12 at 18:00

Hot Licks

47,103
17
93
151

Isn't this a "lock wait" mechanism in sqlite? http://www.sqlite.org/c3ref/busy_timeout.html – Dmitry Pashkevich Aug 07 '13 at 13:34
@DmitryPashkevich - In standard SQLite there is no reliable built-in locking mechanism. What they have is intended purely to protect the integrity of the DB in case of a code bug, and perhaps enable some sort of crude recovery. – Hot Licks Aug 07 '13 at 16:33
I'm confused. So the mentioned feature doesn't work properly? I shouldn't rely on it? But it's there? – Dmitry Pashkevich Aug 08 '13 at 05:43
@DmitryPashkevich - It does what it's intended to do, which is protect the DB and prevent deadlocks. It is not, however, a sufficient lock mechanism for the general case. In particular, you'll get lots of problems with transactions. – Hot Licks Aug 08 '13 at 10:48

score 5 · Answer 3 · answered Dec 04 '12 at 07:44

5

your database is open close it using sqlite3_close(db) if you dont close then the process which accessed your database will be running background which will cause database is locked error.

if you want to remove database is locked error then follow these steps 1.copy your database file to some other location. 2.then replace the database with the copied database 3.this will dereference all processes which were accessing your database file

answered Dec 04 '12 at 07:44

vinayak jadi

859
8
16

i found it out that when i use sqlite3_reset even without closing the database it works out fine there is no database lock error.use sqlite3_reset statement after every prepare so that it will reset that prepared statement.this will solve your database is locked error. – vinayak jadi Jan 22 '13 at 13:24

turingtested · Answer 4 · 2018-12-12T08:53:38.753

If you have tried sqlite3_close(DB) it's probably because your methods are trying to access the same database at the same time. Try to place this line of code

sqlite3_busy_timeout(DB, 500);

somewhere between sqlite3_open and sqlite3_prepare_v2 in the method from where you get the "database is locked"-error message.

The database-connection in that method will then try to write/read in 500 milliseconds before it gives up, which is usually enough time to escape the locking.

score 3 · Answer 5 · edited Feb 27 '13 at 07:44

3

Your database is open. Close it using sqlite3_close(db).

If you don't close, then the process accessing your database will be running background which will cause database is locked error.

edited Feb 27 '13 at 07:44

partlov

13,789
6
63
82

answered Feb 27 '13 at 07:23

prashant wadi

41
2

user3439187 · Answer 6 · 2014-04-11T04:16:04.567

I just spent an hour with the same problem. The problem for me arose when i unwittingly killed a process by closing the shell (when the script was running a while loop and timer), while a db journal temp file was still opened. Windows does not kill the process even though you have killed it in Python, regardless if you have used db.close() or finalize in the script; if you exit all Python apps there will still be one running in the Task manager.

Hence you will need to end all Python processes in Windows Task Manager, restart them, and it should be fine(if that is actually the cause of the problem).

score 0 · Answer 7 · answered Jan 13 '12 at 17:40

0

For one thing, you're not closing the database connection with sqlite3_close() before you open a new connection

answered Jan 13 '12 at 17:40

meggar

1,219
1
10
18

score -3 · Answer 8 · edited Jun 23 '15 at 09:06

-3

I had the same problem a while ago. I tried copying the database file itself, renamed it to different filename, then checked it - It actually WORKED!

I am using SQLite3.

I hope this will also work for you!

edited Jun 23 '15 at 09:06

Chris

2,955
1
30
43

answered Jun 23 '15 at 07:22

Pinkoy Montano

1

sqlite error database is locked

8 Answers8

Linked

Related