1

So I have two Models:

class Unit(models.Model):
   name = models.CharField()

class Session(models.Model):
   unit = models.ForeignKey(Unit)
   startDateTime = models.DateTimeField()
   endDateTime = models.DateTimeField()

Users can book 'Units' for sessions which start and end at a user-requested date/time. No unit can be in use at the same time and I'd like to enforce this by ensuring no overlapping sessions per unit can be booked. I'd like to do this with one underlying query if possible, with this presumably guaranteeing atomicity of the update?

I've come up with two approaches, neither of which I'm very happy with:

Executing this raw SQL:

insert into "myapp_session" user_id, unit_id, startDateTime, endDateTime 
select 6, 2, '2013-05-18 02:09:02', '2013-05-18 03:09:02' from "myapp_session" 
where not exists 
(select * from "myapp_session" where unit_id=2 AND ("startDateTime" BETWEEN '2013-05-18 02:09:02' AND '2013-05-18 03:09:02' OR "endDateTime" BETWEEN '2013-05-18 02:09:02' AND '2013-05-18 03:09:02'));

This should only insert a new Session if there are none already booked that overlap with it. Is it possible to get the ORM to do something similar?

The other approach is to use select_for_update() on the Unit, effectively using it as a lock. 

unitLock = list(Unit.select_for_update().filter(id=2)) # list() forces evaluation, this should block others until we have finished inserting the session?
overlappingSessions = Session.objects.filter(startDateTime__range=[requestedStart, requestedEnd], endDateTime__range=[requestedStart, requestedEnd])
if not overlappingSessions.exists():
    Session.objects.create(unit=2, startDateTime=requestedStart, endDateTime=requestedEnd)
# the lock should be freed as soon as the view function returns

This only locks one row in the Units table, so other sessions for other Units can still be added concurrently.

Another related approach might be to add a 'sessionInsertInProgress' field to Unit. Assuming this would get updated atomically, it would stop other concurrent processes from continuing with inserting a Session on the unit while allowing sessions to be booked for other units unimpeded.

user1478842
  • 135
  • 4
  • 10
  • Seems like you want to do validations while saving stuff to database. I'd suggest overriding [save](http://stackoverflow.com/questions/4574367/django-overriding-save-method-in-model) method and doing all validations and checking while raising exceptions where appropirate. – Sidharth Shah May 19 '13 at 07:05
  • FYI transaction.atomic does not lock the entire database, that would horrible :) – Tommaso Barbugli May 19 '13 at 10:06
  • @SidharthShah Unless I've misunderstood something, the problem with this is that between checking for overlapping sessions and actually doing the insert, we are vulnerable to a race condition because another user may have added a conflicting session between the check and the insert. – user1478842 May 19 '13 at 12:28
  • @TommasoBarbugli ah sorry, I misunderstood. I see now that transaction.atomic() ensures DB updates are rolled back in the event of an exception, but it doesn't make guarantees about integrity during concurrent updates. – user1478842 May 19 '13 at 12:30
  • have you considered enforcing this at db level ? you cant get db contraints on overlapping datetimes but you can easily do this via insert / update triggers ;) – Tommaso Barbugli May 19 '13 at 15:00
  • @TommasoBarbugli Yeah, although I have found some examples online I'm not that much of a DB expert to feel comfortable doing this. I've decided to use select_for_update() on the Unit whose session is being added (even though the Unit itself isn't being updated). Sessions aren't added anywhere else in the application so this will block any other users to avoid conflicts. – user1478842 May 19 '13 at 17:49
  • @user1478842 do you mind posting your complete solution as answer ? Just to make sure you got it right and for other users of SO :) – Tommaso Barbugli May 20 '13 at 09:19

0 Answers0