title: SQLAlchemy and explicit locking
body: <p>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. </p>
  
  <p>I've tried an exist check before doing the insert, like so:</p>
  
  <pre><code>#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()
  </code></pre>
  
  <p>The problem I'm running into is that the <code>exist()</code> 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.</p>
  
  <p>I would really like some way of locking the Camera table before doing the <code>exists()</code> check. </p>