While there are some major annoyances with transactions in MySQL like causing an implicit commit when doing DDL statements, there are also slightly more subtle annoyances. A user just stumbled over a little annoyance in MySQL transaction handling and autoincrement. If you generate a new ID inside a transaction and rollback, the autoincrement counter is not decremented.

Of course I know that autoincrement is just about generating new unique id's and I should not worry about gaps and that for most people the value range for INT is more than they will ever need, not to mention BIGINT (though BIGINT really sucks in PHP as its larger than the natively supported value range for integers in PHP). However this issue goes to show again that a ROLLBACK is not the magic undo-wand that people often expect.

mysql> create table autoinc (sequence int auto_increment primary key) engine=innodb;
Query OK, 0 rows affected (0.59 sec)

mysql> show create table autoinc\G
*************************** 1. row ***************************
       Table: autoinc
Create Table: CREATE TABLE `autoinc` (
  `sequence` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`sequence`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into autoinc (sequence) VALUES (NULL);
Query OK, 1 row affected (0.03 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                1 |
+------------------+
1 row in set (0.04 sec)

mysql> rollback;
Query OK, 0 rows affected (0.06 sec)

mysql> select * from autoinc;
Empty set (0.00 sec)

mysql> insert into autoinc (sequence) VALUES (NULL);
Query OK, 1 row affected (0.04 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

You're right, it's not the magic wand people expect, but for good reason. Consider two concurrent transactions, both inserting a row into your autoinc table.

Transaction 1:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)

Transaction 2:

mysql> insert into autoinc (sequence) values (null);
Query OK, 1 row affected (0.00 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 2 |
+------------------+
1 row in set (0.00 sec)

Now, if you commit Transaction 2 then roll back Transaction 1, what happens to `sequence`? ;)

This is a pretty common thing. Same way in Microsoft SQL Server. You also have to consider replication issues. Unfortunately nothing is ever as simple as we'd like... sigh.

@Dave: sure but in my case I did not. All I did was do an insert and a reasonable expectation would be that if I rollback and execute the same insert again, with no concurrency that it would give me the same as if I would not have rolled back. And you could take the position that a sequence or autoincrement counter is nothing more than a normal data field that is incremented. This position is probably more so valid for autoincrement/identity columns.

Anyways I probably should not have labeled it annoyance. When looking at it from the perspective of preventing any issues with concurrent transactions it does make sense for a unique ID generating facility to not bother with any special rollback handling.

PostgreSQL also does not seem to rollback changes done to a sequences via nextval() and setval().

Same in every database which supports sequences, generators, identities or AUTO_INCREMENT fields. Simply all numbers retreived from seqeunce are allways retreived outside of transaction, so you cannot rollback them.

This is by-design in every database engine that I know of for a good reason. I'm confused why you would expect it to be otherwise. ROLLBACK is not a magic UNDO, but rather it does not merge isolated transaction level changes with the database. By design sequences must go outside of the isolated transaction to grab a unique ID.

Think of sequences as static variables and transactions as objects. Multiple transactions/objects need to access the same instance of a static variable, but none of the transactions/objects should try to "undo" any changes to that variable because they don't know what state it might currently be in.

And you could take the position that a sequence
or autoincrement counter is nothing more than a
normal data field that is incremented.

in that case auto increment actions would become serialized
as transaction locks would have to be set on the sequence
object (or whatever you name it) ... no two transactions
running in parallel could obtain a new sequence value at
the same time

Excellent post. I didn't know this before, and it's something to remember. I agree that it shouldn't make a lot of difference in the long run.

If you use this table:

CREATE TABLE ids (
    id SERIAL PRIMARY KEY
);
-- the sequence "ids_id_seq" will be created implicitly

and you want to rollback an insert and reset the serial value, you can do this:

-- this'll bump up the sequence
BEGIN;
INSERT INTO ids() VALUES();
ROLLBACK;

-- reset the sequence directly
SELECT setval('ids_id_seq', (SELECT MAX(id) FROM ids));

I wouldn't recommend doing this on a regular basis, though. The only time I've ever had to reset a sequence is for database restoration or after a bulk `COPY` command.