dotConnect for SQLite locking/timeout problem

[TimSylvester]

I have been seeing "The database file is locked" errors whenever there is significant contention. I have found many other posts about similar issues, and I have tried the recommendations there; disabled pooling, shared cache mode, etc., but to no avail.

I created a custom build of SQLite3.dll that writes the result of the SQLite API functions to the debug window. It quickly became clear that, immediately before producing an exception, the sqlite3_step function would be called and would return SQLITE_BUSY.

Normally, an application would wait for a small interval and call sqlite3_step again, repeatedly, until a timeout had elapsed. However this function never seems to be called more than 3 times in a row, over only a fraction of a second, before an exception is thrown. This is what the sqlite3_busy_timeout option does, using a gradually increasing wait time per iteration, when not using the advanced sqlite3_step API.

In this case, BusyTimeout is set to 30000, ConnectionTimeout and DefaultCommndTimeout are set to 30, but the locking exception occurs after just a few milliseconds.

This error seems to occur during the initialization of a SQLite connection, so I suspect that whichever timeout value controls the calls to sqlite3_step is not being initialized early enough, and so the timeout behavior only works correctly once a connection is fully established.

I will try to make some time to produce a minimal example that demonstrates this problem. I am hoping that this is enough information for you to find the issue without it, however.

Here's the exception:

Code: Select all

Devart.Data.SQLite.SQLiteException occurred
  HResult=5
  Message=The database file is locked
database is locked
  Source=Devart.Data.SQLite
  ErrorCode=5
  StackTrace:
       at Devart.Data.SQLite.a8.d(bt A_0)
  InnerException: 

This is what the call stack looks like in this condition:

Code: Select all

Devart.Data.SQLite.dll!Devart.Data.SQLite.a8.d(Devart.Data.SQLite.bt A_0) + 0x274 bytes	
Devart.Data.SQLite.dll!Devart.Data.SQLite.bt.b() + 0x5d bytes	
Devart.Data.SQLite.dll!Devart.Data.SQLite.ah.e() + 0x282 bytes	
Devart.Data.SQLite.dll!Devart.Data.SQLite.s.h() + 0x2ff bytes	
Devart.Data.SQLite.dll!Devart.Data.SQLite.s.b() + 0x28 bytes	
Devart.Data.SQLite.dll!Devart.Common.DbConnectionInternal.af() + 0x32 bytes	
Devart.Data.SQLite.dll!Devart.Common.DbConnectionFactory.b(Devart.Common.DbConnectionBase A_0) + 0x1cf bytes	
Devart.Data.SQLite.dll!Devart.Common.DbConnectionClosed.Open(Devart.Common.DbConnectionBase outerConnection) + 0x138 bytes	
Devart.Data.SQLite.dll!Devart.Common.DbConnectionBase.Open() + 0x13d bytes	
Devart.Data.SQLite.dll!Devart.Data.SQLite.SQLiteConnection.Open() + 0x213 bytes	
System.Data.Entity.dll!System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf() + 0x66 bytes	
System.Data.Entity.dll!System.Data.EntityClient.EntityConnection.Open() + 0x8e bytes	
System.Data.Entity.dll!System.Data.Objects.ObjectContext.EnsureConnection() + 0x61 bytes	
System.Data.Entity.dll!System.Data.Objects.ObjectQuery<SerializableObjectDto>.GetResults() + 0x42 bytes	
System.Data.Entity.dll!System.Data.Objects.ObjectQuery<System.__Canon>.System.Collections.Generic.IEnumerable<T>.GetEnumerator() + 0x33 bytes	
System.Core.dll!System.Linq.Enumerable.FirstOrDefault<SerializableObjectDto>(IEnumerable<SerializableObjectDto> source) + 0xdc bytes

[Pinturiccio]

Please create and send us a small test project with the corresponding DDL/DML scripts for reproducing the issue.
Please also tell us the steps for reproducing the issue and your dotConnect for SQLite version.

[TimSylvester]

I was able to put together a minimal example that demonstrates the problem. I then updated from dotConnect for SQLite 4.4.179 to 4.6.301, and that appears to resolve it.

I admit I should have tried that first, however there is no mention of such a bug fix in the change log, and there is some ... overhead to upgrading so I didn't want to take the time. There is one mention of a bug related to "working with one database file simultaneously from several processes," however this occurs within a single process. Perhaps it applies to multiple threads as well?

Anyway, I think this is probably resolved, I just need to upgrade the real app in addition to the test case. Sorry to have bothered you with it before trying the latest release.