Re: [sqlite] sqlite3_busy_timeout not avoiding "database is locked" errors with multi-threaded updates

[Stephen C. Gilardi]

Of course I could implement this in the application code, but I'd 
rather understand why sqlite isn't able to do this first. The sqlite 
source is unfamiliar territory, but I'll have a poke around and try to 
trace how the busy callback is used.
Based on following the discussions on the mailing list and on my own 
experience, I believe the correct answer is for you to implement the 
retry in your application code.  Sqlite is coded to return SQLITE_BUSY 
in cases where (in the current implementation) not doing so would lead 
to deadlock.  The retry logic internal to sqlite is located at a level 
such that it can't do the same kind of retrying that you can do from 
the application.

For me, this shows up as an SQLITE_BUSY return from sqlite3_step when 
using the C API.  I've enclosed below an outline of busy-handling 
application code that's working for me.  Please note that I've called 
the busy handler that's passed to sqlite's busy handling mechanism 
"table_busy_handler", in contrast to my own application-level busy 
handler that I've called "database_busy_handler".

I'd welcome corrections to my understanding, reasoning, or naming based 
on any review of the code below.

Here's the C++-ish pseudocode:
	
    int table_busy_handler(void* inArgs, int inBusyCount) {
        // sleep a relatively short and somewhat randomized amount of 
time sensitive to inBusyCount
        return (inBusyCount is too high) ? 0 : 1;
    }

    int database_busy_handler(void* inArgs, int inBusyCount) {
        // sleep a relatively long and somewhat randomized amount of 
time sensitive to inBusyCount
        return (inBusyCount is too high) ? 0 : 1;
    }

    void Connection::Open(const std::string& inDatabasePath)
    {
        int attempts = 0;
    retry:
        int theStatus = sqlite3_open(inDatabasePath.c_str(), 
&mDatabase);
        if (theStatus == SQLITE_BUSY && database_busy_handler(0, 
++attempts))
            goto retry;
        if (theStatus != SQLITE_OK)
            throw std::runtime_error(std::string("Could not open 
database: ") + inDatabasePath);
        sqlite3_busy_handler(mDatabase, table_busy_handler, 0);
    }

    bool Cursor::Step() {
        int attempts = 0;
    retry:
        int theStatus = sqlite3_step(mVM);
        if (theStatus == SQLITE_ROW)
            return true;
        if (theStatus == SQLITE_BUSY && database_busy_handler(0, 
++attempts))
            goto retry;
        if (theStatus != SQLITE_DONE)
            mConnection.ThrowIfError(theStatus);
        return false;
    }

--Steve