I have a program which manages reservations for events. I use two tables, Event, which holds the name of the event and the maximum number of available tickets, and Reservation, which is associated with an Event and keeps track of a number of tickets to be reserved for a specific person.
Below a simplified version of these classes:
Code:
public class Event {
private String nameOfEvent;
private int maxTickets;
}
Code:
public class Reservation {
private Event event;
private String nameOfUser;
private int desiredTickets;
}
I have a business rule which states that the number of tickets reserved for a particular Event should never exceed the maxTickets value for that Event.
To enforce this rule I use the following code (heavily simplified):
Code:
private void save(Session session, Event event) {
Transaction transaction = session.beginTransaction();
Object[] result = (Object[])session.createQuery("SELECT count(*) FROM Reservation r WHERE r.event = :event").setParameter("event", event).uniqueResult();
if(((Integer)result[0]) > event.getMaxTickets()) {
transaction.rollback();
throw new RuntimeException("Amount exceeded!");
}
event.saveOrUpdate(event);
transaction.commit();
}
This code will work 99% of the time, however it fails when a Reservation was altered (and committed) by someone else just after I did the query that checks the business rule. I can easily reproduce it by putting a 10 second delay after the createQuery line.
All the records I use have versions. What I expected to happen was that all of the records queried in the count query would be read locked (since I'm inside a transaction) to guarantee they cannot be changed while I do calculations with the data, which may or may not result in a real change.
However what happens is that Hibernate only checks if the main record was altered (the Event record) and does not read lock the records in my count query at all.
What would be the best way to solve this problem? I've been looking into using a higher transaction level (Serializable), manually locking the relevant records in the Reservation table, or obtaining an exclusive lock on the associated Event for any changes that relate to that Event.
I'm pretty sure this must have been solved before, so what solution would you recommend?