Wednesday, November 3, 2010

System.Data.Sqlite locks database, even on single threaded access

Sometimes while using the System.Data.Sqlite .Net library I noticed that when db access was finished with and the method doing the access had returned, there were still open file handles to the database file. Every subsequent access created a new perpetual file handle. I have found a way that fixes this, that works for me anyway ;-) ...



You can view a list of file handles on the database by installing the WhoLockMe explorer plugin, right click on the database file and click "WhoLockMe..." Download for free: http://www.dr-hoiby.com/WhoLockMe/. You may see your application executable in the list or w3wp.exe if its a web application (IIS web server process, although a multithreaded host app like IIS will create file handles for each thread accessing the data). 

I have even seen System.Data.Sqlite lock data files on one machine, but not lock it on another while both were running the exact same code.



//Declare sql and connection string
string sqlStr = "SELECT * FROM table";
string connStr = "Data Source=datafilename;Version=3;";

//Problem code:
SqliteConnection sqlCon = new SqliteConnection(connStr);
sqlCon.Open();
  //do stuff
sqlCon.Close();


Even after Close() is called, the database may still be locked and prevent data updates, deletions etc. Check with WhoLockMe.

This code seems to avoid the locking problem:

using (SqliteConnection sqlCon = new SqliteConnection(connStr))
{
  sqlCon.Open();
  using(SqliteCommand sqlCom = new SqliteCommand(sqlCon, sqlStr))
  {

     using(SqliteDataReader rdr = sqlCom.ExecuteReader())
     {
        //do data stuff
        rdr.Close();
     }

     sqlCon.Close(); //CLOSE sqlCon BEFORE sqlCom IS DESTROYED!
  }
}

No comments:

Post a Comment