MySQL autocommit and starttransaction issue.

[MJFShark]

Hi All!

If MySQL is in autocommit mode, then if you call:

StartTransaction
Update/insert/delete
Commit

My understanding is that calling Commit will automatically put the driver back into autocommit mode, but it doesn't (or I should say that the Autocommit properties are set correctly, but the underlying call to mysql_autocommit(FHandle, 1) is never made.) The Commit method has:

Code: Select all

    AutoCommit := True;

I think that the line that says AutoCommit := True is supposed to be calling SetAutocommit(True). I've made that change (in commit and rollback) in my source and it seems to fix the issue, but there may be more to it.

-Mark

[EgonHugeist]

Now we've loads of tests fails now. However i think you're right.

I'll implement the explicit "START TRANSACTION" syntax.

Mark i never was able to find a problem with the current logic. Can you give me an example where it did fail? -> for the tests

[MJFShark]

Hi Michael,

I was able to find and diagnose the problem using two separate connections and transactionally updating records, then reading them from the second session to test if autocommit had been turned back on. In my steps above, the autocommit flag was set, but SetAutoCommit was never called at the driver level. The easiest way to diagnosed it is by putting a breakpoint on the mysql_autocommit(FHandle, 1) line. You should see that it's never called after the commit or rollback. I see you've changed the code, so I'll retest it.

So my steps to show the issue was just:

Make sure autocommit is on.
Start Transaction
Make change 1 (update a record)
Commit
Make change 2
disconnect

And see that change 2 was rolled back instead of being "autocommited" I guess the test would check if change 2 occurred.

Thanks!

-Mark

[MJFShark]

Hi Michael,

I've done some testing on the new code and it looks great! The nested transaction support is quite nice as well. Thanks for your great work!

-Mark