Can you give more concrete example of the failed transaction? What was the error message returned from the failed transaction?
The in memory auto increment counter will reset itself after a server restart and will set itself to the highest value for the auto incremented field during the first insert into the table. But I suspect this is not what has happened.
Hence, this lead me to believe that the issue is related to the type of transaction error you are getting. Something like duplicate key will never get inserted as it will fail before that.
Well the code base used is for a private commercial product so I can't necessarily post code snippets. I tried to recreate the bug using sample tables on the same server but found it did increment correctly each time which leads me to believe that this is a code bug and not a problem with the server itself.
I can tell you that the software is web based, written in php and that I can recreate the bug by making the insert on the second table fail by adding a dummy column to the insert.
So I insert in to orders successfully but the insert into the people table fails because I tell it to set asdf to '' and there is no asdf field name.
The orders table entry rolls back (it doesn't still have an order sitting out there). So I suspect the roll back is getting called appropriately. It, however, does NOT increment the id field in the orders table.
That's where I have to scratch my head.
Other things I can note:
We have a secure database for storing credit card information. This is stored on another server. I start a transaction on both database servers. If either server has a query that fails it rolls back both transactions on both servers.
Some of our tables are MyISAM. These do not roll back. This is the source of our problem. We end up with potentially having some tracking data that belongs to the failed order rather than the one that was processed successfully.
A quick question from PHP are you sending all the SQL statements for the transaction as one string or are SQL statements and executed one at a time? If it is all at once it might be that the SQL server attempts to parse the entire set of statements and the fact that one of them has a syntax error causes none of the statements not to execute.