有关TRANSACTION，COMMIT和ROLLBACK的MySQL自动增量列 [英] MySQL Auto Increment Columns on TRANSACTION, COMMIT, and ROLLBACK

问题描述

使用MySQL START TRANSACTION时，MySQL决定回滚- 如果表具有AUTO_INCREMENT列-该列在回滚过程中是否会减...?

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?

还是应该?

我在正确地回滚事务数据时遇到了一些问题-但是表似乎在回滚中是自动递增而不是递减的.

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

因为MySQL会自动递增第一个表上的ID(无论事务是成功还是失败)，所以这是您自己递减表的标准做法吗?

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?

推荐答案

否，自动递增机制必须在事务范围之外起作用，因为在您之前，另一个用户可能正在插入同一张表完成交易.在知道您的交易是否使用您刚分配的值之前，其他用户的交易必须能够分配下一个值.

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.

对您的评论:如果我可以说得更清楚一些，那么交易范围的任何变化都可能被回滚.自动增量计数器不会回滚，因此不会遵循交易的 atomicity .它也没有遵守 isolation (隔离)，因为即使您的交易尚未提交，另一笔交易也会获得下一个价值.

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.

这篇关于有关TRANSACTION，COMMIT和ROLLBACK的MySQL自动增量列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！