MySQL Sequence Automatically Initialized

Overview

MySQL does not have an object like Oracle’s sequence. The first question for developers who have had a lot of experience with Oracle is mostly about the sequence.

For simple sequence issuance, you can create a function and issue a sequence as shown below.(Simple and efficient!)
https://www.percona.com/blog/2008/04/02/stored-function-to-generate-sequences/

Sometimes, however, I received interesting questions such as the following.

Initialize sequence every 00:00

I looked for ways to solve them most efficiently and created a sequence that can handle the “interesting requirements” by myself. It can be easily solved by referring to the blog I just shared before.
https://gywn.blog/2019/01/31/using-affected-rows-in-mysql/

Goals

I already said that I need a sequence that is initialized every 00:00 above it. In general, if you solve this in your business logic(not database), it will be implemented in the following way.

begin;

select seq, last_date, 
       current_date curr
  from seq_table 
 where seq_name = 'xxx' 
for update;

## if curr == last_date ##
  update seq_table set 
     seq = seq + 1
  where seq_name = 'xxx';
## else ##
  update seq_table set 
     seq = 1,
     last_date = current_date()
  where seq_name = 'xxx';
## end if ##
commit;

This means that four queries must be sent in order to update single row, and this process is burdensome if the network latency is bad. (Think about it. If latency is 5ms, it takes at least 20ms to issue one sequence.) Because of the contention for a particular row, in this case, the application’s branching to business logic worsens performance.

If it does not matter, you can use it like that, but it is not pretty solution to me. Is there a more efficient way? Consider a simple way to solve this with a just single operation.

Solution

First, let’s create a table to save the last sequence value.

create table `seq` (
  `name` varchar(20) not null,
  `curr_date` date not null,
  `val` int(10) not null,
  primary key  (`name`)
) engine = myisam;

Second, let’s create the sequence as below.

delimiter //
drop function seq //
create function seq(seq_name char (3)) returns int
begin
  insert into seq (name, curr_date, val) values (seq_name, current_date(), last_insert_id(1))
      on duplicate key update 
         val = if(curr_date = current_date(), last_insert_id(val+1), last_insert_id(1)),
         curr_date = current_date();
  return last_insert_id();
end
//
delimiter ;

The sequence is normally issued, expected result. 🙂

mysql> select seq('a'), seq('a'), seq('a');
+----------+----------+----------+
| seq('a') | seq('a') | seq('a') |
+----------+----------+----------+
|        1 |        2 |        3 |
+----------+----------+----------+
1 row in set (0.00 sec)

mysql> select seq('a'), seq('a'), seq('a');
+----------+----------+----------+
| seq('a') | seq('a') | seq('a') |
+----------+----------+----------+
|        4 |        5 |        6 |
+----------+----------+----------+
1 row in set (0.01 sec)

To test when the time is over, try changing the timestamp as shown below.

mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2019-03-03     |
+----------------+
1 row in set (0.00 sec)

mysql> set timestamp = unix_timestamp('2019-03-04');
Query OK, 0 rows affected (0.00 sec)

mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2019-03-04     |
+----------------+
1 row in set (0.00 sec)

At this point, the sequence is issued with the initialized values as shown below. You no longer have to run 4 queries to issue next sequence value.

mysql> select seq('a'), seq('a'), seq('a');
+----------+----------+----------+
| seq('a') | seq('a') | seq('a') |
+----------+----------+----------+
|        1 |        2 |        3 |
+----------+----------+----------+
1 row in set (0.00 sec)

Cautions

  1. Using the InnoDB storage engine is helpful if you can branch transactions for sequence issuance.
    • InnoDB : 11,564 op/s
    • MyISAM : 8,813 op/s
  2. In semi-sync, a long node-to-node latency impacts the sequence issue regardless of the storage engine. (Semi-sync is for the IO thread between master and slave.)

Conclusion

I made a simple sequence that is automatically initialized every day. Depending on the service, some services have implemented complex business logic to initialize this value.

I compared the last value of the sequence with the current time, determined whether to initialize it, and issued a sequence value.

It’s very easy and simple, but it’s a useful, so I share!! 🙂