System.AccessViolationException on OleDbConnection.Query using Dapper

.net c# dapper oledb vb.net

Question

I'm using an OleDbConnection in .Net 4.0 to query a Microsoft Access 2010 database with the ACE OleDb 12.0 engine. It's possible that the process executes a high number of queries per second, since I have around 60 possible parallel threads (I set a ThreadPool limit for TPL after reading this article). The mdb file is on a network drive. I create and dispose a connection on each query, with the following code :

Using connection As New OleDb.OleDbConnection(connectionString)
    result = connection.Query(Of Foo)(query, New With {.Param1 = param1, .Param2 = param2}).FirstOrDefault()
End Using

From time to time, seemingly randomly, I get a System.AccessViolationException in System.Data.dll on the call to connection.Query. This exception is supposed to be thrown by unmanaged code, but I don't directly use unmanaged code, so it happens after the call to Query. I also wanted to make sure it was not a problem obviously related to multi-threading, but, if I understand the documentation correctly, using one IDbConnection per query should be thread-safe, and Dapper is supposed to be thread-safe too.

The error doesn't always happen on the same query or POCO type, and it's probably not a problem with the hardware (CPU < 50%, memory < 75% and my process doesn't eat more than 250 MB). I have not yet seen a problem with similar code on a SqlConnection to SQL Server.

Additionally, I'm working with VS Express 2012, so even if I configured "Break on exception", I can't use the Exception Assistant to troubleshoot. I obviously don't want to catch the exception up the stack, since it's a memory-related exception (as I wouldn't want to catch an OutOfMemoryException).

Am I doing something wrong? Is there a way to debug this, since it happens only from time to time? Could this be a bug with Dapper or the OleDb engine?

Accepted Answer

I suspect the issue would be having multiple threads hitting the Access DB. As far as I am aware, MS Access isn't designed for multi-threaded applications, since it has a limit on concurrency.

There's isn't much documentation on it, but it mentions here that MS Access isn't designed for high stress and high concurrency.

EDIT: Based on comments

  1. You could dump the access database directly into SQL Server, as a staging process and then run your .Net code against the Data in the staging table into it's final table on SQL.
  2. Use SSIS data flow to manipulate the data, possibly using a Script task for the .Net code
  3. I have seen a process where Reactive Extensions were being used on large 2gb CSV files, using the buffer to stream the data. So, if you can dump the ms access data into a CSV, you could run a similar process in memory.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why