SQLite keeps locking my database when doing SELECT + UPDATE (C#)

Question

I am trying to doing this:

Read a row from an SQLite db (in GetRuleByID() method)
Update the same row that I just read during (1) (See UpdateStatusForRuleID() method)

However my problem is that SQLite locks the database after the SELECT in GetRuleByID() so that update in UpdateStatusForRuleID() is only successful when called the first time.

I have tried enabling Write-Ahead-Logging in SQLite as well as PRAGMA read_uncommitted=1 in order to avoid SQLite locking the database for the SELECT, but this does not appear to work.

This should be simple but I have so far spent a complete night trying to solve this... Please help !

private static MicroRuleEngine.Rule GetRuleByID(int ruleID, SQLiteConnection connection, out Dictionary<string, string> dict)
{
    dict = new Dictionary<string, string>();

    string sql = String.Format("select * from rules WHERE ID = {0} ", ruleID.ToString());
    SQLiteCommand command = new SQLiteCommand(sql, connection);
    SQLiteDataReader reader = command.ExecuteReader();

    if (reader.HasRows)
    {
        reader.Read();

        // Convert row into a dictionary
        for (int lp = 0; lp < reader.FieldCount; lp++)
        {
            dict.Add(reader.GetName(lp), reader.GetValue(lp) as string);
        }

        string json = dict["fulljson"];
        MicroRuleEngine.Rule r = Newtonsoft.Json.JsonConvert.DeserializeObject<MicroRuleEngine.Rule>(json);

        //command.Dispose();              

        return r;
    }

}

internal static void UpdateStatusForRuleID(SQLConnectionManager DBMANAGER, int ruleID, bool status)
{
    Dictionary<string, string> dict = null;

    string dbVal = (status) ? "1" : "0";
    MicroRuleEngine.Rule r = null;
    string newJSON = null;

    using (SQLiteConnection connection = DBMANAGER.CreateConnection())
    {
        r = GetRuleByID(ruleID, connection, out dict);
        r.Active = (status);
        newJSON = Newtonsoft.Json.JsonConvert.SerializeObject(r);

        Thread.Sleep(1000);

        string sql = "UPDATE rules SET active = @a, fulljson=@j WHERE ID = @i";

        using (var command = new SQLiteCommand(sql, connection))
        {
            command.Parameters.Add(new SQLiteParameter("@a", dbVal));
            command.Parameters.Add(new SQLiteParameter("@i", ruleID));
            command.Parameters.Add(new SQLiteParameter("@j", newJSON));

            command.ExecuteNonQuery();  // Database is locked here ???
        }

        connection.Close();
    }

}

1. from above code I did not see the relationship between two methods 2. what do you mean by 'lock' and how to determine whether locked? — Lei Yang, Feb 24 '17 at 06:13
On first part make a using-block for your command object. In second part the connection.Close(); can be left, because you implement using-block and .Close(); internally just call .Dispose(); which is done by your using-block. Further is there any reason why you are using Thread.Sleep(1000);??? — Sebi, Feb 24 '17 at 06:19
Well the calling code calls UpdateStatusForRuleID() which then calls GetRuleByID() before doing the actual UPDATE command. — Henrik Skov, Feb 24 '17 at 06:20
@sabi The reason for Thread.Sleep() was that the UpdateStatusForRuleID() is successful on first call only and it seem to be needed in orer for the SQLite read lock that GetRuleByID() seems to "create" and the Thread.sleep() was just a way to try to wait for the lock to be released... — Henrik Skov, Feb 24 '17 at 06:24
@sebi No multi-threading. By locking I mean that SQLite throws an exception on this line: "command.ExecuteNonQuery(); // Database is locked here ???" telling me that "Databae is locked". Btw, CreateConnection() already calls Open() on the connection — Henrik Skov, Feb 24 '17 at 06:34
@HenrikSkov Maybe you can finde something helpful here: http://stackoverflow.com/questions/151026/how-do-i-unlock-a-sqlite-database Further do you close the Connection, which you get in SELECT Method anywhere? If this stay open, this may be the problem. — Sebi, Feb 24 '17 at 06:52

score 0 · Answer 1 · answered Feb 24 '17 at 08:47

"Database is locked" means that some other connection (in the same or some other process) still has an active transaction.

You don't need multiple connections (unless you are using multiple threads); just use a single connection object for all database accesses.

Ensure that all command, reader, and transaction objects (and connections, if you decide to use temporary ones) are properly cleaned up, by using using:

using (var command = new SQLiteCommand(sql, connection))
using (var reader = command.ExecuteReader())
{
    if (reader.HasRows)
    ...
}

score 0 · Answer 2 · answered Feb 24 '17 at 14:06

Apparently, the code below works. I basically dropped the GetRuleByID() method (but then I had to re-write 4 other methods)

Thanks to all who provided input.

        internal static void UpdateStatusForRuleID(SQLConnectionManager DBMANAGER, int ruleID, bool status)
    {
        string dbVal = (status) ? "1" : "0";
        MicroRuleEngine.Rule r = null;
        string newJSON = null;

        using (SQLiteConnection conn = DBMANAGER.CreateConnection())
        {
            string sql = String.Format("select * from rules WHERE ID = {0} ", ruleID.ToString());

            using (var command = new SQLiteCommand(sql, conn))
            using (var reader = command.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    reader.Read();

                    string json = reader["fulljson"].ToString();
                    r = Newtonsoft.Json.JsonConvert.DeserializeObject<MicroRuleEngine.Rule>(json);
                    r.Active = (status);
                    newJSON = Newtonsoft.Json.JsonConvert.SerializeObject(r);

                    string sql2 = "UPDATE rules SET active = @a, fulljson=@j WHERE ID = @i";

                    using (var command2 = new SQLiteCommand(sql2, conn))
                    {
                        command2.Parameters.Add(new SQLiteParameter("@a", dbVal));
                        command2.Parameters.Add(new SQLiteParameter("@i", ruleID));
                        command2.Parameters.Add(new SQLiteParameter("@j", newJSON));

                        command2.ExecuteNonQuery(); 
                    }
                }
            }
        }
    }

SQLite keeps locking my database when doing SELECT + UPDATE (C#)

2 Answers2