In the H2 database, does the auto_increment field increase by 32?

Question

I have this simple table (for test only):

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

Then I execute the following queries:

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

At this stage, everything is going well, then I close the H2 Console and open it again and re-execute this request:

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

Finally, here are all the results:

I do not know how to solve this problem if it is a real problem ... awaiting the author's response ...

Answer 1

The database uses a 32-entry cache for sequences , and auto-increment internally implements a sequence. If the system crashes without closing the database, at most this number is lost. This is similar to how sequences work in other databases. Sequence values ​​are not guaranteed if there are no spaces in such cases.

So, have you really closed the database? You should - this is not a technical problem if you do not, but closing the database ensures that such strange things do not happen. I cannot reproduce the problem if I usually close the database (stop the H2 Console tool). Closing all connections will close the database, and the database will be closed if the application is stopped normally (by shutting down).

By the way, what is your exact database url? You seem to be using jdbc:h2:tcp://... but I don't see another url.

Answer 2

Do not close the terminal. The terminal is the parent process of the h2-tcp server. They are not suspended. When you simply close the terminal, the process terminates all child processes, which means server crash