MySQL Auto Increment Columns on TRANSACTION, COMMIT, and ROLLBACK

auto-incrementdecrementMySQLtransactions

When using MySQL START TRANSACTION and the decision is made by MySQL to roll back –
In the case that a table had an AUTO_INCREMENT column – does the column get… decremented during the roll back?

Or should it?

I am having some issues where the transaction data is being properly rolled back – but it looks like the table was auto incremented and not decremented in the rollback.

# BOTH TABLES START OUT EMPTY // TABLE1 ID is **auto_increment** 

START TRANSACTION;

INSERT INTO `TABLE1` (`ID` ,`NAME`) VALUES (NULL , 'Ted');  # MySQL TABLE1 **ID** is Auto incremented to 1 

INSERT INTO `TABLE2` (`ID` ,`WRONGVALUE`) VALUES (NULL , 'some value');  # error. This TRANSACTION will not succeed 

COMMIT;  # Because of the error - this TRANSACTION is now rolled back and Ted is NOT added

Because MySQL will auto_increment the ID on the first table – regardless of if the transaction succeeds or fails – is the standard practice for this to decrement the table yourself?

Best Answer

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.

Related Topic