0

I'm using spring boot JPA 2.1.18.
All my model classes derives from this base class:

@Getter
@Setter
@MappedSuperclass
@EntityListeners(AuditingEntityListener.class) 
public abstract class DbEntity {

   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   protected Long id;

   ....
}

Everything works fine, but I'm facing a strange behaviour with the ids used for the new rows in the db ( I'm using SqlServer):

enter image description here

As you casn see, suddenly the ids jumped forward by 10000 and I don't understand why (it's impossible that the java code reserves 10000 ids, because I don't have batch processes). Any suggestions?

S-Wing
  • 485
  • 6
  • 25
  • 1
    Presumably `id` is an `IDENTITY`? If so this is completely expected. This isn't Java, this is by design, on SQL Server. An `IDENTITY` column should not be expected to be continuous, it simply guarantees that the next value will be larger than the last one generated, and each is generated atomically. – Thom A Jul 16 '21 at 09:30

1 Answers1

0

This is because of an option in SQL Server called IDENTITY_CACHE, take a look at this page from Microsoft which explains a bit more

https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql?view=sql-server-ver15

To disable it, run the below under your database

USE DatabaseName
GO
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF
GO

Note that this can have a performance penalty

Otter
  • 1,086
  • 7
  • 18
  • 1
    Note that turning off the `IDENTITY_CACHE` does come with a performance penalty. The cache is there as it's far more performant for SQL Server to cache the values needed before they are used, rather than grab the next one every time one is needed. – Thom A Jul 16 '21 at 09:31