So, this is a small blog that I felt necessity to do cause I am seeing many tweets on twitter (obviously) talking about that “a rollback query on innodb does not reset the auto_increment counter”. I quickly went test it and really didn’t understand what happened with this user environment, due to it I decided to share it here in a bloh – so, that’s bla bla bla enough, let’s acting!
Everyone knows that InnoDB is the actual default Storage Engine and its strongest characteristic is that is a transactional Storage Engine with fully or 100% ACID support. Naturally, due to it the title of this blog. By default, the InnoDB’s behavior is to auto commit every little transaction, instead of you configure out InnoDB to run with autocommit variable equal 0, or, turned off, what will be possible to COMMIT through the application a good amount of rows as happening e.g. in a batch.
So, if you use START TRANSACTION, autocommit is set to 0 until the next COMMIT or ROLLBACK and we will use this approach to test if “a rollback query on innodb does not reset the auto_increment counter” is true! Follow SQL queries and commands below, I will performing some comments on every little step throughout tests.
mysql> use test # I used test database Database changed mysql> create table t1(id int auto_increment, primary key(id)) engine=innodb; Query OK, 0 rows affected (0.23 sec) mysql> start transaction; # set autocommit =0 Query OK, 0 rows affected (0.01 sec) mysql> insert into t1 values (1),(2); # inserted two lines in a insert multiple Query OK, 2 rows affected (0.03 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from t1; # records there! +----+ | id | +----+ | 1 | | 2 | +----+ 2 rows in set (0.03 sec) mysql> rollback; # roll backing Query OK, 0 rows affected (0.01 sec) mysql> select * from t1; Empty set (0.00 sec) mysql> insert into t1 values (1),(2); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from t1; # ops!! +----+ | id | +----+ | 1 | | 2 | +----+ 2 rows in set (0.00 sec)
As you can see, explicitly we have our auto_increment identifier back and everything happened as we waited. So, let me add new column “name”on the same table and omit the auto_increment column on INSERT query and test it again.
mysql> alter table t1 add name char(60) not null;
Query OK, 2 rows affected (1.11 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> desc t1;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(60) | NO | | NULL | |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.19 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 (name) values ('Wagner'),('Bianchi'); # inserting lines where id=NULL
Query OK, 2 rows affected (0.00 sec) # then id = last_insert_id + 1
Records: 2 Duplicates: 0 Warnings: 0
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 (name) values ('Wagner'),('Bianchi');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT id, name FROM t1;
+----+---------+
| id | name |
+----+---------+
| 1 | Wagner |
| 2 | Bianchi |
+----+---------+
2 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 (name) values ('Wagner'),('Bianchi');
Query OK, 2 rows affected (0.20 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT id, name FROM t1; # Ouch!!
+----+---------+
| id | name |
+----+---------+
| 3 | Wagner |
| 4 | Bianchi |
+----+---------+
2 rows in set (0.00 sec)
Hey!!!! What happened with our tests?!?!?! Sure, it was expected cause this is the same behavior the old MyISAM and those IDs only will be available again if you explicitly mention on your query – it’s normal!
