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
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);
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=?;
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.