MySQL – Oracle Sequences Equivalent

MySQLsequence

I did ask same question on stackoverflow, but no response, hence I thought database experts will help me to solve this.

I have following table similar to Oracle user_sequences.

I have logic of sequence prefix/suffix something, but for simplicity, I'm skipping as matters less here.

create table my_seq(
min_value integer,
Max_value integer,
last_value integer,
increment_by tinyint,
customer_id integer);

Assume in current table there are two records.

insert into my_seq(min_value,max_value,last_value,increment_by,customer_id) 
  values(1,99999999,1,1,'foo#',1),(1,999999999,100,1,'foo#',2);

My foo table structure is like,

create table foo(id Auto_increment,foo_number varchar(20),customer_id integer);

Constrained:
I can't use MySQL AUTO_INCREMENT columns as foo contains different customers data, and every customer could opt foo_number auto generation or manual entry and there should be gap if customer opted for auto_generation. So customer=1 has opted for it, foo# should be 1,2,3,4 etc, no gaps are allowed.

So far so good, with auto increment logic that we have implemented if my app runs in single thread. We generate foo_number and populate in foo table, along with other data points.

I simply do a query to get the next auto#.

select last_number from my_seq where customer_id=?;

reads the # and the update the record.

update my_seq set last_number=last_number+increment_by where customer_id=?;

Problem:
When multiple concurrent session tries the run select last_number from my_seq..., it returns same foo_number multiple times. Also, I can't enforce single thread in application because of application side limitation and performance bottleneck, hence need to solve it in database side.

Please suggest, how I could avoid duplicate numbers? Please help, thanks in advance.

I did google, many stackoverflow links suggests get_last_id(), as you could see, I can't use it. Please suggest.

Best Answer

Possible solution.

Each thread which will obtain the values from "sequence generator" have some customer_id value it will use when ask for next value. Additionally, it generates some random and unique (guaranteed!) value when starting (if all threads works in one OS instance, it can be thread's PID, for example, if not, it can be PID+IP or something else - but with 100% no collision). This random allows to distinguish the threads used the same customer_id value.

You create the my_seq table as shown above:

create table my_seq(
min_value integer,
Max_value integer,
last_value integer,
increment_by tinyint,
customer_id integer);

Additionally you create the table looks like

CREATE TABLE my_gen (
customer_id integer,
session_id integer,
generated_value integer,
UNIQUE KEY idx (customer_id,generated_value)
);

When a thread needs next sequence value, it tries to obtain it by the next code:

SELECT last_value+increment_by 
    INTO @next_val 
    FROM my_seq 
    WHERE customer_id = @customer_id;

In that timepoint any parallel thread can obtain the same value... to avoid it, the threat tries to secure this value by:

INSERT IGNORE INTO my_gen (customer_id, session_id, generated_value)
    VALUES (@customer_id, @session_id, @next_val);

Only one thread used the same @customer_id can insert the record to this table with generated value - all another will not because of unique key.

And now the thread checks was the secure process for that value successfull:

SELECT *
FROM my_gen
WHERE customer_id = @customer_id
  AND session_id = @session_id 
  AND generated_value = @next_val;

If 1 record returns - the secure process is successfull, the thread can use obtained value, and it updates my_seq table:

UPDATE my_seq
SET last_value = @next_val
WHERE customer_id = @customer_id;

If 0 records returns - the secure process is unsuccessfull, obtained value was obtained, secured and used by another thread with the same customer_id... and this thread needs to repeat the whole process of generate sequence value.

You may to assemble this operations into one stored procedure or function. Moreover, you can organize the cycle directly into SP which will execute till the next value obtained... or any amount of attempts performed to avoid infinite loop.

Or simply lock the my_seq table exclusive for read and write before first read and till last update - no parallel thread can do something to interfere in that case.

Related Question