MySQL 在 TRANSACTION、COMMIT 和 ROLLBACK 上自动递增列 [英] 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.

回复您的评论:如果我可以说得更清楚，事务范围的任何更改都可能会回滚.自增计数器不会回滚，因此它不遵守事务的原子性.它也不遵守隔离，因为即使您的交易尚未提交，另一个交易也会获得下一个值.

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.

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