SQL Server Compact 4.0 random Access Violation exceptions with multi-threaded application

Question

I have a multi-threaded WPF application with background threads performing queries into a SQL CE database. I have been running the app with SQL CE 3.5 SP2 and have been experiencing occasional File Sharing Exceptions (on tmp files) during a query performed by such a background thread. So I tried to upgrade to SQL CE 4.0. Now I am getting occasional Access Violation Exceptions such as:

System.AccessViolationException was unhandled
  Message=Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
  Source=System.Data.SqlServerCe
  StackTrace:
       at System.Data.SqlServerCe.NativeMethodsHelper.CloseAndReleaseStore(IntPtr& pSeStore)
       at System.Data.SqlServerCe.NativeMethods.CloseAndReleaseStore(IntPtr& pSeStore)
       at System.Data.SqlServerCe.SqlCeConnection.ReleaseNativeInterfaces()
       at System.Data.SqlServerCe.SqlCeConnection.Close(Boolean silent)
       at System.Data.SqlServerCe.SqlCeConnection.Close()
       at System.Data.Linq.SqlClient.SqlConnectionManager.CloseConnection()
       at System.Data.Linq.SqlClient.SqlConnectionManager.ReleaseConnection(IConnectionUser user)
       at System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReaderSession`1.Dispose()
       at System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReaderSession`1.CheckNextResults()
       at System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReaderBase`1.Read()
       at System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReader`2.MoveNext()
       at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
       at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

or

System.AccessViolationException was unhandled
  Message=Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
  Source=System.Data.SqlServerCe
  StackTrace:
       at System.Data.SqlServerCe.NativeMethodsHelper.OpenStore(IntPtr pOpenInfo, IntPtr pfnOnFlushFailure, IntPtr& pStoreService, IntPtr& pStoreServer, IntPtr& pQpServices, IntPtr& pSeStore, IntPtr& pTx, IntPtr& pQpDatabase, IntPtr& pQpSession, IntPtr& pStoreEvents, IntPtr& pError)
       at System.Data.SqlServerCe.NativeMethods.OpenStore(IntPtr pOpenInfo, IntPtr pfnOnFlushFailure, IntPtr& pStoreService, IntPtr& pStoreServer, IntPtr& pQpServices, IntPtr& pSeStore, IntPtr& pTx, IntPtr& pQpDatabase, IntPtr& pQpSession, IntPtr& pStoreEvents, IntPtr& pError)
       at System.Data.SqlServerCe.SqlCeConnection.Open(Boolean silent)
       at System.Data.SqlServerCe.SqlCeConnection.Open()
       at System.Data.Linq.SqlClient.SqlConnectionManager.UseConnection(IConnectionUser user)
       at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult)
       at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries)
       at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
       at System.Data.Linq.DataQuery`1.System.Linq.IQueryProvider.Execute[S](Expression expression)
       at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source)

Sometimes I do not even get an exception, the aplication just terminates, presumably because memory is corrupt.

A new SqlCeConnection object and a new DataContext object are created for each thread. The exception seems to occur randomly, but its frequency is higher than that of the exceptions I used to get with SQL CE 3.5 SP2. Does anybody have any idea of what could be the reason for those exceptions with SQL CE 4.0? Is it that it does not really support multi-threading much better than previous versions, or is there anything that I could do to avoid those problems?

Answer 1

Have you seen this? http://blogs.msdn.com/b/sqlservercompact/archive/2009/05/06/troubleshooting-access-violation-exception-while-using-sql-server-compact-database-with-ado-net-provider.aspx

---

Please mark as answer, if this was it. Visit my SQL Server Compact blog

Answer 2

Yes I have seen this article. However, I have set up SQL CE 4.0 to be privately installed as part of the application and, as far as I can see, the 4.0 dlls are being loaded, not the 3.5 ones. SQL CE 3.5 SP2 was installed on both machines on which I have tested. I could try and deinstall it to see if it makes any difference but, if so, the problem is, when the application is eventually deployed on an end-user's PC, how can I be sure that no other version of SQL CE has been installed on that machine? I was hoping that using a private installation would isolate the app from any other SQL CE installation that may have been made.

Answer 3

I think it is a thread safety issue, not a DLL mismatch issue.

---

Please mark as answer, if this was it. Visit my SQL Server Compact blog

Answer 4

You are probably right but, as I mentioned earlier, each thread has its own DataContext object and its own SqlCeConnection object. Perhaps the problem has something to do with the fact that the queries are performed using LINQ to SQL, which I understand SQL CE 4.0 is not supposed to support, but rewriting it all to use the Entity Framework would be too much work at this stage. As it is, staying with SQL CE 3.5 SP2 might be the best option, as it seems to cope a lot better with the multi-threading. With that version, an SqlCeException is occasionally thrown with a File Sharing Violation error related to accessing tmp files, but at least I can catch that particular exception and retry the query, which so far has always worked. There is obviously a performance impact but it is not too bad as it does not happen too frequently. With SQL CE 4.0, the Access Violation Exceptions occur very often and may not be recoverable (sometimes the application just terminates without any exception being thrown).