Mono SQLite fails to open database

Question

I have written a simple Mono C# application for writing to an SQLite database using the Mono implementation present in the Mono.Data.Sqlite package:

using System;
using Mono.Data.Sqlite;

class MainClass
{
    public static void Main (string[] args)
    {
        using (var dbConnection = new SqliteConnection (@"Data Source=/var/log/gmblog;Version=3;"))
        {
            dbConnection.Open();

            string sql = @"INSERT INTO ""queue"" (""data"") VALUES(""Test"")";
            using (var insertCommand = new SqliteCommand (sql, dbConnection))
            {
                insertCommand.ExecuteNonQuery();
            }
        }
    }
}

This works fine until I do an insert from another application such as sqlite3 and keeps this application running:

sqlite> insert into queue ("data") VALUES("test2");

Now the C# program hangs until it gives the following error:

Unhandled Exception: Mono.Data.Sqlite.SqliteException: The database file is locked

I don't have any problems writing to the table from other instances of sqlite3 or from a C++ application I created.

If I close the sqlite3 instance then the C# application works again.

Doing a lsof /var/log/gmblog shows that sqlite3 has obtained a reader lock after performing the INSERT:

sqlite3 15578  cup    3ur  REG   8,17    13312 4988505 /var/log/gmblog

Before the INSERT it didn't have this lock:

sqlite3 15578  cup    3u   REG   8,17    13312 4988505 /var/log/gmblog

But as I pointed out other applications do not have any problems written to the table while other applications are using the database.

Any ideas on what is wrong with my C# code? Is it a bug in the Mono implementation of SQLite?

Update 25/11

Note that it's the dbConnection.Open(); which results in the database locked error, not the insertCommand.ExecuteNonQuery();. I.e. the following code doesn't work either:

using System;
using Mono.Data.Sqlite;

class MainClass
{
    public static void Main (string[] args)
    {
        using (var dbConnection = new SqliteConnection (@"Data Source=/var/log/gmblog;Version=3;"))
        {
            dbConnection.Open();
        }
    }
}

Answer 1

I found a similar discussion on sqlite's website on multi-threading. I understand that you are not multi-threading there but instead trying to get a write-lock - but still the discussion have some useful hints that maybe helpful.

Each thread then proceeds to insert a number of records, let's say 1000. The problem you will encounter is the following: one thread will get control over the database by setting a lock on the file. This is fine, but the rest of the threads will keep on failing for each attempted INSERT while the lock is active.

Solution

Test for SQLITE_BUSY, which I didn't do originally. Here's some pseudo-code to illustrate a solution:

  while (continueTrying) {
    retval = sqlite_exec(db, sqlQuery, callback, 0, &msg);
    switch (retval) {
      case SQLITE_BUSY:
        Log("[%s] SQLITE_BUSY: sleeping fow a while...", threadName);
        sleep a bit... (use something like sleep(), for example)
        break;
      case SQLITE_OK:
        continueTrying = NO; // We're done
        break;
      default:
        Log("[%s] Can't execute \"%s\": %s\n", threadName, sqlQuery, msg);
        continueTrying = NO;
        break;
    }
  }

You may also want to try busy_timeout parameter on connection string as shown here and here.

The busy_timeout parameter is implemented as a call to sqlite(3)_busy_timeout. The default value is 0, which means to throw a SqliteBusyException immediately if the database is locked.

Answer 2

Please see the SQLiteConnection documentation: "If the SQLiteConnection goes out of scope, it is not closed. Therefore, you must explicitly close the connection by calling Close.". The example shows the connection being closed in a finally block. I can't see anywhere in your code sample where you close the connection.

You should also be able to use the "using" block which automatically calls close when the object goes out of scope:

using (var dbConnection = new SqliteConnection (@"..."))
{
    dbConnection.Open();

    string sql = @"INSERT INTO ""queue"" (""data"") VALUES(""Test"")";
    using (var insertCommand = new SqliteCommand (sql, dbConnection))
    {
        insertCommand.ExecuteNonQuery();
    }
}

This makes sure everything gets released properly and in a timely fashion.