sequences - mysql - making a mechanism similar to Oracle's seqences

Question

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 nextval in Oracle), you need to prevent other sessions to perform this operation (or even fetch the current value) till the updated is completed.

Two theoretical options:

a - Use an UPDATE statement that would return the new value in a single shot, or

b - Lock the table between the UPDATE and SELECT.

Unfortunately, it would appear that MySQL does not allow to lock tables within functions / procedures, and while trying to make the whole thing in a single statement (like UPDATE... RETURNING...) you must use @-type variables which survive the completion of the function/procedure.

Does anyone have an idea/working solution for this?

Thanks.

Answer 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;