SQLAlchemy and explicit locking

Question

I have multiple processes that can potentially insert duplicate rows into the database. These inserts do not happen very frequently (a few times every hour) so it is not performance critical.

I've tried an exist check before doing the insert, like so:

#Assume we're inserting a camera object, that's a valid SQLAlchemy ORM object that inherits from declarative_base...
try:
  stmt = exists().where(Camera.id == camera_id)
  exists_result = session.query(Camera).with_lockmode("update").filter(stmt).first()

  if exists_result is None:
    session.add(Camera(...)) #Lots of parameters, just assume it works
    session.commit()
except IntegrityError as e:
  session.rollback()

The problem I'm running into is that the exist() check doesn't lock the table, and so there is a chance that multiple processes could attempt to insert the same object at the same time. In such a scenario, one process succeeds with the insert and the others fail with an IntegrityError exception. While this works, it doesn't feel "clean" to me.

I would really like some way of locking the Camera table before doing the exists() check.

Solution

Pehaps this might be of interest to you:

https://groups.google.com/forum/?fromgroups=#!topic/sqlalchemy/8WLhbsp2nls

You can lock the tables by executing the SQL directly. I'm not sure what that looks like in Elixir, but in plain SA it'd be something like:

 conn = engine.connect()
 conn.execute("LOCK TABLES Pointer WRITE")
 #do stuff with conn
 conn.execute("UNLOCK TABLES")