• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JPA: preventing overlapping calendar events by locking a table?

 
Erik Penny
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,

I'm writing a reservation system using JPA which allows the user to create and modify Reservation entities, which are basically your garden variety calendar events. Reservations occur either individually, or in relation to Recurrence entities; a single Recurrence 'contains' many Reservations via a one-to-many relationship.

I have a screen in which the user (say, Bob) can create a Recurrence by adding/removing individual Reservations using a list of checkboxes:



One of the restrictions on the model is that Reservations cannot overlap.
Therefore, when this screen is first drawn, the database is queried to check whether these dates are actually available. Unavailable dates are marked with an asterisk '*'.

However, since multiple users may be using the system simultaneously, the following scenario could occur:

1) Bob opens recurrence creation screen (see my ASCII art)
2) The system checks and marks the unavailable dates with '*'
3) Alice creates a Reservation on 22 September
4) Bob clicks on 'ok', also submitting 22 September as one of his desired Reservations

Now in order to ensure that the 'no overlaps' constraint isn't violated, I would like to be able to add the following steps:

5a) Bob's client will check that the selected dates are still available, and write-locks the table for Reservations atomically
6) The system will persist the Reservations he has ticked if and only if they do not overlap existing Reservations
7) The system relinquishes the write lock

About step 5: from the moment the system has checked the availability of the given dates, and ascertained that they are all available, it should be impossible for anyone to add/modify Reservation entities (i.e. the table should be write-locked in some way), in order to ensure that the constraint is respected, until the new Recurrence and its Reservation entities have all been persisted.

My question: how do I do this? I think a pessimistic approach would be to have a table-level lock on the 'Reservation' table, e.g. by means of a separate lock table using a unique id constraint. But isn't there a better way to do this with JPA/Hibernate?

I'm aware btw that this is not a very scalable solution, but for now that's no biggie since the number of concurrent users will be very low.

Another more optimistic approach might be to persist all of the newly created Reservations within a transaction, and to roll it back if any new overlapping Reservations have been created in the mean time, but again this would require atomic checking and persisting using... a table level lock? xO

Any suggestions?

Thanks!
Erik
[ September 21, 2008: Message edited by: Erik Penny ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic