If you've gotten an Exception stack trace in SQLite.net that looks like the one below, please read.
Library used incorrectly
library routine called out of sequence: at System.Data.Common.DbDataAdapter.UpdatingRowStatusErrors(RowUpdatingEventArgs rowUpdatedEvent, DataRow dataRow)
at System.Data.Common.DbDataAdapter.Update(DataRow[ dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataTable dataTable) ...
I tracked the cause to a concurrency issue within sqlite.net (1.0.48). The problem is that while an UPDATE, INSERT OR DELETE is occuring on one thread, the GC (garbage collector) thread is also invoking sqlite3 routines on its own thread - which eventually leads to an SQliteException with a code SQLITE_MISUSE (21). Below is a detailed explaination of the problem and, also, my quick solution.
Since ansi C doesn't provide synchronization devices such as semaphores and mutexes, the sqlite3 library uses flags to indicate that a thread is occupying a critical section of code. This is done via Sqlite3SafetyOn() and Sqlite3SafetyOff() functions. Any two successive calls to either of these functions causes the database "context" to be set to an invalid state, which then causes any sqlite3 routine (that validates the database context) to return an error of SQLITE_MISUSE (21). In short, the sqlite3 library is not thread safe.
When manipulating (INSERT, UPDATE, DELETE) an sqlite3 database via Sqlite.net, Sqlite3SafetyOn() is called routinely, with each call correctly being matched with a call to Sqlite3SafetyOff(). However, the GC (garbage collector) cleans up SQLiteStatementHandles behind the scenes on its own thread. During this clean-up, the SQLiteStatementHandle.ReleaseHandle() method ultimately invokes the Sqlite3SafetyOn() routine in sqlite3 library. This eventually leads to two successive calls to Sqlite3SafetyOn() (one from the INSERT/UPDATE/DELETE thread, the second from the GC thread). When this occurs, the database context ( "sqlite3.magic" ) within the sqlite3 library is set to an invalid state (SQLITE_MAGIC_ERROR). The next sqlite3 routine/function that validates this database context will return an error code of SQLITE_MISUSE (21). When Sqlite.NET encounters this error code, it throws an SqliteException, containing this error code. Below are 2 separate call stacks from my application which both end up invoking the Sqlite3SafetyOn() routine simutaneously (Note: the stack frames within the sqlite3 library are not included).
This call stack belongs to my INSERT/UPDATE thread...
at System.Data.SQLite.SQLite3.ColumnMetaData(String dataBase, String table, String column, String& dataType, String& collateSequence, Boolean& notNull, Boolean& primaryKey, Boolean& autoIncrement) in C:\Documents and Settings\eadams\My Documents\My Downloads\SQLite-1.0.48.0-source\System.Data.SQLite\SQLite3.cs:line 405
at System.Data.SQLite.SQLiteDataReader.GetSchemaTable(Boolean wantUniqueInfo, Boolean wantDefaultValue) in C:\Documents and Settings\eadams\My Documents\My Downloads\SQLite-1.0.48.0-source\System.Data.SQLite\SQLiteDataReader.cs:line 601
at System.Data.SQLite.SQLiteDataReader.GetSchemaTable() in C:\Documents and Settings\eadams\My Documents\My Downloads\SQLite-1.0.48.0-source\System.Data.SQLite\SQLiteDataReader.cs:line 509
at System.Data.SQLite.SQLiteCommandBuilder.GetSchemaTable(DbCommand sourceCommand) in C:\Documents and Settings\eadams\My Documents\My Downloads\SQLite-1.0.48.0-source\System.Data.SQLite\SQLiteCommandBuilder.cs:line 304
at System.Data.Common.DbCommandBuilder.BuildCache(Boolean closeConnection, DataRow dataRow, Boolean useColumnsForParameterNames)
at System.Data.Common.DbCommandBuilder.RowUpdatingHandlerBuilder(RowUpdatingEventArgs rowUpdatingEvent)
at System.Data.Common.DbCommandBuilder.RowUpdatingHandler(RowUpdatingEventArgs rowUpdatingEvent)
at System.Data.SQLite.SQLiteCommandBuilder.RowUpdatingEventHandler(Object sender, RowUpdatingEventArgs e) in C:\Documents and Settings\eadams\My Documents\My Downloads\SQLite-1.0.48.0-source\System.Data.SQLite\SQLiteCommandBuilder.cs:line 101
at System.Data.SQLite.SQLiteDataAdapter.OnRowUpdating(RowUpdatingEventArgs value) in C:\Documents and Settings\eadams\My Documents\My Downloads\SQLite-1.0.48.0-source\System.Data.SQLite\SQLiteDataAdapter.cs:line 127
at System.Data.Common.DbDataAdapter.Update(DataRow[ dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet)
at Commoditrack.CommoditrackDataStore.Update(CTBaseData data) in C:\Development\gold\trunk\source\DotNet\Commoditrack\Utils\Data\CommoditrackDataStore.cs:line 100
at Commoditrack.CTDataManager.UpdateData2(TopicEntry entry, CTBaseData newObject, CommoditrackDataStore dataStore, String messageTime) in C:\Development\gold\trunk\source\DotNet\Commoditrack\Net\CTDataManager.cs:line 808
at Commoditrack.CTDataManager.ProcessDataMessage(Message msg, CommoditrackDataStore dataStore) in C:\Development\gold\trunk\source\DotNet\Commoditrack\Net\CTDataManager.cs:line 991
at Commoditrack.CTDataManager.ProcessDataMessages() in C:\Development\gold\trunk\source\DotNet\Commoditrack\Net\CTDataManager.cs:line 543
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.runTryCode(Object userData)
at System.Runtime.CompilerServices.RuntimeHelpers.ExecuteCodeWithGuaranteedCleanup(TryCode code, CleanupCode backoutCode, Object userData)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
This call stack belongs to the GC (garbage collector thread).
at System.Data.SQLite.SQLiteBase.FinalizeStatement(SQLiteStatementHandle stmt) in C:\Documents and Settings\eadams\My Documents\My Downloads\SQLite-1.0.48.0-source\System.Data.SQLite\SQLiteBase.cs:line 180
at System.Data.SQLite.SQLiteStatementHandle.ReleaseHandle() in C:\Documents and Settings\eadams\My Documents\My Downloads\SQLite-1.0.48.0-source\System.Data.SQLite\UnsafeNativeMethods.cs:line 148
at System.Runtime.InteropServices.CriticalHandle.Cleanup()
at System.Runtime.InteropServices.CriticalHandle.Dispose(Boolean disposing)
at System.Runtime.InteropServices.CriticalHandle.Finalize()
My crude solution was to synchronize the interop calls made from the SQLite.net assembly into sqlite3 native library. The SQLite.net team may have a better solution (design wise), but nonetheless, the GC thread and the application's data maniuplation thread cannot be allowed to invoke the sqlite3 library with out concurrency control.
A snippet of my synchronized wrapper...
internal sealed class SynchronizedUnsafeNativeMethods
{
private static readonly object _syncRoot = new object();public static int sqlite3_finalize_interop(SQLiteStatementHandle stmnt)
{
lock (_syncRoot)
{
return UnsafeNativeMethods.sqlite3_finalize_interop(stmnt);
}
}
public static int sqlite3_table_column_metadata_interop(IntPtr db, byte[ dbName, byte[ tblName, byte[ colName, out IntPtr ptrDataType, out IntPtr ptrCollSeq, out int notNull, out int primaryKey, out int autoInc, out int dtLen, out int csLen)
{
lock (_syncRoot)
{
return UnsafeNativeMethods.sqlite3_table_column_metadata_interop(db, dbName, tblName, colName, out ptrDataType, out ptrCollSeq, out notNull, out primaryKey, out autoInc, out dtLen, out csLen);
}
}
....
Eugene J. Adams - Sofware Developer, ICE Risk
IntercontinentalExchange | ICE