3

I'm using session with a room reservation app that uses SQL Alchemy. The app until now works fine, but, I tested with 2 persons using it at the same time, using different user accounts, and the reserves can be done at the same room, and hour of the day.

This didn't happen when using just one account, i tested the room check code a lot, so must be a concurrency problem (both users use the check when is empty and the make it). I'm using PostgreSQL as DBMS, is there a way lock the table while making the commit to the DB and then unlock it when it's done??

Improve this question
2
  • 1
    Add your table layout and the queries you run. It's unclear whether your problem is with concurrency or with database design. Commented Aug 23, 2012 at 23:38
  • If I understand this correctly, you have two processes that query the db, they see an empty room and both reserve successfully, which is translated to two successful INSERTs (or updates?), which should not happen. Would something like a version counter or configuring stricter transaction isolation in Postgres help? Commented Apr 13, 2014 at 10:19

1 Answer 1

Reset to default
2

This is exactly the kind of case for which exclusion constraints where invented. When 9.2 is released you will be able to use these with timestamptz ranges; for versions 9.0 and 9.1 you will need to install the the temporal and btree_gist modules.

For more information see this blog post by the Jeff Davis, who came up with the idea and implemented it.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

above link is broken, blog post by the Jeff Davis: thoughts.davisjeff.com/2010/09/25/…

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.