The idea is to provide one global incrementing sequence number which is safe for concurrent access by multiple clients.
Schema ¶
A table with one record holds the "current value" of the sequence (value last given out).
CREATE TABLE `OpSequence` (
seqNumber BIGINT UNSIGNED
) ENGINE=InnoDB;
Simply use more columns if more sequences are required (note that all sequences would be locked together, this way; this is unlikely to be an issue, otherwise use multiple tables).
Initialise ¶
INSERT INTO OpSequence (seqNumber) VALUES (0);
Fetch new value ¶
This query must return a different value every time it is used (by any thread/client).
Can probably be a stored procedure.
DO GET_LOCK('x.OpSeq',10); -- check result
UPDATE OpSequence SET seqNumber = seqNumber+1;
SELECT seqNumber FROM OpSequence;
DO RELEASE_LOCK('x.OpSeq');
There is a variation of this that can be used if an error should not cause a number to be consumed