OracleBulkCopy with multiple threads

Question

I have five threads. At the same time, they make OracleBulkCopy (1 million records each) in the same table (EXCEL_DATA). But at some point in time I get below the error:

ORA-00604: error at recursive SQL level 1 ORA-00054: resource is busy and acquired with the specified NOWAIT

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 looks like the table or section is locked (it’s quite reasonable during a bulk copy, especially if you have an explicit transaction), and this blocks the other inserts from competing bulk copies. That doesn't sound very surprising. The best I can say is ... don't do this. In particular, this is an IO-bound operation with your main lock, which is most likely to be a network, with the secondary limit being the internal server, which is also necessary to comply with the ACID rules that you specified. For these reasons, parallel execution of these operations is unlikely to give significant performance benefits, but it can very well cause timeouts due to blocking.

So: instead of doing it in parallel ... do them sequentially.