Does SQLiteConnection.BeginTransaction retry when database is locked?

Question

I'm using SQLite (System.Data.SQLite.dll) in a multi-threaded Windows desktop program. I use this code:

using (var cn = new SQLiteConnection(connectionString))
{
    cn.Open();
    using (var tx = cn.BeginTransaction())
    {
          // do some work here

         tx.Commit();
    }
}

I'm stressing the program using 8 concurrent threads writing to the database at the same time. Sometimes I see this message on the Visual Studio output window:

SQLite error (517): statement aborts at 1: [BEGIN IMMEDIATE] database is locked 

But no exception is thrown at all, and everything works as expected. So I guess SQLConnection.BeginTrasaction() retries when it receives SQLite error (517) database is locked.

Am I right?

If yes, how many times SQLConnection.BeginTransaction retries before throwing an exception. Is this configurable?

Answer 1

This is handled by BUSY TIMEOUT:

PRAGMA busy_timeout;
PRAGMA busy_timeout = milliseconds;

Query or change the setting of the busy timeout. This pragma is an alternative to the sqlite3_busy_timeout() C-language interface which is made available as a pragma for use with language bindings that do not provide direct access to sqlite3_busy_timeout().

Each database connection can only have a single busy handler. This PRAGMA sets the busy handler for the process, possibly overwriting any previously set busy handler.

The timeout interval is defined by default to 30 seconds in SQLite.dll. Turns out you can't change that, only via invoking the unsafe C function.

From the SQLite C Interface:

int sqlite3_busy_timeout(sqlite3*, int ms);

Calling this routine with an argument less than or equal to zero turns off all busy handlers.

Answer 2

I finally found it digging into the source code of System.Data.SQLite.dll.

SQLite3.Prepare method (wich is called before executing any command) has the following code in it:

while ((n == SQLiteErrorCode.Schema || n == SQLiteErrorCode.Locked || n == SQLiteErrorCode.Busy) && retries < 3)
{
  try
  ...

So, System.Data.SQLite will retry any command up to 3 times before throwing an exception.

EDIT:

SQLite3.Prepare method has this interesting piece of code which is self-explanatory:

  else if (n == SQLiteErrorCode.Locked || n == SQLiteErrorCode.Busy) // Locked -- delay a small amount before retrying
  {
    // Keep trying
    if (rnd == null) // First time we've encountered the lock
      rnd = new Random();

    // If we've exceeded the command's timeout, give up and throw an error
    if ((uint)Environment.TickCount - starttick > timeoutMS)
    {
      throw new SQLiteException(n, GetLastError());
    }
    else
    {
      // Otherwise sleep for a random amount of time up to 150ms
      System.Threading.Thread.Sleep(rnd.Next(1, 150));
    }
  }

Note that locked and busy erros don't increment retries counter, so Prepare method will keep retrying until command timeout expires.

I created a ticket asking for max number of retries be configurable:

Configurable number of retries and sleep time of SQLite3.Prepare