[sqlite] Database is locked exceptions

[Mike McWhinney]

Hello,

I have written a program in C# Visual Studio Winforms. In general the SQLite 
database is working quite well.
However, lately we have been plagued with database locking errors:


Here is the exeption:

database is locked
database is locked
   at System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt)
   at System.Data.SQLite.SQLiteDataReader.NextResult()
   at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, 
CommandBehavior behave)
   at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior 
behavior)
   at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery(CommandBehavior 
behavior)
   at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()
   at WindowsApplication1.Form1.logExceptionToTable()
   at WindowsApplication1.Form1.MyExceptionHandler(Object sender, 
ThreadExceptionEventArgs e)
   at 
System.Windows.Forms.Application.ThreadContext.OnThreadException(Exception
 t)
   at System.Windows.Forms.Control.WndProcException(Exception e)
   at 
System.Windows.Forms.Control.ControlNativeWindow.OnThreadException(Exception
 e)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg,
 IntPtr wparam, IntPtr lparam)
   at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG&
 msg)
   at 
System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32
 dwComponentID, Int32 reason, Int32 pvLoopData)
   at 
System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32
 reason, ApplicationContext context)
   at 
System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 
reason, ApplicationContext context)
   at System.Windows.Forms.Application.RunDialog(Form form)
   at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
   at System.Windows.Forms.Form.ShowDialog()
   at 
WindowsApplication1.LabTestPatientForm.enterTestDataButton_Click(Object 
sender, EventArgs e)
   at System.Windows.Forms.Control.OnClick(EventArgs e)
   at DevComponents.DotNetBar.ButtonX.OnClick(EventArgs e)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, 
MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at DevComponents.DotNetBar.ButtonX.WndProc(Message& m)
   at 
System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& 
m)
   at 
System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg,
 IntPtr wparam, IntPtr lparam)



The database resides on a network on a Windows Server 2008 R2 system. The users 
have full read/write
permissions to the database file.

The code that I'm using to access the database is:

 OMConnection.sqConn = new SQLiteConnection(

"URI=file:mydb.db; default timeout=10; Pooling=True; Max Pool Size=100;";


            sqConn.Open();

          

            string sql = "SELECT " +
                "*" +
                " FROM APPOINTMENTTYPES";

        
         
            SQLiteCommand cmd = OMConnection.sqConn.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;

            SQLiteDataReader dr = cmd.ExecuteReader();
            DataTable myTable = new DataTable();
            myTable.Load(dr);
            //put the categories into the
            for (int rowIndex = 0; rowIndex < myTable.Rows.Count; rowIndex++)
            {
                dataSet1.Tables["ReasonForVisit"].Rows.Add("");
                
dataSet1.Tables["ReasonForVisit"].Rows[rowIndex]["ReasonForVisitName"] =
                     myTable.Rows[rowIndex]["Name"].ToString().Trim();
                dataSet1.Tables["ReasonForVisit"].Rows[rowIndex]["Duration"] =
                     myTable.Rows[rowIndex]["Duration"];
           
            }
            myTable.Dispose();

            OMConnection.sqConn.Close();
            OMConnection.sqConn.Dispose();
            OMConnection.sqConn = null;


The thing is, the database locking errors are random and do not always occur. I 
am using the 

c# ADO provider, version 1.0.94.0 (version 3.8.6 of SQLite).  

One thing is that I have tried a method where I open the database and leave it 
open through out the life
of the program (open it when I start the program and close on exit). Should I 
perhaps change this so that 
I open the database, perform the query, then close it right away?

Thanks
Mike
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users