generate auto increment id (int) in java for multi instance application

Question

I have a table which stores users information(id, name, surname, address, email, etc). I don't want to use auto-increment function of the database. So, we don't want to create sequence. I want to generate id in spring boot application and insert my custom human readable ids(such that 1,2,3, ... n) to the db while creating new user. In order to do that I always want to select max id and increment it by one and insert as a id for new record. For example, max id is 5 in the users table, I will get max and increment it and new user id will be 6. If there is just one instance there will not be any problem but if there are more than 1 instance of spring boot application, there can be problem theoretically. If both instances take max id and increment it, they will get same result and one of them will get exception because of unique key constraint.

I have some solutions but I think there is better solutions. My solutions

1. storing max key always in redis and while taking max increment it immediately.
2. locking table (bad solution)
3. if I can duplicate constraint exception I will check max again, check id is greater than new user id then increment again and insert it.

However, I think they are not best solutions. Finally, I just want increment id in application level, I dont want to use db's sequences. My problem is there can be problem if I will have more than 1 instance. So how can I solve this problem.

Answer 1

In postgresql there is a function called nexVal(seqId). It is used to reserve an id that no one else can use . It could be one possible solution for your problem