Emulate MyISAM's composite primary key with an autoincrement behavior in InnoDB

Question

In MySQL, if you have a MyISAM table that looks something like:

CREATE TABLE `table1` (
`col1` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`col2` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`col2`, `col1`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

if you insert rows then the autoincrement base will be unique for every distinct col2 value. If my explanation isn't clear enough, this answer should explain better. InnoDB, however, doesn't follow this behavior. In fact, InnoDB won't even let you put col2 as first in the primary key definition.

My question is, is it possible to model this behavior in InnoDB somehow without resorting to methods like MAX(id)+1 or the likes? The closest I could find is this, but it's for PostgreSQL.

edit: misspelling in title

Answer 1

It's a neat feature of MyISAM that I have used before, but you can't do it with InnoDB. InnoDB determines the highest number on startup, then keeps the number in RAM and increments it when needed.

Since InnoDB handles simultaneous inserts/updates, it has to reserve the number at the start of a transaction. On a transaction rollback, the number is still "used" but not saved. Your MAX(id) solution could get you in trouble because of this. A transaction starts, the number is reserved, you pull the highest "saved" number + 1 in a separate transaction, which is the same as that reserved for the first transaction. The transaction finishes and the reserved number is now saved, conflicting with yours.

MAX(id) returns the highest saved number, not the highest used number. You could have a MyISAM table whose sole purpose to to generate the numbers you want. It's the same number of queries as you MAX(id) solution, it's just that one is a SELECT, the other an INSERT.