[MDEV-14088] Strange behavior with SEQUENCE Created: 2017-10-18  Updated: 2017-10-28  Resolved: 2017-10-28

Status: Closed
Project: MariaDB Server
Component/s: Sequences
Affects Version/s: 10.3.1, 10.3.2
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Wagner Bianchi (Inactive) Assignee: Andrii Nikitin (Inactive)
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

CentOS 7.2
[root@box01 ~]# uname -r
3.10.0-327.4.5.el7.x86_64


 Description   

Folks,

Checking the SEQUENCE feature while working with a new schema on MariaDB 10.3.1 and 2, I found the following situation what I think is a bug. I created a simple SEQUENCE, selected some times its NEXTVAL and then, I issued the command flush tables. Continuing with the tests, I selected the SEQUENCE's NEXTVAL again and for my surprise, the numeric sequence got broken for some reason. Below, the sequence of the facts:

MariaDB [mydb]> create sequence s1;
 
Query OK, 0 rows affected (0.006 sec)
 
 
 
MariaDB [mydb]> select s1.nextval;
 
+------------+
 
| s1.nextval |
 
+------------+
 
|          1 |
 
+------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [mydb]> select s1.nextval;
 
+------------+
 
| s1.nextval |
 
+------------+
 
|          2 |
 
+------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [mydb]> flush tables;
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [mydb]> select * from s1;
 
+-----------------------+---------------+---------------------+-------------+-----------+------------+--------------+-------------+
 
| next_not_cached_value | minimum_value | maximum_value       | start_value | increment | cache_size | cycle_option | cycle_count |
 
+-----------------------+---------------+---------------------+-------------+-----------+------------+--------------+-------------+
 
|                  1001 |             1 | 9223372036854775806 |           1 |         1 |       1000 |            0 |           0 |
 
+-----------------------+---------------+---------------------+-------------+-----------+------------+--------------+-------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [mydb]> select s1.nextval;
 
+------------+
 
| s1.nextval |
 
+------------+
 
|       1001 |
 
+------------+
 
1 row in set (0.000 sec)

Analysizng the results, it's clear that, if it has cached all the values until 1000, as 1001 is the next_not_cached_value, it's not OK to receive 1001 after 2 in case I flush tables; if I'm using on my application the S1.NEXTVAL instead of an AUTO_INCREMENT column, I will have a gap of many positions on my PK.

Getting a new record into a table having the INSERT using another existing SEQUENCE, gave me the below after the FLUSH TABLES:

MariaDB [mydb]> SELECT * FROM TBL_CUSTOMER;
 
+-------------+---------------------+---------------------+
 
| CUST_NUMBER | CUST_NAME           | CUST_BORN_DATE      |
 
+-------------+---------------------+---------------------+
 
| 2           | KUDDI SACARANDEERMA | 1980-09-09 00:00:00 |
 
| 3           | JANNE KESKITALANII  | 1983-02-17 00:00:00 |
 
| 4           | SUOMMO AKETELEENI   | 1981-05-11 00:00:00 |
 
| 1000001     | CIKUA ROCONNEQ     | 1967-03-27 00:00:00 |
 
+-------------+---------------------+---------------------+
 
4 rows in set (0.000 sec)

Thanks for checking, cheers!

 Comments   
Comment by Andrii Nikitin (Inactive) [ 2017-10-19 ]

Cache in Sequence is used to address potential contention for highly concurrent load.
We did consider all available solutions and every variant needs some trade between usability and throughput and current approach was chosen.

If you don't need to address such contention - consider using CACHE = NOCACHE clause in CREATE command as mentioned at https://mariadb.com/kb/en/library/create-sequence/

Comment by Wagner Bianchi (Inactive) [ 2017-10-19 ]

Andrii,

The CACHE=NOCACHE wasn't possible to compile unless I made a mistake, but, I created the SEQUENCE on the following way:

MariaDB [mydb]> CREATE SEQUENCE SEQ_CAR
 
    ->   MINVALUE 1
 
    ->   START WITH 1
 
    ->   INCREMENT BY 1
 
    ->   CACHE 0;
 
Query OK, 0 rows affected (0.006 sec)

And then, I made the same tests as before:

MariaDB [mydb]> SELECT SEQ_CAR.NEXTVAL;
 
+-----------------+
 
| SEQ_CAR.NEXTVAL |
 
+-----------------+
 
|               1 |
 
+-----------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [mydb]> SELECT SEQ_CAR.NEXTVAL;
 
+-----------------+
 
| SEQ_CAR.NEXTVAL |
 
+-----------------+
 
|               2 |
 
+-----------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [mydb]> FLUSH TABLES;
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [mydb]> SELECT SEQ_CAR.NEXTVAL;
 
+-----------------+
 
| SEQ_CAR.NEXTVAL |
 
+-----------------+
 
|               3 |
 
+-----------------+
 
1 row in set (0.000 sec)

I'm seeing that the default value for the CACHE when omitted on the sequence creation syntax is 1000, shouldn't be 0? This problem with FLUSH TABLES, is it expected?

Cheers guys,

Bianchi

Comment by Andrii Nikitin (Inactive) [ 2017-10-20 ]

Yes, default value of CACHE is intentional 1000, which means that 1000 requests to sequence object will not need any IO operation / locking.
And yes - it is intentional behaviour that all cached values are discarded when the object is closed / reopened.

Comment by Andrii Nikitin (Inactive) [ 2017-10-20 ]

I've checked the syntax and it should be like this:

MariaDB [test]> create sequence x1 nocache;
 
Query OK, 0 rows affected (0.106 sec)
 
 
 
MariaDB [test]> show create sequence x1\G
 
*************************** 1. row ***************************
 
       Table: x1
 
Create Table: CREATE SEQUENCE `x1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=InnoDB
 
1 row in set (0.089 sec)

Generated at Thu Feb 08 08:10:49 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.