Search code examples
auto-incrementh2

In H2 database, the auto_increment field is incremented by 32?

I have this simple Table (just for test) :

create table table 
(
key int not null primary key auto_increment,
name varchar(30)
);

Then I execute the following requests:

insert into table values ( null , 'one');// key=1
insert into table values ( null , 'two');// key=2

At this Stage all goes well, then I close The H2 Console and re-open it and re-execute this request :

insert into table values ( null , 'three');// key=33

Finally, here is all results:

enter image description here

I do not know how to solve this problem, if it is a real problem... pending a response from the author...

Solution

  • The database uses a cache of 32 entries for sequences, and auto-increment is internally implemented a sequence. If the system crashes without closing the database, at most this many numbers are lost. This is similar to how sequences work in other databases. Sequence values are not guaranteed to be generated without gaps in such cases.

    So, did you really close the database? You should - it's not technically a problem if you don't, but closing the database will ensure such strange things will not occur. I can't reproduce the problem if I normally close the database (stop the H2 Console tool). Closing all connections will close the database, and the database is closed if the application is stopped normally (using a shutdown hook).

    By the way, what is your exact database URL? It seems you are using jdbc:h2:tcp://... but I can't see the rest of the URL.