5

I'm using a get_or_create pattern similar to the answer to this question:

Does SQLAlchemy have an equivalent of Django's get_or_create?

But I'm having trouble with another thread creating an instance (with the same pk) between the 'select' and the 'insert' of the first thread.

Should the get_or_create function lock the table for the two queries? What is the best way of implementing locking with SqlAlchemy?

I'm using postgresql: http://www.postgresql.org/docs/current/static/sql-lock.html

The only sqlalchemy locking functionality I can see is 'for update', which doesn't seem to be the right type of locking?

Community
  • 1
  • 1
EoghanM
  • 25,161
  • 23
  • 90
  • 123

2 Answers2

2

It seems I have misunderstood your reply to my answer in the other question.

What you are looking for here probably isn't locking directly, but transactions. http://www.sqlalchemy.org/docs/core/connections.html#using-transactions

But... are you trying to prevent executing the same insert twice? In that case you would need a lock somewhere in python (or catch the duplicate key error). That is actually the only foolproof way to do it. Try to insert and get it if you get a duplicate key error.

Wolph
  • 78,177
  • 11
  • 137
  • 148
2

Thanks to WoLpH, the following seems to be working for me (EdgeInfo is an elixir model):

from sqlalchemy import exc

session.begin_nested()
e_info = EdgeInfo(pk_attr_1=pk_attr_1, pk_attr_2=pk_attr_2)
try:
    session.commit()
except exc.IntegrityError:
    session.rollback()
    e_info = EdgeInfo.get((pk_attr_1, pk_attr_2))
EoghanM
  • 25,161
  • 23
  • 90
  • 123