OracleBulkCopy with Multiple threads

Question

I have five threads. They are doing OracleBulkCopy(1 million records each) into the same table (EXCEL_DATA) at same time. But at some point in time I am getting below error:

ORA-00604: error occurred at recursive SQL level 1 ORA-00054: resource busy and acquire with NOWAIT specified

I am using below code for OracleBulkCopy:

using (OracleConnection con = new OracleConnection(ConnectionString))
                {
                    con.Open();
                    using (var bulkcopy = new OracleBulkCopy(con, options))
                    {
                        OracleTransaction tran = 
                            con.BeginTransaction(IsolationLevel.ReadCommitted);
                        bulkcopy.DestinationTableName = DestinationTable;
                        foreach (var mapping in columnMappings)
                            bulkcopy.ColumnMappings.Add(mapping);
                        bulkcopy.BulkCopyTimeout = TimeOut.Value;
                        try
                        {
                            bulkcopy.WriteToServer(dataTable);
                            tran.Commit();
                        }
                        catch (Exception ex)
                        {
                            tran.Rollback();
                        }
                    }
                }

Answer 1

It sounds like the table or a section is locked (quite reasonable during a bulk-copy, especially since you have an explicit transaction), and that is blocking other inserts from competing bulk-copies. That doesn't sound very surprising. The best thing I can say is... "don't do that". In particular, this is an IO-bound operation, with your main blockage very likely to be the network, with the secondary limit being the back-end server - which is also required to observe the ACID rules you have specified. For these reasons, doing these operations in parallel is not likely to give any significant performance benefits, but is very likely to cause timeouts due to blocking.

So: instead of doing these in parallel... do them in series.