0

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.

Red Boy
  • 5,429
  • 3
  • 28
  • 41
  • After reading I still can't understand why a sequence can't be used. Is this because every customer should have it's own sequence? You've correctly noticed that this solution is not safe in multi-user environment and what you could do is apply UPSERT logic combined with a loop instead of UPDATE, but that's a last resort. – Kamil Gosciminski Jun 13 '18 at 08:47
  • @KamilG. Yes, its because every customer have its own sequence. Yes, further exploring and looking for solution, to make it safe in multi-user environment. – Red Boy Jun 13 '18 at 08:53
  • @RedBoy . . . You would need to use a trigger. And that makes `inserts` rather expensive. – Gordon Linoff Jun 13 '18 at 11:17
  • @GordonLinoff yes, it would make expensive, also, I doubt if it can make sure unique records. – Red Boy Jun 13 '18 at 12:36
  • Any reason for down voting this question? Let me know, I'll improve the question. – Red Boy Jun 14 '18 at 06:57

1 Answers1

1

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.

Red Boy
  • 5,429
  • 3
  • 28
  • 41
  • similar question at dba exchange https://dba.stackexchange.com/questions/209633/oracle-sequences-equivalent-in-mysql – Red Boy Jun 18 '18 at 14:31