1

I have a MySql database with nine tables within it. I need to dynamically upload to six of them from an asynchronous job within C#. However, to speed upload, four of them use MySqlBulkLoader, while the other two use a manual upload method. To support concurrency and prevent errors within the database, I need to upload them all at the same time. To do so, I decided to use transactions to make sure an "all or nothing" type upload.

Therein lies the problem: the MySqlCommand class allows for transaction support via the MySqlTransaction class within its Transaction property, and the MySqlBulkLoader allows for atomic upload (I use INNODB for the engine, see mysql - Can MySqlBulkLoader be used with a transaction?). However, the MySqlBulkLoader creates it's own transaction for atomic upload, while another separate transaction will be used for the MySqlCommand's I will be executing. This does not support serialization, and could still result in errors if one bulk loader completes it's job, yet the application closes before another can finish. This would be solvable if transactions could be nested; however, they cannot: php - Mysql transactions within transactions.

My questions is as follows: is there any way in the C# connector to allow the MySqlBulkLoader to be associated with a MySqlTransaction, and if not, if there is any way to automatically rollback changes made by previous MySqlBulkLoaders. I have looked in the API provided by the Connector.NET 6.9, and it seems this is potentially possible via the MySqlConnection.BeginTransaction() method, but a question from Werner Wolf suggests otherwise.

EDIT

I found this question by Saravanan. However, I do not think that question relates to this one by the factor of the MySqlBulkLoader intermingling with the MySqlCommand.

Community
  • 1
  • 1
astrogeek14
  • 234
  • 1
  • 14

1 Answers1

1

The original question asks:

is there any way in the C# connector to allow the MySqlBulkLoader to be associated with a MySqlTransaction
To this end, it seems there is not. Just slapping two transactions together did not work either, as it does not support serialization (as I thought it wouldn't). However, the alternative question asks:
if there is any way to automatically rollback changes made by previous MySqlBulkLoaders.
And again, it seems there is not an easy way to rollback the changes. Of course, I could check the tables states before and after, deleting the entries that were not present before; but, this solution is not optimal. However, a solution may present itself in the form of a question. The answer to the original question postulated that there was no way to associate a separate transaction with a MySqlBulkLoader; yet did not specify that we could make one on our own (a.k.a., extend the MySqlBulkLoader class to include a Transaction instance parameter). This would seem to be the solution to the problem, as well as the solution to the question Werner Wolf asked.

However, this was not the solution at all. Since the MySqlBulkLoader is a simple wrapper for the LOAD DATA INFILE syntax of SQL, it acts just as the SQL query would. And from the MySQL Reference Manual, we receive our answer:

The statements listed in this section (and any synonyms for them) implicitly end any transaction active in the current session, as if you had done a COMMIT before executing the statement.
A statement lower in the section of the webpage is LOAD DATA INFILE. This is the true answer to our question: it is impossible to associate a MySqlBulkLoader with a MySqlTransaction, at least to the effect that it would work the way we intend it to. Yet, as stated above, a solution to the problem may be to check the indices of the primary key's of each table, lock the table, save their position, and do a manual "rollback" after an upload has failed.
astrogeek14
  • 234
  • 1
  • 14