0

I have a table where id is primary key and squence. In transaction I call sequence

select NEXTVAL(next_cons_id) as next_cons_id

then create a new record where field id = next_cons_id got from code bellow.

DDL sequence

CREATE SEQUENCE IF NOT EXISTS next_cons_id START WITH 1;

In most cases it work well. Having done a small study I found some patterns.

Id, ... gap ..., nextId
606401,605557,
604556,605401,
603555,604401,
602594,603401,
601594,602401

Ids with dates:

601594  2019-10-10 00:04:54
602401  2019-10-10 01:36:33
602594  2019-10-10 21:46:43
603401  2019-10-11 01:19:49
603555  2019-10-11 21:41:26
604401  2019-10-12 00:30:09
604556  2019-10-13 00:15:32
605401  2019-10-13 03:51:42
605557  2019-10-13 22:42:22
606401  2019-10-14 02:14:28

And after I did create console script that make insert by one record per second in test table:

237343  2019-10-14 00:31:03 2019-10-14 00:31:03
237344  2019-10-14 00:31:03 2019-10-14 00:31:03
....gap...
238000  2019-10-14 00:31:04 2019-10-14 00:31:04

It table use only test script.

But it time we can see some problem: enter image description here

There were no network problems at this point enter image description here

In monitoring of zabbix rollback of transactions not found.

Database has one replication server.

Server version: 10.3.13

Do you have any ideas to fix this?

UPD: I remove the sequence and recreate this table with id primary key autouncrement but this case has been repeated.

gregman
  • 332
  • 1
  • 11

0 Answers0