2

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.

  • Problem: When multiple concurrent session tries the run select last_number from my_seq..., it returns same foo_number multiple times. You mean multiple concurrent sessions used the same value of customer_id? And how they can be distinguished? Assume in current table there are two records. 5 fields and 6 values for them... really? – Akina Jun 14 '18 at 8:04
  • lock the table or the record during the read-update procedure. – Jasen Jun 14 '18 at 8:37
  • What properties of a "sequence" do you require? Uniqueness? Increasing? No gaps? Deleted values may [may not] be reused? – Rick James Jun 16 '18 at 18:54
  • Is MariaDB an option, which for most intents and purposes is a drop-in replacement for MySQL, and as of version 10.3 has sequences built-in? Just thought I should mention it. – dbdemon Jun 18 '18 at 8:20
  • @dbdemon OK, thanks for info, I was not aware about MariaDB that it has builtin sequences, but I can't switch my app to MariaDB or introduce any new database dependency just for this use case,SO, switching database was out of question in my case, but surely could an option for someone just looking for specific solution. – Red Boy Jun 18 '18 at 8:29
1

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.

  • thank you for response. Your second solution seems promising to me,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. Let me try moving in this direction. Your help much appreciated. – Red Boy Jun 14 '18 at 8:59
  • @RedBoy You must to solve a problem of thread hang/crash during this process... – Akina Jun 14 '18 at 9:01
  • 1
    @RedBoy - Do not use MyISAM; do not use LOCK TABLES. – Rick James Jun 16 '18 at 18:52
  • @rickjames thanks for suggestion. Yes, I have solved the problem yesterday and will answer my question soon. You're correct, I needed innodb to solve it. – Red Boy Jun 17 '18 at 11:32
0

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 problem has slowed down other similar area 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 helpfull to others.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.