[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

Re: [sqlite] Database is locked exceptions

[Simon Slavin]

On 29 Oct 2014, at 4:13pm, Mike McWhinney elja...@sbcglobal.net wrote:

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

Can you please change your timeout to 1 (really, 1ms == 10 seconds) and 
see if this makes the problems go away ?

It may not be necessary to leave the setting like that.  But the information 
about whether this does or doesn't solve the problem may tell us what the 
problem is.

 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?

One usually keeps the database open for the life of the program, the way you 
have it working already.  So lets try other solutions first.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is locked exceptions

[Sohail Somani]

On 2014-10-29, 12:13 PM, Mike McWhinney wrote:

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


I'm not very knowledgeable about Windows forms but if you are writing 
the SQLite database from multiple threads, that could be the reason. The 
main reasons I've seen this happen are when something is trying to write 
to the database when something else has it open for a read.


If you are using threads, then ensure that reader threads exhaust their 
result sets. Unfortunately some third party libraries do lazy loading 
which doesn't work well with SQLite. Pseudo-code:


# guithread.pseudo
def fetchResults(query):
  while(query.hasMore())
query.fetchMore() # without this, the sqlite result is active...

# writethread.pseudo
def execQuery(query):
  query.exec() # ...which would lock this

If you are not using threads, then it is possible that having the DB 
itself on a network share is causing the problem. I seem to recall some 
issues along these lines in the docs but I don't have any personal 
experience.


Sohail

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