Issue: Multithread bulkinsert

Question

I tried to do a bulkinsert with many threads. After using linq to read 1000 rows of one dataTable (called 'dt'), it made a new dataTable and did the bulkinsert into the database.

There is the code to initialize the threads:

        ManualResetEvent[] doneEvents = new ManualResetEvent[10];
        BancoDAO[] fibArray = new BancoDAO[10];

         for (int i = 0; i < 10; i++)
        {
            doneEvents[i] = new ManualResetEvent(false);
            BancoDAO bd = new BancoDAO() 
            {
                _doneEvent = doneEvents[i],
                dataTable = (dt.AsEnumerable()
                    .Skip(i * 1000)
                    .Take(1000)
                    ).CopyToDataTable<DataRow>() 
            };
            fibArray[i] = bd;
            ThreadPool.QueueUserWorkItem(bd.ThreadPoolCallback, i);
        }

        WaitHandle.WaitAll(doneEvents);

As you can see, the insert occurs on BancoDAO class. Here is the code:

    public DataTable dataTable = new DataTable();
    public ManualResetEvent _doneEvent;

    public void ThreadPoolCallback(Object threadContext)
    {
        int threadIndex = (int)threadContext;
        GravaTabelaThread(dataTable);
        _doneEvent.Set();
    }

    public static void GravaTabelaThread(DataTable dt)
    {
            OracleConnection cteste = new OracleConnection(ConfigurationManager.ConnectionStrings["TesteUpload"].ToString());
            cteste.Open();
            OracleBulkCopy bcp = new OracleBulkCopy(cteste);
            bcp.DestinationTableName = "MAG_T_SORTIMENTO2";

            foreach (KeyValuePair<string, string> k in ColumnMappings())
            {
                bcp.ColumnMappings.Add(k.Key, k.Value);
            }
            try
            {
                bcp.WriteToServer(dt);
                bcp.Dispose();
            }
            catch (Exception ex)
            {
            }
            cteste.Close();
            //Now I open and close the connection everytime after doing the bulkinsert in the database.
            //I'm not using anymore the same connection.
    }

The problem is: some threads inserts the values on database...some times this exception blows (I'll translate the oracle message from portuguese into english, so, please keep in mind this is a "free translation"):

{Oracle.DataAccess.Client.OracleException Error in row '1' column '1'
ORA-39776:  API fatal error, wrong directoty way when loading the table USR_TRANSF.MAG_T_SORTIMENTO2
ORA-39781: Loads of direct path stream are not allowed after another context loading the same table was to be terminated  in Oracle.DataAccess.Client.OracleBulkCopy.PerformBulkCopy()
   in Oracle.DataAccess.Client.OracleBulkCopy.WriteDataSourceToServer()
   in Oracle.DataAccess.Client.OracleBulkCopy.WriteToServer(DataTable table, DataRowState rowState)
   in Oracle.DataAccess.Client.OracleBulkCopy.WriteToServer(DataTable table)
   in UploadArquivo.BancoDAO.GravaTabelaThread(DataTable dt) at c:\Users\Rafael.pinho\Desktop\UploadArquivo\UploadArquivo\UploadArquivo\BancoDAO.cs:linha 49}

Answer 1

It appears from the ODP.Net Developer's Guide that OracleBulkCopy class does a direct-path load. If that's the case, then it is not really compatible with a multi-threaded application. Only one session can be doing a direct-path load on a particular object at any point in time. I suppose that you could serialize your threads so that only one thread has an open transaction at any point in time but it seems highly likely that this would defeat the purpose of multithreading on the client. On the other hand, since a direct path insert is the most efficient way to load data, you should basically be able to load data as quickly as you can pump it over the network (assuming, of course, your database can process data that quickly but I would assume that it could).