0

When I update some data, sometimes it throws a exception: Database is locked. The code below:

public int DisableSalesBySalesID(string SalesID)
{
    int count = 0;
    try
    {
        using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
        {
            conn.Open();
            string sql = @"update SalesMaster set Disabled = '1' where SalesID=@SalesID";
            using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
            {
                cmd.Parameters.Add(new SQLiteParameter("@SalesID", SalesID));
                count = cmd.ExecuteNonQuery();
            }
        }
    }
    catch (Exception ex)
    {
    }
    return count;
}

And, it depends on SalesID. Some SalesID will be OK, but the others will make the exception. Why?

m0nhawk
  • 22,980
  • 9
  • 45
  • 73
Prince
  • 199
  • 1
  • 3
  • 14
  • 5
    Presumably another query has a lock for that SalesID at the moment you run the update. This error specifically means it is your own process, not a different process, creating the lock. Do you have a SELECT still pending when you call this code? – Eric J. Oct 12 '15 at 15:58
  • http://stackoverflow.com/questions/17592671/sqlite-database-locked-exception?rq=1 – Eric J. Oct 12 '15 at 15:59
  • You could apply the all mighty hackish approach to your query, `WITH (NOLOCK)` or `(nolock)`. Except that would be applied to almost all your `SELECT` queries. – Greg Oct 12 '15 at 16:13
  • Does your program have multiple threads that connect to the same database? – dan04 Oct 12 '15 at 22:56
  • Thank you everyone , I knew the program has multiple threads that connect to the same database or the connection doesn't dispose will make this problem. But I have focus on the update command, and forgot the select command before and after the update command. It has been resolved when i put the using blocks in the select command. Thank you！ – Prince Oct 13 '15 at 01:05

1 Answers1

0

a common cause is that you have a dangling sqlcommand. sqlcommands hold locks on the database. same for sqlreaders. Make sure they are all in using blocks

pm100
  • 48,078
  • 23
  • 82
  • 145