Sqlite Database Locked Exception

SQLite Database Locked exception

Somewhere along the way a connection is getting left open. Get rid of OpenConnection and CloseConnection and change ExecuteNonQuery to this:

using (SQLiteConnection c = new SQLiteConnection(ConnectionString))
{
    c.Open();
    using (SQLiteCommand cmd = new SQLiteCommand(sql, c))
    {
        cmd.ExecuteNonQuery();
    }
}

Further, change the way you read data to this:

using (SQLiteConnection c = new SQLiteConnection(ConnectionString))
{
    c.Open();
    using (SQLiteCommand cmd = new SQLiteCommand(sql, c))
    {
        using (SQLiteDataReader rdr = cmd.ExecuteReader())
        {
            ...
        }
    }
}

Do not attempt, to manage connection pooling on your own like you are here. First, it's much more complex than what you have coded, but second, it's handled already inside the SQLiteConnection object. Finally, if you're not leveraging using, you're not disposing these objects properly and you end up with issues like what you're seeing now.

sqlite database is locked exception

From the code provided it seems that you do not close the connecttion.

Pay attention also on fact, don't know if it's relevant in your case or not, that the Sqlite not very good on supporting concurent inserts and sometimes db got lock, if you operate on it from multiple threads.

SQLite: Database Is locked C#

The culprit was the reader, although I closed all the connection with the using parameter, i forgot to close all the readers i used, I should be utilizing using here too, but you get the idea. Database is locked with the readers unclosed

 public static void LoadScannedUser(string ID)
        {
            string sql = "SELECT * FROM Users WHERE ID = '" + ID + "'";

            using (var conn = new SQLiteConnection(ConnectionString))
            {
                using (var cmd = conn.CreateCommand())
                {

                    try
                    {
                        cmd.CommandText = sql;
                        conn.Open();
                        SQLiteDataReader reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            scannedUser.ID = reader.GetString(1);
                            scannedUser.Password = reader.GetString(2);
                            scannedUser.PPicture = Cryptorizer.Base64ToImage(reader.GetString(3));
                            scannedUser.FName = reader.GetString(4);
                            scannedUser.MName = reader.GetString(5);
                            scannedUser.LName = reader.GetString(6);
                            scannedUser.Agency = reader.GetString(7);
                            scannedUser.Position = reader.GetString(8);
                        }
                        reader.Close();
                    }

                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }

                }
            }

        }

Android: SQLite database locked exception on first getWritableDatabase() execution attempt after onUpgrade

onCreate() and onUpgrade() already execute in a transaction, and sqlite does not really support nested transactions.

Remove the BEGIN TRANSACTION and COMMIT from your SQL scripts.

---

Related Topics