Java - Distributed JPA Lock For Reservation

Question

I am working on a legacy system that allows reservation scheduling. The application is stateless REST and designed to be horizontally scaled. The database, however, is shared between all instances. Before I get a lecture on design and scale, it's not mine - have to make the best of a bad situation (or codebase). Recently we have seen an issue where there are duplicate reservations. I believe it is because of the nature of request response threads. The process currently is, receive request, check database for conflicting time reservation, if none, insert. Depending on time between reads and insert it's possible that both get inserted. Scenario seems to look like this:

|------|-------|-------|
R1     C1      I1     RSP

-|--------|-------|---------|
R2       C2     I2   RSP

Where R = Request, C = DB Check, I = Insert.

So I believe I could use the @Synchronized annotation which would force all threads to be ordered. The problem comes with the fact that there are multiple instances running so that wont work overall instances. Pessimistic or Optimistic reads and writes dont seem to apply since we are trying to do a read and write combo unless I completely misunderstand. Any thoughts to handle this problem across scale? Would prefer to handle it in java via a table lock or something similar rather then add additional services (kafka, redis, etc).

EDIT: The database looks something like this and using h2 in dev and mysql in production.

 id |  start_time  | locationid | postingid | userid | durration
 --------------------------------------------------------------- 

Answer 1

Implementing something like this from scratch is no rocket science but maybe you may want to take a look at this GitHub project: https://github.com/alturkovic/distributed-lock

I'm neither involved in this project nor currently using it but it looks very promising. You simply need to create a Spring configuration with EnableJdbcDistributedLock:

@Configuration
@EnableJdbcDistributedLock
public class LockConfiguration {
}

And create the needed database table:

create table lock (
  id int not null auto_increment primary key,
  lock_key varchar(255) unique,
  token varchar(255),
  expireAt timestamp,
);

Once this is in place you can sychronize method calls in a distributed environment by a simple annotation (taken from the project's examples):

@JdbcLocked(expression = "#name")
public String sayHello(final String name) {
  return "Hello " + name + "!";
}