0

I'm trying to import a number of MS Excel (xlsx & xls) files via a multi-threaded process; however I occasionally (approx 5% of the time) receive an error

System.AccessViolationExeption: Attempted to read or write protected memory. This is often an indication that other memory is corrupt

...when multiple oledb or odbc connections are opened simultaneously.

The application appears to work successfully when run on a single thread.

So far i've tried:

  • Both OLEDB and ODBC
  • Alternate MS Access drivers (Microsoft.ACE.12 and Microsoft..ACE.16)
  • Alternate architectures (compiling for x64 & x86)

Code sample below is used to connect to the files;

//// Connecting to file


// Get factory

DbProviderFactory dbfct = DbProviderFactories.GetFactory("System.Data.OleDb");

// Connect to db

DbConnection dbconn = dbfct.CreateConnection();

dbconn.ConnectionString = dataObject.GetSourceConnectionString(filename);

// Attempt to open connection

dbconn.Open();

DbCommand dbcmd = dbconn.CreateCommand();
dbcmd.CommandText = dataObject.GetSourceCommandString(filename);

// Read the data
using (IDataReader sourcerdr = dbcmd.ExecuteReader())
{
    // IDataReader is passed to SQL Bulk copy to load into database
    ProcessDataFromReader(dataObject, sourcerdr, byPassPrepare);
}

// Close & dispose
dbconn.Close();
dbcmd.Dispose();


//// Code used to create multiple tasks to process across multiple threads

// Create x tasks to process

Task[] consumers = new Task[ProcessConcurrency];
for (int i = 1; i <= ProcessConcurrency; i++)
{
    // Log the task ids
    string taskId = i.ToString();
    consumers[i - 1] = Task.Run(() => ConsumeQueue() // Pass off to an object processor
    );
}

// Wait for the processors to complete

await Task.WhenAll(consumers);
Jake Michael Gosling
  • 61
  • 1
  • 2
  • Set `OLE DB services=-1` or keep the connections open: https://stackoverflow.com/a/47145747/495455 **Or** see the DbConnection implements IDisposable so you will want to put that in a `using` clause to clean up the unmanaged resource.. that actually could be the fix, because it hasn't been disposed it causes the problem of conflicting memory access. Yes you close the connection but you don't Dispose it. – Jeremy Thompson Jul 13 '20 at 05:07
  • Thanks Jeremy, I've tried disposing the connection after use; still experiencing the error. I had previously attempted the OLE DB services=-1 flag which did not resolve the error. Connection pooling isnt implemented as each file is only connected to once; i.e. a list of files is enumerated, creating an OLEDB connection to each spreadsheet in order to process. – Jake Michael Gosling Jul 13 '20 at 05:25
  • Further to the comment above, another part of the application which connects to various MS SQL servers in order to copy data works fine across multiple threads - which leads me to believe this issue is related oledb connections in a multi-threaded environment. – Jake Michael Gosling Jul 13 '20 at 05:27
  • 2
    Unless you are reading the excel files via the Open XML format I think you'll find that every other technology is specifically single threaded - in other words, you can't use multiple threads.. – Enigmativity Jul 13 '20 at 05:32
  • @Enigmativity he's using Drivers though, it's not the Object Model (so KB257757 doesn't apply). It's worth trying actually to rule it out.. – Jeremy Thompson Jul 13 '20 at 05:34
  • @JakeMichaelGosling I'm not 100% on multi-threaded, seems opening and closing from multiple source quickly is reproducible: https://social.msdn.microsoft.com/Forums/en-US/24f33a3b-a948-4829-a23e-dfe807badc9b/excel-2016-accessviolation-in-aceoledbdll-after-creatingopeningclosing-a-oledbconnection-multiple?forum=exceldev – Jeremy Thompson Jul 13 '20 at 05:36
  • Does keeping all the connections open till after the `ConsumeQueue()` finishes resolve the problem? – Jeremy Thompson Jul 13 '20 at 05:43
  • @JeremyThompson - Error still occurs, even when the connections arent closed/ disposed. – Jake Michael Gosling Jul 13 '20 at 05:52
  • Because its a second chance exception the debugger can't catch it. In this case you can do a memory Dump or you can place some logging. I need to see the lines of code where the error occurs, if we remove the Open/Close what else could be causing it??? Can you first confirm the Open XML method and if that fails, do a log output to identify the line of code causing the error. – Jeremy Thompson Jul 13 '20 at 06:00
  • https://stackoverflow.com/questions/37432816/microsoft-ace-oledb-12-0-bug-in-multithread-scenario ? I agree with enigmativity; I think multithreading here is beating a dead horse. Have you considered EPPlus? Also, when you said *Connection pooling isn't implemented* - do you have a source for this implication that connecting different files doesn't reuse connections? – Caius Jard Jul 13 '20 at 06:00
  • Doesn't look good: https://stackoverflow.com/q/37432816/495455. FWIW I have a multi-threaded app that uses Open XML (or closed XML the better version) to read XML files and generate XSLX files and emails them out. So try the XML method. – Jeremy Thompson Jul 13 '20 at 06:07

0 Answers0