Re: Simulating sequences

[Rick James]

> I am going to create a table of ids/sequence names. Then create a stored procedure to a) insert a row in a sequence table, b) get the auto-incremented primary key for my id and c) delete the row. Does anyone see any issues with this approach? Anything I need to watch out for with regards to multi-threading?

* Do the Sequence values need to be always-increasing? InnoDB transactions fail in this regard.

* Will that Stored Procedure be called inside of a transaction that _may_ be ROLLBACK'd? The answer is certainly yes. This can lead to gaps in the numbers; make sure that is OK. Having it outside the primary transaction is likely to be better.

* The cost of INSERT + DELETE is possibly excessive -- consider REPLACE:

CREATE TABLE Seq (
id INT NOT NULL UNSIGNED AUTO_INCREMENT,
blah CHAR(1) NOT NULL,
PRIMARY KEY (blah),
INDEX(id)
) ENGINE = ?;
REPLACE INTO Seq SET blah='x', id=NULL;

Notes:
* REPLACE Needs a unique key to know which row to 'replace'
* REPLACE does two steps atomically: DELETE + INSERT.
* There is never more than one row in the table, yet id will continue to increase. (OK, you could have multiple `blah` values.)
* id=NULL & INDEX(id) is sufficient get the next AUTO_INCREMENT.
* ENGINE = ? -- It _may_ be better to make it MyISAM (or MEMORY?), that way it does not take part in the transaction or ROLLBACK.
* Any method you use (including this one) may 'burn' ids in corner cases.