dotConnect for SQLite locking/timeout problem
Posted: Wed 21 Aug 2013 23:41
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:
This is what the call stack looks like in this condition:
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:
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
