Can MySqlBulkLoader be used with a transaction?

Question

Can MySqlBulkLoader be used with a transaction? I don't see a way to explicitly attach a transaction to an instance of the loader. Is there another way?

Answer 1

As stated here by member of MySQL documentation team:

It's not atomic.  The records loaded prior to the error will be in the
table.

Work arround is to import data to dedicated table and then execute INSERT INTO ... SELECT ... which will be atomic operation. On huge data sets this is potential problem becasue of long transaction.

Answer 2

The MySQL manual indicates that the MySqlBulkLoader is a wrapper of 'LOAD DATA INFILE'. While looking at the 'LOAD DATA INFILE' documentation I noticed this paragraph:

If you specify IGNORE, input rows that duplicate an existing row on a unique key value are skipped. If you do not specify either option, the behavior depends on whether the LOCAL keyword is specified. Without LOCAL, an error occurs when a duplicate key value is found, and the rest of the text file is ignored. With LOCAL, the default behavior is the same as if IGNORE is specified; this is because the server has no way to stop transmission of the file in the middle of the operation.

I found no discussion on transactions but the above paragraph would indicate that transactions are not possible.

A workaround would be to import the data into a import table and then use a separate stored procedure to process the data using transactions into the desired table.

So in answ