Detect blocking DB issue

Question

I am having difficulties with a database is locked database is locked Source: System.Data.SQLite

The thing here is that I am running the same method in two different apps, in one app the method is excecuted fine and in the other one I get this exception. I know that I can't insert at the same time from two or more threads, so I try calling the method at the very beggining just to test and the result is the same.

The method is the following:

internal static void TransferTable()
        {
            String full_path = System.Reflection.Assembly.GetCallingAssembly().GetName().CodeBase;
            String directory_path = full_path.Substring(0, full_path.LastIndexOf("\\"));
            string pathDB = System.IO.Path.Combine(directory_path, "Data\\DB1.s3db");
            conString = @" Data Source = " + pathDB;

            using (SQLiteConnection con = new SQLiteConnection(conString))
            {
                con.Open();
                try
                {
                    using (SQLiteCommand cmd = con.CreateCommand())
                    {
                        cmd.CommandText = "ATTACH '" + pathDB + "' AS TOMERGE";

                        int retval = 0;
                        try
                        {
                            retval = cmd.ExecuteNonQuery();
                        }
                        catch (Exception ex) { Log.Fatal(ex.Message); }

                        string SQL = "INSERT OR REPLACE INTO Person SELECT * FROM TOMERGE.Person";

                        cmd.CommandText = SQL;
                        retval = 0;
                        try
                        {
                            retval = cmd.ExecuteNonQuery();
                        }
                        catch (Exception ex) { Log.Fatal(ex.Message); }
                    }
                }
                catch (Exception ex) { Log.Fatal(ex.Message); }
                finally { con.Close(); GC.Collect(); }
            }
        }

How can I detect which is the problem?

Answer 1

Normally sqLite handles file locking, shared and exclusive access by itself.

To gain control on when which thread gains a lock, you may use BEGIN [TRANSACTION] and finally COMMIT (see http://www.sqlite.org/lockingv3.html)

Alternatively you can use PRAGMA locking_mode = exclusive plus BEGIN EXCUSIVE / COMMIT.

Then check in the processes/threads that access the database for locking status and retry after some time.