I am reasonably new to mySQL. Normally I use MS SQL.
I understand from doing a bit of research that the mySQL connector works slightly differently to sqlClient when it comes to rolling back transactions.
I saw this post here: http://stackoverflow.com/questions/1630400/duplicate-key-error-does-not-cancel-rollback-mysql-transaction
although I didn't really understand what I read.
The scenario I am testing is that I have two Test tables (myTestTable1 and myTestTable2). Both just have an auto increment primary key and a varchar field. I have however added a unique constraint to table 2's varchar field only. I am trying to insert the same text values into both tables. Table one should succeed, but because of the constraint, table 2 should fail. I am using a transaction, so I'm assuming the Rollback of the transaction should cancel the insert of Table 1 also, but it doesn't - it remains there.
This works fine in MS SQL.
I'm a bit out of my depth here, so if someone could translate the gist of the URL above or give me a reason why the transaction is not being treated as such (or steps to make it so), I would be grateful.