How can I use mysql table as a unique key generator in Spring Boot JPA?

Question

I want to generate unique keys at run time across regions and the key generation will be owned by master region. I will have one table with one column(key) with default value as 0 and one row only. Now whenever I need to generate the new unique key,i will increment that column key and return its value. Now I want both this to happen in a transactional way: Increment count of column and return value.My method will look as:

int generateKey(){

1. UPDATE key_generation SET key = key+1;
2. Select * from key_generation limit 1; }

How can I ensure that both of this are executed together and no read/write should happen on this table when this one request is running?

Answer 1

You should use auto_increment for that purpose.

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
);

Source: https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html