Dropping SQLite index with fmbd on iOS fails with SQLITE_LOCKED

Question

I'm having some trouble dropping an index I've created in SQLite in an iOS application. I'm using fmdb.

When attempting to drop an index, sqlite3_step always returns SQLITE_LOCKED. As a result, fmdb gets caught in an infinite loop where it continuously attempts to retry the drop statement (every single time, sqlite3_step returns SQLITE_LOCKED), and the statement never succeeds.

As far as I know, there are no other processes touching the database and statements right before the drop statement work without a problem. What am I missing?

Here's pretty much a verbatim copy of the code that's failing:

[db open];
/* ... */
[db executeUpdate:@"DROP INDEX IF EXISTS bookmark_hash_idx;"];
[db close];

db is a pointer to a sqlite database in my documents directory.

Here's the relevant code from fmdb, if that's useful:

do {
    rc      = sqlite3_step(pStmt);
    retry   = NO;

    if (SQLITE_BUSY == rc || SQLITE_LOCKED == rc) {
        // this will happen if the db is locked, like if we are doing an update or insert.
        // in that case, retry the step... and maybe wait just 10 milliseconds.
        retry = YES;
        if (SQLITE_LOCKED == rc) {
            rc = sqlite3_reset(pStmt);
            if (rc != SQLITE_LOCKED) {
                NSLog(@"Unexpected result from sqlite3_reset (%d) eu", rc);
            }
        }
        /* ... */
    }
    /* ... */
} while (retry);

Answer 1

You probably have open result sets which is blocking the DROP INDEX to go through. From SQLite docs:

The "DROP TABLE" Exception

When a call to sqlite3_step() returns SQLITE_LOCKED, it is almost always appropriate to call sqlite3_unlock_notify(). There is however, one exception. When executing a "DROP TABLE" or "DROP INDEX" statement, SQLite checks if there are any currently executing SELECT statements that belong to the same connection. If there are, SQLITE_LOCKED is returned. In this case there is no "blocking connection", so invoking sqlite3_unlock_notify() results in the unlock-notify callback being invoked immediately. If the application then re-attempts the "DROP TABLE" or "DROP INDEX" query, an infinite loop might be the result.

You should call closeOpenResultSets on your FMDatabase object to ensure all open result sets are closed before dropping the indexes.