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: 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);
  //do stuff

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))
  using(SqliteCommand sqlCom = new SqliteCommand(sqlCon, sqlStr))

     using(SqliteDataReader rdr = sqlCom.ExecuteReader())
        //do data stuff

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

No comments:

Post a Comment