0

We have a table with the following DDL:

create table test
(
    a           VARCHAR(36) CHARSET utf8                  NOT NULL,
    b           TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3) NOT NULL,
    number      BIGINT UNSIGNED AUTO_INCREMENT,
    updated_at  TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) NOT NULL,
    PRIMARY KEY (a, b)
)

And the hibernate mapping:

public class TestTable {
    @Id
    @GeneratedValue(strategy = IDENTITY)
    @Column(name = "number")
    private Long number;

    @Column(name = "a")
    private String a;

    @Column(name = "b")
    private Date b;

    @Column(name = "updated_at")
    private Date updated_at;
}

We have a spring-boot app that exposes two endpoints. One endpoint reads data from this table and another one saves the data. There are a lot more writes than reads.

The SELECT query is the following:

SELECT * FROM TestTable where number >= ?

The problem that we are facing is that sometimes we have two records with the following data:

  • record #1 number = 100, updated_at = 2022-01-01 00:00:00.200000
  • record #2 number = 101, updated_at = 2022-01-01 00:00:00.000000

For record #2, the auto_increment column 'number' has a higher value than Record #1 'number' column, but it has a lower timestamp value for column 'updated_at' than Record #1. Vice-versa for Record #1

Given the following select query is executed:

SELECT * FROM TestTable where number >= 100

When the SELECT query is executed (probably sometime around timestamp 2022-01-01 00:00:00.100000, so between the two timestamps) it will not return record #1.

How is it possible to have these values out of order and what should we do to avoid this from happening?

Dan Nemes
  • 300
  • 3
  • 16
  • The query which inserts number=101 starts its execution earlier but accesses the autoincrement procedure later (for example due to long execution plan building process or any lock). This is possible, and this is not a problem. *what should we do to avoid this from happening?* Try explicit LOCK TABLES. – Akina Jun 07 '22 at 10:12
  • Thank you for the response. That makes sense. The issue is that we can not use LOCK TABLES because there will be a significant performance impact. Would there be a different solution? – Dan Nemes Jun 07 '22 at 11:56
  • I have had a read through the MySQL docs and found the innodb_autoinc_lock_mode setting. The value is set to "interleaved" (which explains the behaviour better). I believe that a simple change of this value from 2 (interleaved) to 1(consecutive) or even 0 (traditional) would fix this problem. Is my assumption right or is there a caveat to this? – Dan Nemes Jun 08 '22 at 05:55

0 Answers0