mysql - making a mechanism similar to Oracle's seqences

Solution 1:

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).

Schema:

-- 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)
) ENGINE=InnoDB;

-- truncate table sequences;
insert sequences (sectionType,nextSequence) values
('Chassis',1),('Engine Block',1),('Brakes',1),('Carburetor',1);

Sample code:

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 COMMIT, ASAP.

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 ;

Test:

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). In other words, it is easy to ditch the OUT parameter concept.

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;