Search code examples
mysqlsqldatabasesequences

Oracle sequences equivalent in MySQL

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.

Solution

  • I was able to solve this problem by just combining suggestions of @Akina and @RickJames , thank you both for thier support.

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

    Here ENGINE=InnoDB is very important. In order to make sure there is table level locking while reading, I have modified my app code to:

    Auto-Commit=FALSE

    Then,

    //very import to begin the transaction
begin;
select last_number from my_seq where customer_id=? FOR UPDATE;

Read the result in App.

update my_seq set last_number=last_number+1 where customer_id=?;
commit;

    This was generating the unique sequence number even in case of multiple concurrent sessions.

    I have faced another problem, that this solution has slowed down other are where I do generate sequence#. I have solved it enabling a row level lock instead of table level lock by indexing customer_id.

    ALTER TABLE TABLE_NAME ADD INDEX (customer_id);

    Hope this will be help full to others.