MySQL provides an automatic mechanism to increment record IDs. This is OK for many purposes, but I need to be able to use sequences as offered by ORACLE. Obviously, there is no point in creating a table for that purpose.
The solution SHOULD be simple:
1) Create a table to hosts all the needed sequences,
2) Create a function that increases the value of a specific sequence and returns the new value,
3) Create a function that returns the current value of a sequence.
In theory, it looks simple... BUT...
When increasing the value of a sequence (much the same as
The following is a simple example with a FOR UPDATE intention lock. A row-level lock with the INNODB engine. The sample shows four rows for next available sequences that will not suffer from the well-known INNODB Gap Anomaly (the case where gaps occur after failed usage of an AUTO_INCREMENT).
-- drop table if exists sequences; create table sequences ( id int auto_increment primary key, sectionType varchar(200) not null, nextSequence int not null, unique key(sectionType) ); -- truncate table sequences; insert sequences (sectionType,nextSequence) values ('Chassis',1),('Engine Block',1),('Brakes',1),('Carburetor',1);
START TRANSACTION; -- Line1 SELECT nextSequence into @mine_to_use from sequences where sectionType='Carburetor' FOR UPDATE; -- Line2 select @mine_to_use; -- Line3 UPDATE sequences set nextSequence=nextSequence+1 where sectionType='Carburetor'; -- Line4 COMMIT; -- Line5
Ideally you do not have a
Line3 or bloaty code at all which would delay other clients on a Lock Wait. Meaning, get your next sequence to use, perform the update (the incrementing part), and
The above in a stored procedure:
DROP PROCEDURE if exists getNextSequence; DELIMITER $$ CREATE PROCEDURE getNextSequence(p_sectionType varchar(200),OUT p_YoursToUse int) BEGIN -- for flexibility, return the sequence number as both an OUT parameter and a single row resultset START TRANSACTION; SELECT nextSequence into @mine_to_use from sequences where sectionType=p_sectionType FOR UPDATE; UPDATE sequences set nextSequence=nextSequence+1 where sectionType=p_sectionType; COMMIT; -- get it and release INTENTION LOCK ASAP set p_YoursToUse=@mine_to_use; -- set the OUT parameter select @mine_to_use as yourSeqNum; -- also return as a 1 column, 1 row resultset END$$ DELIMITER ;
set @myNum:= -1; call getNextSequence('Carburetor',@myNum); +------------+ | yourSeqNum | +------------+ | 4 | +------------+ select @myNum; -- 4
Modify the stored procedure accordingly for you needs, such as having only 1 of the 2 mechanisms for retrieving the sequence number (either the OUT parameter or the result set).
If you do not adhere to ASAP release of the LOCK (which obviously is not needed after the update), and proceed to perform time consuming code, prior to the release, then the following can occur after a timeout period for other clients awaiting a sequence number:
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Hopefully this is never an issue.
show variables where variable_name='innodb_lock_wait_timeout';
MySQL Manual Page for innodb_lock_wait_timeout.
On my system at the moment it has a value of 50 (seconds). A wait of more than a second or two is probably unbearable in most situations.
Also of interest during TRANSACTIONS is that section of the output from the following command:
SHOW ENGINE INNODB STATUS;