I'm currently developing a C# application with MySql as backend database.
Unfortunately I found a wired behaviour I can't explain.
I wanted to use transactions, to "combine" multiple queries to make sure only all commands or none are executed.
However, because of a coding error by me, an unhandled exception occured during the transaction.
My understanding of a transaction is, that in such a case, the transaction is rolled back.
Nevertheless I found out that Mysql seems to enable AutoCommit by default and commits after the disconnect because of the crash resulting from the unhandled exception.
So I tried to disable it by executing SET autocommit = 0
But this doesn't work.
EDIT: I created the transaction using following code:
_transaction = _mysqlConnection.BeginTransaction();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = _mysqlConnection;
cmd.Transaction = _transaction
Other people mentioned, that the usage of BEGIN should solve this issue, but this isn't the case. In the Mysql log the following querys are executed:
239 Connect user@localhost on gssm
239 Query SHOW VARIABLES
239 Query SHOW COLLATION
239 Query SET character_set_results=NULL
239 Init DB db
239 Query SET autocommit = 0
239 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
239 Query BEGIN
239 Query INSERT INTO TestTable (KeyField, Value) VALUES ('foo', 'bar')
Of course I should fix my code, so that no unhandled exception occurs, but is there a way to prevent Mysql from doing this?
Thanks!
Chris
It is a good idea, to use a MySql Engine that actually supports Transactions... MyISAM does not, which was the problem. So if want to use transactions make sure, that you use the right engine (InnoDB for example).
Sorry for your waste and time and thank you for your suggestions.
Chris