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?
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
SSIS data flow to manipulate the data
, possibly using a Script task for the .Net codeReactive 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.