How to get spaces in non-overlapping date ranges in concurrency-median JPA usage?

Question

Problem

Suppose I have the following object:

@Entity public class TimeSlot { @Id private DateTime start; private DateTime end; // other properties and accessors } 

I have several such objects stored in an SQL database, and I can guarantee that they do not overlap, but there may be spaces between these ranges. Now I want to implement a function that, taking into account the start and end dates, will create and save a new TimeSlot in each interval in this interval:

 public List<TimeSlot> aquireGaps(DateTime start, DateTime end); 

Example

To make it more specific, suppose we just use integers instead of dates. Now my table might look like this:

 +-------+-----+ | start | end | +-------+-----+ | 1 | 5 | | 8 | 12 | | 20 | 24 | +-------+-----+ 

After calling the aquireGaps(3, 22) function aquireGaps(3, 22) I want the table to look like this:

 +-------+-----+ | start | end | +-------+-----+ | 1 | 5 | | 6 | 7 | <-- new interval fills gap | 8 | 12 | | 13 | 19 | <-- new interval fills gap | 20 | 24 | +-------+-----+ 

Question

The question is how to implement this as a transaction. To be specific, I want every other thread / process to not create a new TimeSlot in any interval in the interval in which I work. Performance is not a problem, as the number of concurrent users is very low. I am looking for a solution that is easiest to implement.

In simple JDBC, I would use the SERIALIZABLE transaction isolation level, but there is no standard way to set the isolation level in JPA. I am looking for a solution that does not depend on the specifics of the supplier.

Alternatively, I could just use an optimistic lock and try again if the commit failed, but this requires additional error handling logic.

Are there any viable alternatives or optimistic to block / retry exit from JPA?

Answer 1

I don’t understand what you are trying to do, perhaps explain better. What kind of specific SQL are you trying to prevent?

It seems that you are trying to avoid parallel inserts, which blocks or serializes transaction isolation, it will not help you, row locking prevents simultaneous updating / deleting on the same lines.

A primary key or unique constraint should be sufficient to prevent two transactions from inserting TimeSlots that have the same startDate.

See, http://en.wikibooks.org/wiki/Java_Persistence/Locking