Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.

Close

#41 Provider should retry if database locked

closed-fixed
Robert Foster
None
5
2005-08-22
2005-07-08
jongalloway
No

Inserts / Updates in SQLite cause database to be
locked. Any other Inserts / Updates throw an exception.
Provider should retry if database is locked until
CommandTimeout exceeded.

Background:

See last comment here:
http://sourceforge.net/forum/forum.php?thread_id=1292627&forum_id=325526

PySQLite handles this:
http://groups-beta.google.com/group/comp.lang.python/browse_thread/thread/475d065fa7871e63/e489037befcac1d9?q=sqlite+%22database+is+locked%22&rnum=18&hl=en#e489037befcac1d9

Discussion

  • Logged In: NO

    I think I may have solved why so many people are frustrated
    with SQLite updates using this ADO.NET provider.

    Firstly, looking at the source code I can see the command
    object supports the busy_timeout(ms) method and so setting
    the CommandTimeout property to a sensible number of
    milliseconds (eg. 10000) results in simultaneous
    inserts/updates/deletes from being handled without getting a
    SQLiteException (database is locked) error when calling a
    command.

    *However*, it appears that the SQLiteException (database is
    locked) is still thrown when opening a database connection
    rather than executing a command.

    Looking at the source in Connection.cs the
    ConnectionTimeout property is not being utilised.

    By simply adding the following few lines of code to the
    provider source I have has many simultaneous threads all
    updating the database without dataloss or errors.

    1. Declare a ConnectionTimeout member variable in
    Connection.cs. Default to say 30ms like the
    CommandTimeout does.

    internal int mConnectionTimeout = 30;

    2. Modify the property get/set

    public int ConnectionTimeout
    {
    get
    {
    return mConnectionTimeout;
    }
    set
    {
    if (value < 0)
    throw new ArgumentException();

    mConnectionTimeout = value;
    }

    }

    3. Call busy_timout before any exec's are called in the Open
    method:

    sqlite.open(pDB);

    // ADDED CALL TO BUSY TIMEOUT
    sqlite.busy_timeout(mConnectionTimeout);

    4. Compile up the changes.

    In your code you now need to set the Connection and
    Command timeout values to something sensible. E.g. if you
    have code that does a lot of updates and takes several
    seconds to complete, set the timeouts to exceed the worst
    case scenario.

    conn.ConnectionTimeout = 20000; // 20 sec
    cmd.CommandTimeout = 20000; // 20 sec

    Now the changes to the provider will result in insert/updates
    and deletes queing internally within SQLite engine rather than
    the provider throwing exceptions thanks to this built in API
    call:

    /*
    ** This routine
    sets a busy handler that sleeps for a while when a
    ** table is
    locked. The handler will sleep multiple times until
    ** at least "ms"
    milleseconds of sleeping have been done. After
    ** "ms"
    milleseconds of sleeping, the handler returns 0 which
    ** causes
    sqlite3_exec() to return SQLITE_BUSY.
    **
    ** Calling this
    routine with an argument less than or equal to zero
    ** turns off all
    busy handlers.
    */
    [DllImport("sqlite3")]
    private static extern void
    sqlite3_busy_timeout(IntPtr h, int ms);

    Hope this is useful stuff and not complete rubbish I'm
    spouting. It appears to work for me and make SQlite and the
    ADO.NET provider useable under ASP.NET for updates.

    Can the developers incorporate this simple change into the
    next release?

    Regards,

    Mike

     
  • Robert Foster
    Robert Foster
    2005-08-22

    Logged In: YES
    user_id=526041

    This has been fixed and uploaded to CVS (or will be in about
    15 minutes).

     
  • Robert Foster
    Robert Foster
    2005-08-22

    • assigned_to: nobody --> robertjf
    • status: open --> closed-fixed