MySQL Auto Increment Columns on TRANSACTION, COMMIT, and ROLLBACK

Disclosure: I am relatively new to SQL so some of this may be wrong. But this is how I understand it. Auto increment must function outside of transactions otherwise you can compromise data. Let us say you have 2 users both trying to connect to your dB. Both are creating accounts. When Account 1 is being created it increments up to 1. Then Account 2 is created and it increments to 2. So far it would look like the following:

• (1, Account1)
• (2, Account2)
• Increment: 2.

Now let us say that Account 2 commits, but Account 1 fails. Now your table looks as follows:

• (2, Account2)
• Increment: 1.

Because you decremented your Auto Increment, you will fail at having a unique value when someone tries to register a new account, because the pair will be:

• (2,Account2)
• Attempting to Insert (2, NewAccount)

Which will fail because 2, the primary key, will not be unique.

If you need to fix your table (for development purposes) you can always run the following code:

ALTER TABLE `DB_NAME`.`TABLE_NAME` 
AUTO_INCREMENT = 1 (or some other number);

No, auto-increment mechanisms must work outside the scope of transactions, because another user may be inserting to the same table before you finish your transaction. The other user's transaction must be able to allocate the next value, before knowing whether your transaction is using the value you just allocated.

Re your comment: If I may say it more clearly, any change in the scope of a transaction may be rolled back. The auto-increment counter is not rolled back, so it doesn't obey atomicity of transactions. Nor does it obey isolation because another transaction gets the next value even though your transaction hasn't committed yet.

The way auto-increment works means that sometimes, if you insert some rows and then roll back your transaction, the values you allocated with auto-increment are lost forever!

But this is okay. Primary key values must be unique, but they don't need to be consecutive. In other words, they are not row numbers, and you shouldn't use them like that. So you should never need to decrement values created by auto-increment.