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();
                        }
                    }
                }

Marc Gravell · Accepted Answer · 2012-09-11 06:52:26Z

4

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.

answered Sep 11, 2012 at 6:52

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Carlos Over a year ago

I have the same problem (ORA-00054), but I've specified the timeout to zero (infinite?¿?). is there any way, to load asynchronously, not taking care at all of the BulkCopy operations being executed at the same time? usually my bulk copy operations take seconds ( less than 20k rows), therefore, I dont understand why this error if my timeout is set zero (infinite if I understood well). I know oracle locks the entire table, but the timeout property should make the bulkcopy to "wait" until the table is not locked anymore. Shouldn't it?

Collectives™ on Stack Overflow

OracleBulkCopy with Multiple threads

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related