Mysql - creating a mechanism similar to Oracle seqences

Question

MySQL provides an automatic mechanism for increasing record identifiers. This is fine for many purposes, but I need to be able to use the sequences suggested by ORACLE. Obviously, it makes no sense to create a table for this purpose.

The solution SHOULD be simple:

1) Create a table to place all the necessary sequences,

2) Create a function that increments the value of a specific sequence and returns a new value,

3) Create a function that returns the current value of the sequence.

In theory, it looks simple ... BUT ...

When increasing the value of a sequence (just like Oracle's nextval ), you need to prevent other sessions from performing this operation (or even fetching the current value) until the update is complete.

Two theoretical options:

a - Use an UPDATE statement that returns a new value in one frame or

b - lock the table between UPDATE and SELECT.

Unfortunately, it seems that MySQL does not allow tables to be locked inside functions / procedures, and when trying to do all this in one expression (e.g. UPDATE ... RETURNING ...), you should use @ -type variables that survive after completion functions / procedures.

Anyone have an idea / working solution for this?

Thanks.

Answer 1

The following is a simple FOR UPDATE intent lockout example. Row level lock with INNODB engine. The example shows four lines for the following available sequences that will not suffer from the known INNODB gap anomaly (the case when spaces occur after unsuccessful use of AUTO_INCREMENT).

Scheme:

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

Code example:

 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 don't have a Line3 or bloaty code that delays other clients in standby lock mode. Meaning: use the following sequence, perform the update (incremental part) and COMMIT , ASAP .

The above procedure is stored:

 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 your needs, for example, having only one of two mechanisms for retrieving the serial number (either the OUT parameter or the result set). In other words, it is easy to abandon the concept of parameter OUT .

If you do not adhere to the ASAP LOCK release (which obviously is not needed after the upgrade), and proceed with the execution of time-consuming code before the release, then after a waiting period for other clients the following may happen: waiting for the sequence number:

ERROR 1205 (HY000): lock timeout has been exceeded; try restarting the deal

Hope this will never be a problem.

 show variables where variable_name='innodb_lock_wait_timeout'; 

MySQL man page for innodb_lock_wait_timeout .

On my system, it currently has a value of 50 (seconds). An expectation of more than a second or a second is probably unbearable in most situations.

In addition, the interest during transactions is that the section is derived from the following command:

 SHOW ENGINE INNODB STATUS;