MySQL Bugs: #6714: INNoDB transaction rollback does not reset auto increment fields

Bug #6714	INNoDB transaction rollback does not reset auto increment fields
Submitted:	19 Nov 2004 3:34	Modified:	19 Nov 2004 8:39
Reporter:	John Mallery	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	4.1.7	OS:	MacOS (Mac OS X 10.3.6)
Assigned to:		CPU Architecture:	Any

Description:
Transactional tables could be track of auto incrementing fields and return them to the original 
values when the transaction aborts and the table is rolled back.

This would eliminate gaps in indices due to rollbacks.

How to repeat:
Easy make a table with an auto increment field and rollback a transaction.

Suggested fix:
make the auto increment counters transactional rather than locking based.

This wouldn't work. If transaction T1 generates an
AUTO_INCREMENT value, and then transaction T2
generates the next value and commits before T1
rolls back, the counter will already have advanced
past the value generated by T1.  AUTO_INCREMENT
values are never less than the current maximum
in the table, so the value generated by T1 will not
be reused.

Hi!

Paul is right, we cannot roll back the auto-inc counter in the general case.

However, you can code your own transactional counter table by hand. The downside in a fully transactional counter is less concurrency: the transaction must keep a lock on the counter table until a commit.

Best regards,

Heikki

Couldn't transactional auto_increment be an option for those applications that would benefit?  For example: transactional storage for messages in a message queue system where the sequence number is auto_increment.  A rolled back message would currently cause a sequence number gap that would confuse the receiver(s).

create table msg ( seqnum integer, primary key ( seqnum ) ) engine=InnoDB;
insert into msg (seqnum) select if(max(seqnum) is null,1,max(seqnum)+1) from msg;
insert into msg (seqnum) select if(max(seqnum) is null,1,max(seqnum)+1) from msg;
insert into msg (seqnum) select if(max(seqnum) is null,1,max(seqnum)+1) from msg;
ERROR 1062 (23000): Duplicate entry '3' for key 1

However, select if(max(seqnum) is null,1,max(seqnum)+1) from msg; // -> 3
And, insert into msg (seqnum) values (3); // -> works!

Without the if() and just using max(seqnum)+1 it seems to interpret NULL+1 as 0 for InnoDB and the above problem doesn't happen (but there is a 'data truncated' warning).  However insert into msg (seqnum) values (NULL+1); doesn't give a record with seqnum 0 as that would lead you to expect! :-)

Given that there are multiple transactional table types (InnoDB, BDB, InnoDB) it seems like there should be one clean way to do this without involving multiple statements or behavior that might turn out to be specific to the table type... for example, the time complexity of max() or the treatment of NULL+1 in an insert/select.