Noticed in mysql, when rolling back a ROLLBACK transaction, INSERT INTO AUTO_INCREMENT operations are not rolled back. InnoBD table type.

The problem is that you need the same id, since in the future there is a selection of a bunch of tables by id.

Is it possible to set up an AUTO_INCREMENT rollback or to add a new column to control the integrity of the id?

  • one
    In general, for good you should put in the first table, and in the second auto-increment should not be. When pasting into the second one, use the last_insert_id() function - Mike

3 answers 3

Values ​​of auto-increment fields, (as well as independent sequence generators where they are implemented) exist outside of transactions. This has its wisdom. Otherwise it would be difficult to avoid repetitions in concurrent transactions.

As Mike rightly pointed out, you only need one auto-increment. First inserted into the table with auto-increment, then refer to the value from last_insert_id () in all other places.

    The documentation ( http://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html ) says the following:

    “Lost” auto-increment values ​​and sequence gaps

    In all lock modes (0, 1, and 2), if this transaction is generated, the auto values ​​are “lost”. It can be used if it’s not backed out. Such lost values ​​are not reused. Thus, there may be a column of a table in the AUTO_INCREMENT column.

    So alas ...

      You leave AI in one table (let's call it “Main” in terms of business logic), and for the second table it’s better to use an insert using the foreign key. This will directly solve the task of linking tables for sampling. In any case, you cannot use two AI in different tables for data relatedness problems - this is an architecturally wrong way.