Database file is inexplicably locked during SQLite commit

Question

I'm performing a large number of INSERTS to a SQLite database. I'm using just one thread. I batch the writes to improve performance and have a bit of security in case of a crash. Basically I cache up a bunch of data in memory and then when I deem appropriate, I loop over all of that data and perform the INSERTS. The code for this is shown below:

    public void Commit()
    {
        using (SQLiteConnection conn = new SQLiteConnection(this.connString))
        {
            conn.Open();
            using (SQLiteTransaction trans = conn.BeginTransaction())
            {
                using (SQLiteCommand command = conn.CreateCommand())
                {
                    command.CommandText = "INSERT OR IGNORE INTO [MY_TABLE] (col1, col2) VALUES (?,?)";

                    command.Parameters.Add(this.col1Param);
                    command.Parameters.Add(this.col2Param);

                    foreach (Data o in this.dataTemp)
                    {
                        this.col1Param.Value = o.Col1Prop;
                        this. col2Param.Value = o.Col2Prop;

                        command.ExecuteNonQuery();
                    }
                }
                this.TryHandleCommit(trans);
            }
            conn.Close();
        }
    }

I now employ the following gimmick to get the thing to eventually work:

    private void TryHandleCommit(SQLiteTransaction trans)
    {
        try
        {
            trans.Commit();
        }
        catch (Exception e)
        {
            Console.WriteLine("Trying again...");
            this.TryHandleCommit(trans);
        }
    }

I create my DB like so:

    public DataBase(String path)
    {
        //build connection string
        SQLiteConnectionStringBuilder connString = new SQLiteConnectionStringBuilder();
        connString.DataSource = path;
        connString.Version = 3;
        connString.DefaultTimeout = 5;
        connString.JournalMode = SQLiteJournalModeEnum.Persist;
        connString.UseUTF16Encoding = true;

        using (connection = new SQLiteConnection(connString.ToString()))
        {
            //check for existence of db
            FileInfo f = new FileInfo(path);

            if (!f.Exists)  //build new blank db
            {
                SQLiteConnection.CreateFile(path);
                connection.Open();

                using (SQLiteTransaction trans = connection.BeginTransaction())
                {
                    using (SQLiteCommand command = connection.CreateCommand())
                    {
                        command.CommandText = DataBase.CREATE_MATCHES;
                        command.ExecuteNonQuery();

                        command.CommandText = DataBase.CREATE_STRING_DATA;
                        command.ExecuteNonQuery();
                        //TODO add logging
                    }
                    trans.Commit();
                }
                connection.Close();
            }
        }            
    }

I then export the connection string and use it to obtain new connections in different parts of the program.

At seemingly random intervals, though at far too great a rate to ignore or otherwise workaround this problem, I get unhandled SQLiteException: Database file is locked. This occurs when I attempt to commit the transaction. No errors seem to occur prior to then. This does not always happen. Sometimes the whole thing runs without a hitch.

• No reads are being performed on these files before the commits finish.
• I have the very latest SQLite binary.
• I'm compiling for .NET 2.0.
• I'm using VS 2008.
• The db is a local file.
• All of this activity is encapsulated within one thread / process.
• Virus protection is off (though I think that was only relevant if you were connecting over a network?).
• As per Scotsman's post I have implemented the following changes:
• Journal Mode set to Persist
• DB files stored in C:\Docs + Settings\ApplicationData via System.Windows.Forms.Application.AppData windows call
• No inner exception
• Witnessed on two distinct machines (albeit very similar hardware and software)
• Have been running Process Monitor - no extraneous processes are attaching themselves to the DB files - the problem is definitely in my code...

Does anyone have any idea whats going on here?

I know I just dropped a whole mess of code, but I've been trying to figure this out for way too long. My thanks to anyone who makes it to the end of this question!

brian

UPDATES:

Thanks for the suggestions so far! I've implemented many of the suggested changes. I feel that we are getting closer to the answer...however...

The code above technically works however it is non-deterministic! It is not guaranteed to do anything aside from spin in neutral forever. In practice it seems to work somewhere between the 1st and 10th iteration. If i batch my commits at a reasonable interval damage will be mitigated but I really do not want to leave things in this state...

More suggestions welcome!

Answer 1

It looks like you failed to link the command with the transaction you've created. Instead of:

using (SQLiteCommand command = conn.CreateCommand())

You should use:

using (SQLiteCommand command = new SQLiteCommand("<INSERT statement here>", conn, trans))

Or you can set its Transaction property after its construction.

While we are at it - your handling of failures is incorrect:

The command's ExecuteNonQuery method can also fail and you are not really protected. You should change the code to something like:

   public void Commit()
    {
        using (SQLiteConnection conn = new SQLiteConnection(this.connString))
        {
            conn.Open();
            SQLiteTransaction trans = conn.BeginTransaction();
            try
            {
                using (SQLiteCommand command = conn.CreateCommand())
                {
                    command.Transaction = trans; // Now the command is linked to the transaction and don't try to create a new one (which is probably why your database gets locked)
                    command.CommandText = "INSERT OR IGNORE INTO [MY_TABLE] (col1, col2) VALUES (?,?)";

                    command.Parameters.Add(this.col1Param);
                    command.Parameters.Add(this.col2Param);

                    foreach (Data o in this.dataTemp)
                    {
                        this.col1Param.Value = o.Col1Prop;
                        this. col2Param.Value = o.Col2Prop;

                        command.ExecuteNonQuery();
                    }
                }

                trans.Commit();
            }
            catch (SQLiteException ex)
            {
                // You need to rollback in case something wrong happened in command.ExecuteNonQuery() ...
                trans.Rollback();
                throw;
            }
        }
    }

Another thing is that you don't need to cache anything in memory. You can depend on SQLite journaling mechanism for storing incomplete transaction state.

Answer 2

Run Sysinternals Process Monitor and filter on filename while running your program to rule out if any other process does anything to it and to see what exacly your program is doing to the file. Long shot, but might give a clue.

Answer 3

We had a very similar problem using nested Transactions with the TransactionScope class. We thought all database actions occurred on the same thread...however we were caught out by the Transaction mechanism...more specifically the Ambient transaction.

Basically there was a transaction higher up the chain which, by the magic of ado, the connection automatically enlisted in. The result was that, even though we thought we were writing to the database on a single thread, the write didn't really happen until the topmost transaction was committed. At this 'indeterminate' point the database was written to causing it to be locked outside of our control.

The solution was to ensure that the sqlite database did not directly take part in the ambient transaction by ensuring we used something like:

using(TransactionScope scope = new TransactionScope(TransactionScopeOptions.RequiresNew))
{
  ...
  scope.Complete()
}